Seems the post title has got your attention. Well guess what it will not take you even 20 minutes to go through the post below, but it will still give you a head out in TSM SQL Query that people who had used TSM for many years have not grasped yet. Its funny to know how many TSM admin I see on daily basis who ask me the same famous question “What SQL  select statement that shows me…”. As I had been answering many of these lately, I had decided to share with you how these are built up. If you are new to TSM you would ask your self why do I need these, then the easy answer is that you can easily figure out almost anything in TSM using SQL queries.

Lets start. When you are to find out the piece of information you are looking for you would have to start up by finding the table name containing that information. A great way to do this is by running the following command:

select tabname from syscat.tables

Ah I know the list is ridiculous long & you will never remember all of it. so a good idea is to get the output of this command to text file. To do this execute the following command

dsmadmc -ID=yourusername -pass=yourpassword select tabname from syscat.tables > TSMTables2Q.txt

The above command will give you the name of all TSM tables available. So you have to relate the topic you need to the table names to find out the table that contain the data you are looking for.

SQL Query statment usually go in the below format:

Select  <Item you are looking for> from <desired table> where <paramater is valid from table> order by <The field you want the output to be ordered by>

If you can master the above statement then you can generate most of the required queries on your own. Let’s look at an example:

:
select * from drives where online = “YES” order by LAST_UPDATE

This Query will list everything (*) from the table called (drives) where the Online parameter equal yes (online = “YES”) and it will be ordered by the last time the drive was updated (LAST_UPDATE).
Ah wow the command seems a lot clearer now. By now you should have learned the structure of the command and how to get the table name, but how do you find the rest of the parameter. This is what I am going to show you next.

:
Let’s assume you want to find out all tapes which is not full yet in your enviornment. Maybe you are trying to decide if you need to purchase new tapes & how soon you will need them. A good start again look at the table names exported to the text file earlier. Did not I tell you yet that list will be your friend 🙂 ? Ok, we are looking for tape and if we try to relate it to one of the table names then that will fall under media. Do you agree that you can relate that far?  I hope so. Let’s assume you can then you run:

select * from media

Then the SQL gift you an output similar to the below.

VOLUME_NAME: A1233
STATE: MOUNTABLEINLIB
UPD_DATE: 2008-02-21 11:15:17.000000
LOCATION:
STGPOOL_NAME: TAPESTORAGEPOOL
LIB_NAME: MYTAPELIBRARY
STATUS: FULL
ACCESS: READWRITE
LRD: 2008-02-21 08:15:17.000000

This output is quite useful as it give you a list of all the parameters & its values available into that table. So from that you can figure out the parameter you are looking for is STATUS & if you look for the other entries in the table you will find that the value you want is FILLING and from that you figure out the part needed for your statement is (status = ‘FILLING’) which mean to list the tapes which has a status of filling which basically not full. So up to know the query we had built is:

select * from media where status = ‘FILLING’

:
Great, This command listed all the details of every not full tape in your environment. Let’s say you want to provide that list to your manager to prove the need for tapes, but you don’t want to give him the other data in the table only the tapes name. You might say easy I will take the output to an Excel sheet and clean it manually :). What if you had a 1000 tape would you still want to do that or rather customize your query a bit.
So now we don’t want to list (*) which mean all fields, but we only want to list tape field name which (volume_name). Ah, where did this come from? Look back at the output of the earlier query above and you will find it. So the query end up looking something like below:

select volume_name from media where status = ‘FILLING’

So get the output and export it to your excel sheet & keep for reference.

OK, So that’s all for today let’s summarize:

To get any data of an SQL database you will have to follow the below steps:

1. Find in the syscat.table the table which have the type of information you are looking for
2. Run select * from tablename to find out which parameter you will need to filter your list by.
   
3. Replace the * to what criteria you actually want to list.

I hope after this you will be helping people with queries & laugh how easy it was :).