SELECT queries for contents ... SLOOOW

[wuestenfuchs]

Hello,

I would like to query via SELECT from dsmadmc from a TSM 5.5.5 server which files a client node has backed up during a certain time frame. This is not very difficult, I used queries like this:

select filespace_name,hl_name,ll_name from backups where node_name='SERVER01' and backup_date>current_timestamp-24 hours and type='FILE'

This gives me a list of files this node backed up during the last 24 hours. The problem is that the backups table does not contain the SIZE of the files. I can only find the size of files in the contents table.

Unfortunately, any attempts to extend this query to also include the file size by joining with the contents table lead to such slow queries that I could not yet wait long enough to have a query complete even for a very small client node.

What I wonder is how the TSM B/A (GUI) client retrieves the same data which it displays in the GUI. There it does not take very long to retrieve the data from the TSM server. And it does also include both the time stamps and the size which seem to come from those two tables.

Does anyone have an idea how I could achieve what I need through some other way? I am just out of ideas ... Thanks!

Kai-Uwe

[dburress]

I typically just look at the summary of the DSMSCHED.log on the client. After the last backup it creates a summary.

C:\Program Files\Tivoli\TSM\baclient\dsmsched.log

For example:

03/04/2011 06:23:47 Total number of objects inspected: 6,440,637
03/04/2011 06:23:47 Total number of objects backed up: 11,706
03/04/2011 06:23:47 Total number of objects updated: 6
03/04/2011 06:23:47 Total number of objects rebound: 0
03/04/2011 06:23:47 Total number of objects deleted: 0
03/04/2011 06:23:47 Total number of objects expired: 2,097
03/04/2011 06:23:47 Total number of objects failed: 29
03/04/2011 06:23:47 Total number of subfile objects: 0
03/04/2011 06:23:47 Total number of bytes transferred: 81.31 GB
03/04/2011 06:23:47 Data transfer time: 1,327.65 sec
03/04/2011 06:23:47 Network data transfer rate: 64,222.44 KB/sec
03/04/2011 06:23:47 Aggregate data transfer rate: 1,912.25 KB/sec
03/04/2011 06:23:47 Objects compressed by: 0%
03/04/2011 06:23:47 Subfile objects reduced by: 0%
03/04/2011 06:23:47 Elapsed processing time: 12:23:08
03/04/2011 06:23:47 --- SCHEDULEREC STATUS END
03/04/2011 06:23:47 --- SCHEDULEREC OBJECT END 6PM_SCHEDULE 03/03/2011 18:00:00

[wuestenfuchs]

I typically just look at the summary of the DSMSCHED.log on the client. After the last backup it creates a summary.

Well, may be I did not expressed my need good enoguh but this kind of summary is the REASON why I want that query.

Often I encounter the case that a machine backs up much more volume during its daily incremental backup than expected. In such cases I want to find out WHY. This in its first step means finding out WHAT IN DETAIL did it back up.

So I need to find out which files have been backed up for a specific machine last night and how large was each of them. Typically there are several hundred or even thousands of files backed up but often only one or a few very large ones account for the majority of the amount. And these I want to find out!

Kai-Uwe

[dburress]

Have you tried to just do a dsmc q backups from the client?

dsmc q backup *.* -fromdate=03/04/2011 -subdir=yes -querysummary

You can also define this to be more granular, if you know where the problem area is instead of using the wildcard *.* like I did here.

[wuestenfuchs]

Have you tried to just do a dsmc q backups from the client?

dsmc q backup *.* -fromdate=03/04/2011 -subdir=yes -querysummary

You can also define this to be more granular, if you know where the problem area is instead of using the wildcard *.* like I did here.

Yes, meanwhile I discovered this way, too. However, *.* does not work as a wildcard. You do have to use something like c:\ on Windows or /usr/ on Unix/Linux.

But it is quite slow, too.

Perhaps this will get better with TSM 6 and the DB2 database which may be able to perform the queries faster and/or I may be able to define additional (suitable) indexes for this kind of queries.

Kai-Uwe

[dburress]

Yes, meanwhile I discovered this way, too. However, *.* does not work as a wildcard. You do have to use something like c:\ on Windows or /usr/ on Unix/Linux.

But it is quite slow, too.

Perhaps this will get better with TSM 6 and the DB2 database which may be able to perform the queries faster and/or I may be able to define additional (suitable) indexes for this kind of queries.

Kai-Uwe

My apologies, I was testing this from a 6.2 client. There maybe some differences in the command. Either way, sounds like your still wanting a faster way to retrieve this info. I have to agree with you, I think all we can do is hope for the best with the DB2 instance. Not sure how strict TSM will be in regards to defining your own indexes.

[cjhood]

Running select commands over the backups table on v6.* is much, much faster than on v5 from my experience.