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/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com
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" <ADSM-L AT VM.MARIST DOT EDU> 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.
|
