If you are getting actual results (and better yet, correct results) then there is nothing "wrong" per se with your syntax.
The tables presented by the SELECT statement do not really exist as such in the TSM server database. Rather, these tables are constructed "on the fly" during SELECT processing. For very large tables, the processing time can be substantial. On the other hand, the built-in QUERY commands are optimized and read directly from the actual TSM database structures. If your activity log contains a large number of records, then this is the most likely explanation for the difference in run times between SELECT and QUERY. Try running this: select count(*) from actlog How long does it take to run? What is the count (number of records in the ACTLOG table)? If even this simple SELECT statement takes a while to run and there are a lot of records, then I think the explanation I provided above is the answer. In this case, there isn't much you can do other than try to reduce the size of your activity log by reducing the length of time you keep activity log records via the SET ACTLOGRETENTION command. Reducing the retention will reduce the size of the table and that *might* improve performance. But do not reduce the retention merely to improve SELECT performance unless you truly do not require keeping the records for as long as they are being kept today. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. "Good enough" is the enemy of excellence. "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> wrote on 07/09/2004 12:48:50: > Hello Everyone, > > I have a quick question in reference to using select statements to query the > activity log. For some reason the regular select statement would take forever > to run a simple query? How does TSM query the activity log? I'm still quite a > novice at selects statements so maybe I'm issuing the wrong syntax. > > Reason why I would like to use the select statement vs. q act is because TSM > Reporting tool allows me to generate alerts by setting up notification rules > which queries the server using select statements. > > > Examples below: > > TSM QUERY: (Takes about a blink of an eye) > TSM_SERVER_01>q act msg=0986 begintime=14:30:00 endtime=15:00:00 > Date/Time Message > -------------------- ---------------------------------------------------------- > 07/09/04 14:35:21 ANR0986I Process 7544 for SPACE RECLAMATION running in the > BACKGROUND processed 36047 items for a total of > 4,279,628,603 bytes with a completion state of SUCCESS at > 14:35:21. > 07/09/04 14:42:05 ANR0986I Process 7545 for SPACE RECLAMATION running in the > BACKGROUND processed 33537 items for a total of > 8,644,875,214 bytes with a completion state of SUCCESS at > 14:42:05. > > > > SQL QUERY: (Takes about 20 mins!!!!!) > TSM_SERVER_01>select date_time,message from actlog where date_time between > '07/09/04 14:30:00' and ' 07/09/04 15:00:00' and msgno=0986 > > ANR2963W This SQL query may produce a very large result table, or may require > a significant amount of time to compute. > > Do you wish to proceed? (Yes (Y)/No (N)) y > > DATE_TIME MESSAGE > ------------------ ------------------ > 2004-07-09 ANR0986I Process > 14:35:21.000000 7544 for SPACE > RECLAMATION > running in the > BACKGROUND > processed 36047 > items for a total > of 4,279,628,603 > bytes with a > completion state > of SUCCESS at > 14:35:21. > 2004-07-09 ANR0986I Process > 14:42:05.000000 7545 for SPACE > RECLAMATION > running in the > BACKGROUND > processed 33537 > items for a total > of 8,644,875,214 > bytes with a > completion state > of SUCCESS at > 14:42:05. > > > My DB Maximum Reduction space is well over 3 gigs so I know that's not an issue. > > Any suggestions and comments would be deeply appreciated! > > Thanks in advance! > Calvin > > > > --------------------------------------- > > The information contained in this e-mail message, and any attachment thereto, > is confidential and may not be disclosed without our express permission. If > you are not the intended recipient or an employee or agent responsible for > delivering this message to the intended recipient, you are hereby notified > that you have received this message in error and that any review, > dissemination, distribution or copying of this message, or any attachment > thereto, in whole or in part, is strictly prohibited. If you have received > this message in error, please immediately notify us by telephone, fax or e- > mail and delete the message and all of its attachments. Thank you. > > Every effort is made to keep our network free from viruses. You should, > however, review this e-mail message, as well as any attachment thereto, for > viruses. We take no responsibility and have no liability for any computer > virus which may be transferred via this e-mail message.
