This is untested, but may be faster:

select node_name, filespace_name, ll_name, date(backup_date) from
backups
where node_name in (select node_name from nodes where node_name like
'%-ORC')
and ((days(current_date) - days(backup_date) >= 100)) and hl_name='//'

This should prevent a full index scan over backups.

On Wednesday, Jun 30, 2004, at 00:19 Australia/Sydney, Prather, Wanda
wrote:

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

--
Paul Ripke
Unix/OpenVMS/TSM/DBA
I love deadlines. I like the whooshing sound they make as they fly by.
-- Douglas Adams