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:stix AT STIX.HOMEUNIX DOT NET]
Sent: Friday, July 02, 2004 02:20
To: ADSM-L AT VM.MARIST DOT EDU
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: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
**********************************************************************
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain confidential and
privileged material intended for the addressee only. If you are not the
addressee, you are notified that no part of the e-mail or any attachment may be
disclosed, copied or distributed, and that any other action related to this
e-mail or attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately by return
e-mail, and delete this message. Koninklijke Luchtvaart Maatschappij NV (KLM),
its subsidiaries and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor responsible for
any delay in receipt.
**********************************************************************
|
