I can't say which messages you can choose/afford to ignore, but a couple
of things that might help reduce the time:
1) Change the "severity" condition from:
severity='E' or severity='W'
to
severity in ('E', 'W')
Note: now you don't need the parentheses around the severity criterion
since there is no more OR operator. I also removed the extraneous
parentheses around the date criterion, but I don't expect that to make a
significant difference.
select date_time, msgno, message
from actlog
where severity in ('E', 'W')
and date_time>current_timestamp-1 day
2) The date_time column is the first indexed column in the table, so try
making that the first criterion in the WHERE clause:
select date_time, msgno, message
from actlog
where date_time>current_timestamp-1 day
and severity in ('E', 'W')
Your mileage may vary, but I found on my system that this cut the amount
of time in half for the command to run (from 3.5 seconds to around 1.6 -
1.7 seconds).
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.
Joni Moyer <joni.moyer AT HIGHMARK DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
07/07/2003 08:18
Please respond to "ADSM: Dist Stor Manager"
To: ADSM-L AT VM.MARIST DOT EDU
cc:
Subject: Re: MVS select statement
Thank you all for your input!
The parentheses worked! Are there any suggestions on what msgno's I
should
be looking for or are more common than others? This select statement ran
forever and it was suggested that I narrow down my search, but I wasn't
sure what I should/shouldn't be looking for on a daily basis.
Thanks again!
Joni Moyer
Systems Programmer
joni.moyer AT highmark DOT com
(717)975-8338
Andrew Raibeck
<storman AT US DOT IBM.C To: ADSM-L AT VM.MARIST
DOT EDU
OM> cc:
Sent by: "ADSM: Subject: Re: MVS select
statement
Dist Stor
Manager"
<[email protected]
.EDU>
07/07/2003 10:23
AM
Please respond to
"ADSM: Dist Stor
Manager"
AND has precendence over OR, so your "where" criteria are being evaluated
like this:
where cond1 OR (cond2 AND cond3)
In your case, "cond1" is for the messages of severity 'E', so those are
displayed regardless of the date.
Put parentheses around the ORed conditions to fix the problem:
where (cond1 OR cond2) AND cond3
i.e.
select date_time, msgno, message
from actlog
where (severity='E' or severity='W')
and date_time>(current_timestamp-(1 day))
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.
Joni Moyer <joni.moyer AT HIGHMARK DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
07/07/2003 07:14
Please respond to "ADSM: Dist Stor Manager"
To: ADSM-L AT VM.MARIST DOT EDU
cc:
Subject: MVS select statement
Hello all!
I have been trying to set up a select statement to look for all of the
error and warning messages within the past 24 hours. Here is what I have
come up with:
select date_time, msgno, message
from actlog
where severity='E' or severity='W'
and date_time>(current_timestamp-(1 day))
My problem is that it goes through the entire 14 days of the activity log
that I have and ignores the last statement. Any suggestions?
Thanks in advance!!!
**************************************
Joni Moyer
Systems Programmer
joni.moyer AT highmark DOT com
(717)975-8338
|