Saturday, December 18, 2010

Querying the Tivoli Storage Manager Database

While the Tivoli Storage Manager system gives a lot of options to gather data from the system, there are times when what you want to know is not available, even from the admin command line (you will never see me in the web-based admin interface).  The TSM database can be queried to produce this other information.  And that information can be dumped to text files allowing import into documents and spreadsheets.

So, how do you query the TSM database?  From the admin command line.  That makes the first step to open the command line.  It will be installed on your server and available for use.  Just go to the TSM program group and select Administrative Command Line.  Then enter the admin's user name (admin) and password.  If the password is still admin, you really should change it!

Now you are ready to start entering your query commands.  The syntax is the same as in standard queries.  But I will bet you are wondering what tables to query.  How do you find that out?  As usual, TSM has some awesome hep for this.  In fact, when working with the command line interface I often keep two windows open.  One for command entry and one for help.  So, the easiest path now is to open a second instance of the command window and type - Help Select.  You will now see in the first page of the help text how to find the tables in TSM, the columns in those tables and valid values for the columns that need that.  So in the other window, enter select * from syscat.tables.  TSM, of course, gives the results in a descriptive formatted style with description of each table.

Now that you see what the tables are, let's try a simple query that I have used at many sites to see what is in the system.  Type select count(*) from adsm.contents.  You will probably get a warning that this may take a bit of time to complete.  So go get a cup of coffee or lunch.  Mine came back fairly quickly (time to get coffee) with a result of 1,055,163.  This tells me that this is the number of files stored in my TSM system.  Since it just backs up my personal network, yours will probably be a lot larger.  Next you may want to see what columns are available for the adsm.contents table.  You could do this by select * from syscat.columns where tabname = 'CONTENTS' (and the uppercase letters are important).  Now you see all the column names for CONTENTS with the description of each one.  What could be easier to help in building a query?  

Next I did another query that may take some time to complete.  What this one will give you is the number of files in your TSM system for each node on the network.  Then you can go holler at the people who are taking more than their share.  select node_name, count(node_name from adsm.contents group by node_name order by node_name  Again, not sure how long this will take on your system.  Mine is an overworked older server so it was slow.  But once it completes, the count of file usage for each node will be available.

One last item and then you are free to go and see what you can do with this portion of TSM.  I said you could dump this info to text files and import them into documents and spreadsheets.  How to do this is total simplicity as with most of TSM.  Try this command.  Make sure the folder you plan on entering here does exist.  Select * from syscat.columns where tabname = 'CONTENTS' > C:\TSM_Query\Output\ContentsColumns.txt  And hit enter.  Now go to that folder and you will see a text file with a nice formatted output from that query.  This information can then be imported into an Excel spreadsheet, included in a document, or just emailed as is to someone in need of your query results.

So go explore the available tables and columns, then impress your boss by creating a formatted report of data stored in TSM and do it in very short time.  Or take a break in the middle.  You will still impress him but will make it look harder to do.