Elomis
ADSM.ORG Senior Member
There are heaps upon heaps of questions posted in these forums that either start with, or are entirely, "I need a select statement that shows me...". In the spirit of teaching people to fish rather than giving them a fish, I thought I'd pen some notes on how to figure out essentially anything in TSM using SQL.
I am no TSM superguru, I know some really clever tricks and understand a substantial amount of the inner workings, I'm even certified - but I can pull any piece of information out of TSM that anyone needs me to. You can too, like this;
If you are unfamiliar with what parts of TSM you can query, the first things you should do is enter select tabname from syscat.tables at the admin console prompt, this will dump out a list of each area of TSM you can query using SQL. Copy and paste this into notepad or something and save it to a thumb drive because you are going to be referring to it a lot. In fact if you go to a command prompt and type dsmadmc -ID=yourusername -pass=yourpassword select tabname from syscat.tables > TSMTablesToQuery.txt to create a text file list in TSMTablesToQuery.txt
If you are unfamiliar with SQL you need the three line primer on what it is. SQL queries the TSM database by selecting stuff where a paramater is valid from a table and ordering it by the paramater. So:
select * from drives where online = "YES" order by LAST_UPDATE
Selects everything from the drives table, that matches the online paramater being yes, and orders the list by the last time the drive was updated.
Now the juicy bit. Your boss comes along and says to you, "I want a list of every tape that is not full yet", how do you resolve this query and make yourself look like the purveyor of all information? Have a look at the table names, tapes are media, there's a table name called media.
Type select * from media and you'll get an output similar to the following;
VOLUME_NAME: A12345
STATE: MOUNTABLEINLIB
UPD_DATE: 2008-01-01 12:13:19.000000
LOCATION:
STGPOOL_NAME: TAPESTORAGEPOOL
LIB_NAME: GIANTTAPELIBRARY
STATUS: FULL
ACCESS: READWRITE
LRD: 2008-01-01 09:13:19.000000
... for a few pages, where TSM has gone through and found all of the items in the table "media" that matches your criteria. In this instance you haven't specified criteria so it's going to try and return all of them.
But what our boss asked for was a list of them that are not full, so we need to specify our parameter;
select * from media where status = 'FILLING'
Will do it, presenting an output of all the details of each tape that is filling.
We still haven't quite given the boss exactly what he/she wants, they asked for a list of the tapes, and our output so far is all the details of all the tapes, so instead of select * (return all the details), we just want the names of the tapes. Easy-peasey;
select volume_name from media where status = 'FILLING'
Is exactly what the boss asked for. I recommend importing it into Microsoft Excel and colour coding it to make youself look even more useful to management, because we all know that to management the only acceptable format for anything is Excel and nothing is of any value unless it's colour coded *saracstic giggle*
So to recap;
To get any piece of information out of TSM.
I am no TSM superguru, I know some really clever tricks and understand a substantial amount of the inner workings, I'm even certified - but I can pull any piece of information out of TSM that anyone needs me to. You can too, like this;
If you are unfamiliar with what parts of TSM you can query, the first things you should do is enter select tabname from syscat.tables at the admin console prompt, this will dump out a list of each area of TSM you can query using SQL. Copy and paste this into notepad or something and save it to a thumb drive because you are going to be referring to it a lot. In fact if you go to a command prompt and type dsmadmc -ID=yourusername -pass=yourpassword select tabname from syscat.tables > TSMTablesToQuery.txt to create a text file list in TSMTablesToQuery.txt
If you are unfamiliar with SQL you need the three line primer on what it is. SQL queries the TSM database by selecting stuff where a paramater is valid from a table and ordering it by the paramater. So:
select * from drives where online = "YES" order by LAST_UPDATE
Selects everything from the drives table, that matches the online paramater being yes, and orders the list by the last time the drive was updated.
Now the juicy bit. Your boss comes along and says to you, "I want a list of every tape that is not full yet", how do you resolve this query and make yourself look like the purveyor of all information? Have a look at the table names, tapes are media, there's a table name called media.
Type select * from media and you'll get an output similar to the following;
VOLUME_NAME: A12345
STATE: MOUNTABLEINLIB
UPD_DATE: 2008-01-01 12:13:19.000000
LOCATION:
STGPOOL_NAME: TAPESTORAGEPOOL
LIB_NAME: GIANTTAPELIBRARY
STATUS: FULL
ACCESS: READWRITE
LRD: 2008-01-01 09:13:19.000000
... for a few pages, where TSM has gone through and found all of the items in the table "media" that matches your criteria. In this instance you haven't specified criteria so it's going to try and return all of them.
But what our boss asked for was a list of them that are not full, so we need to specify our parameter;
select * from media where status = 'FILLING'
Will do it, presenting an output of all the details of each tape that is filling.
We still haven't quite given the boss exactly what he/she wants, they asked for a list of the tapes, and our output so far is all the details of all the tapes, so instead of select * (return all the details), we just want the names of the tapes. Easy-peasey;
select volume_name from media where status = 'FILLING'
Is exactly what the boss asked for. I recommend importing it into Microsoft Excel and colour coding it to make youself look even more useful to management, because we all know that to management the only acceptable format for anything is Excel and nothing is of any value unless it's colour coded *saracstic giggle*
So to recap;
To get any piece of information out of TSM.
- Look at syscat.tables to identify where that type of information is likely to reside, drives for drives, media for tapes, nodes for nodes etc.
- Do a quick select * from tablename to ID what criteria you are going to filter your query by.
- Change the * to what criteria you actually want to list.
