Re: SQL Question
2003-05-22 09:50:19
Thanks everyone for the great examples. I tried everyone's! John, using
the VOLHIST was the 'least' resource intensive of all examples. In fact it
took only seconds to run. I knew that someone out there would take a whole
different approach. It is amazing to me the way different people look at
the same path and see such different roads, especially in this profession!
Thanks again everyone!
Shannon
Madison Gas & Electric Co.
e-mail sbach AT mge DOT com
John Naylor
<john.naylor@SCOTTISH-SOUT To: ADSM-L AT
VM.MARIST DOT EDU
HERN.CO.UK> cc:
Sent by: "ADSM: Dist Stor Subject: Re: SQL
Question
Manager"
<ADSM-L AT VM.MARIST DOT EDU>
05/22/2003 06:42 AM
Please respond to "ADSM:
Dist Stor Manager"
Shannon,
Why not use the volhistory table.
This will give you your new scratch storage pool vols for previous 24 hours
SELECT COUNT(*) FROM VOLHISTORY WHERE TYPE='STGNEW' -
AND DATE_TIME>(CURRENT_TIMESTAMP-(1 DAY))
This does not give you database volumes used from scratch but the message
1340 from the activity log does not include them either, and if you use the
volume
history table you can also include TYPE='BACKUPINCR' and
TYPE='BACKUPFULL'
John
Shannon Bach <sbach AT mge DOT com> on 05/21/2003 05:49:15 PM
Please respond to "ADSM: Dist Stor Manager" <adsm-l AT vm.marist DOT edu>
To: adsm-l AT vm.marist DOT edu
cc: (bcc: John Naylor/HAV/SSE)
Subject: SQL Question
I have a SQL question I have spent hours trying to figure out but I can't
seem to get it. What I am trying to do is discover how many scratch tapes
we mount in 24 hours. Because we are an MVS OS/390 the information is a
little harder to get. I've tried several different ways but always get
some sort of syntax. Below I will list my examples, maybe someone can lead
me in the right direction.
What I can do is;
tsm: SERVER>select count(*) as SCR from actlog where msgno=1340
SCR
-----------
1427
(I am going by the MSGNO in Actlog
Activity Log
------------
Date and Time Message
-----------------------------------------------------------------------------------------
05/21/2003 07:06:27 ANR1340I Scratch volume 100024 is now defined in
storage pool OFFSITE01.
What I can't figure out is how to get scratch volumes defined only within
past 24 hours
1. select count(*) as TMS from actlog where msgno=1340 and BEGIND=TODAY-1
2. select count(*) as TMS from actlog where msgno=1340 and
date_time=current_date-1
3. select count(*) as TMS from actlog where msgno=1340 and
(dayofyear)timestamp=current_date-1
None of these work. I am probably going about this the wrong way. I did
try to get the info out of the Summary but couldn't get that to fly either.
1.tsm: SERVER>select count(*) as SCR from summary where activity='TAPE
MOUNT' and (current_art_time)hours<24
ANR2916E The SQL data types INTERVAL HOUR(9) and INTEGER are incompatible
for operator '<'.
Any ideas?
Thanks,
Shannon
Madison Gas & Electric Co.
e-mail sbach AT mge DOT com
**********************************************************************
The information in this E-Mail is confidential and may be legally
privileged. It may not represent the views of Scottish and Southern
Energy plc.
It is intended solely for the addressees. Access to this E-Mail by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
Any unauthorised recipient should advise the sender immediately of
the error in transmission.
Scottish Hydro-Electric, Southern Electric, SWALEC and S+S
are trading names of the Scottish and Southern Energy Group.
**********************************************************************
|
|
|