Re: Speeding up my SQL statement

[Paul Ripke]

Just running on a hunch - a really good query optimiser would
have been able to do the right thing - but we all have to
remember that TSM is designed first and foremost as a backup
and recovery tool :)

On Friday, Jul 2, 2004, at 18:40 Australia/Sydney, Loon, E.J. van -
SPLXM wrote:

Hi Paul!
Thanks for your suggestion!
I have tried your statement on our test server with just one Oracle
node. It
runs half an hour before result are returned. However, when I change
"where
node_name in (select node_name from nodes where node_name like
'%-ORC')"
into "where node_name='MYORACLENODE'" the query takes about 5 seconds
to
finish.
So, the database is indeed indexed (like Wanda pointed out, thanks
Wanda!)
and statements like like or in causes TSM not to use these indexes.
I guess it's best to issue one SQL statement per node.
Thank you all for helping!!!
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines

-----Original Message-----
From: Paul Ripke [mailto:[EMAIL PROTECTED]
Sent: Friday, July 02, 2004 02:20
To: [EMAIL PROTECTED]
Subject: Re: Speeding up my SQL statement

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:[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

--
Paul Ripke

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