Hi Guys, The SQL tables we have to play with in TSM are indeed indexed.
If you do select * from syscat.columns, you will see there is a field called INDEX-KEYSEQ and INDEX-ORDER. The BACKUPS table is indexed on NODE_NAME, then FILESPACE_NAME, then FILESPACE-ID, then STATE, in that order. Speaking from experience, I can tell you the query DOES run faster if you select on an indexed field. So if you could select on a specific NODE-NAME, you would do a lot better. What I don't know is the effect of using a generic match like %ORC%; I don't know if that negates the indexing or not. What I have done in the past was to write a host script that generated the list of node_names for me, then iteratively ran the SELECT on the backups table with "where node_name=BLAH", sending the output to a file. Running the individual queries against one node_name at a time finished in about 3 hours, where running the entire backups table (as in your original query) ran for over 24 (before I gave up and cancelled it!). Wanda Prather "I/O, I/O, It's all about I/O" -(me) -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Richard Sims Sent: Tuesday, June 29, 2004 8:03 AM To: [EMAIL PROTECTED] Subject: Re: Speeding up my SQL statement >I thought about that, but would that help? If TSM still has to scan every >object for a match, it wouldn't help much... That's the problem, I don't know >how SQL works... Eric - Your perception is correct: if you scan a table, it will traverse the whole thing. Whereas the Backups table is the predominant (=huge) table in a TSM system, it will take a long time. Some optimization can be had through well-formulated queries, but the opportunities for doing that are rather rare. The only thing that really helps SQL performance is indexing, where short, key columns are also kept in a hash. Whereas TSM SQL is an overlay on a B-tree database, I don't believe there is any indexing opportunity, and so SQL scans are painful. Sometimes, the best thing to do is perform Query Backup from the client side, where dedicated logic gets results faster. It is often possible to accomplish that by masquerading as each defined TSM node, via VIRTUALNodename. Another approach to finding flotsam, of course, is to inspect the last backup time in filespaces, which helps narrow down the search arena. Richard Sims
