Be the TSM SQL Query Master in 1 hour

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 :) .




10 Responses to 'Be the TSM SQL Query Master in 1 hour'

  1. SS - February 17th, 2009 at 7:17 am

    Can we fire the SQL query directly on the TSM server prompt?

  2. admin - February 18th, 2009 at 12:29 pm

    You can issue the SQL query directly at the command line of an administrative client, but you cannot issue this command from the server console. Please note though when using the TSM administrative client you are mostly tightened up to a select statements.

  3. SQL Tutorials - April 30th, 2009 at 8:52 pm

    You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

  4. admin - May 1st, 2009 at 6:49 am

    Well, would DB2 or Oracle do it for you?

  5. ToM - January 9th, 2011 at 10:44 am

    Thaks a lot for the article. Great you share your expert TSM knowlage.

  6. admin - January 12th, 2011 at 12:59 pm

    Glad you enjoy it.

  7. Kamran - February 26th, 2011 at 5:39 am

    Sir did these queries work with db2 database like after tsm 6.1 db2.
    thanks

  8. EvieD - June 4th, 2011 at 2:58 pm

    Very new to TSM. Just came across this and I am so glad I did! Thanks for sharing!

  9. anusha - August 11th, 2011 at 1:30 am

    i had a table with the columns process_id,wf_status , part_dealerid and this diaplays the status of my ETL’S
    when i execute the query
    select count(*),wf_status from mytablename order by wf_status
    then it displays the status.
    Here my question is i want to get the status for every 1 hour to my mail, Can any body give the query for my question

  10. Shin - August 27th, 2011 at 12:19 pm

    Hi,
    A great article.. helped me a lot.. but I am currently working as how can we run tsm queries from a webpage(using some scripting) and display the results given by TSM on same page .. any suggestion or advises would be great, also link to some article if any as how can we achieve this.. I am newbie so tryin to get ideas


Leave a Reply