1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

SELECT queries for contents ... SLOOOW

Discussion in 'Command Line Administrator Client' started by wuestenfuchs, Mar 4, 2011.

  1. wuestenfuchs

    wuestenfuchs New Member

    Joined:
    Feb 12, 2009
    Messages:
    16
    Likes Received:
    0
    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
     
  2.  
  3. dburress

    dburress New Member

    Joined:
    Jan 23, 2008
    Messages:
    27
    Likes Received:
    0
    Occupation:
    Senior Systems Engineer
    Location:
    Jasper, IN
    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
     
  4. wuestenfuchs

    wuestenfuchs New Member

    Joined:
    Feb 12, 2009
    Messages:
    16
    Likes Received:
    0
    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
     
  5. dburress

    dburress New Member

    Joined:
    Jan 23, 2008
    Messages:
    27
    Likes Received:
    0
    Occupation:
    Senior Systems Engineer
    Location:
    Jasper, IN
    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.
     
  6. wuestenfuchs

    wuestenfuchs New Member

    Joined:
    Feb 12, 2009
    Messages:
    16
    Likes Received:
    0
    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
     
  7. dburress

    dburress New Member

    Joined:
    Jan 23, 2008
    Messages:
    27
    Likes Received:
    0
    Occupation:
    Senior Systems Engineer
    Location:
    Jasper, IN
    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.
     
  8. cjhood

    cjhood Moderator

    Joined:
    Dec 11, 2007
    Messages:
    322
    Likes Received:
    10
    Occupation:
    Storage Administrator - TSM/SAN
    Location:
    Brisbane, Australia
    Running select commands over the backups table on v6.* is much, much faster than on v5 from my experience.
     
: sql

Share This Page