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:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Richard Sims
Sent: Tuesday, June 29, 2004 8:03 AM
To: ADSM-L AT VM.MARIST DOT EDU
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