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 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. **********************************************************************
