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.

TSM Database Page Shadow File

The Tivoli Storage Manager database has a file calle the Page Shadow File (dbpgshdw.bdt).  This file is a mirror of transactions that are written to the database.  This occurs only if two options are enabled in the Server Options file (dsmserv.opt). 

DBPAGEShadow Yes

MIRRORWrite DB Parallel

By default TSM installs with the first set to Yes and the second set to Sequential.  This causes the Page Shadow File to not be used.  However, even with the Parallel option being chosen, the Yes option still causes the Tivoli system to require that file.  In fact, one of the big ways I have seen a Tivoli Storage Manager database to get corrupted is having the parameters set as above and then some process lock the Shadow File.  I have seen machine backups that lock each file as they are backed up do this.  When that happens, you better have a good current back up of the database available.

The Page Shadow File is implemented in TSM to help with high volume systems that may get overwhelmed in database updates.  The transactions are written to this file allowing them to be picked up later and processed when there is less system activity.  If you have a system with heavy activity like this, I would encourage use of the shadow file.  I would make sure it is in a location where the system backup will not be touching it.  There is no need for this file to be backed up, and as I said above, locking this file is dangerous.  The location of this file can be determined int he dsmserv.opt.  By default the TSM server looks for it in the server directory that was created when the server instance was created.  By changing the entry to a fully defined path, it can be relocated to any directory on the server. 

If your system is not high volume, I would reccomend changing the default DBPAGEShadow from Yes to No.  Once this is done, you can delete the file and all is fine.  Or it can be left alone since the system will no longer look for it.  This will keep any harm from this file and keep it from causing database corruption if the file is inadvertantly locked by another program.