virgo0880
asked on
TSM query for getting backups completed for last 10 days
We have to reschedule some of the clients in the Schedules to balance it. For this, first i need to work on the trends for backups. I want to know how can i get the duration of the backups completed for the nodes for last 7 days. Is there any query for that so that i can generate that report & compare.
Thanks
virgo
Thanks
virgo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
See the second-to-last line in the attachment.
select cast(START_TIME as DATE) DATE, -
cast(START_TIME as TIME) STTIME, -
(END_TIME-START_TIME) minutes as "Minutes", -
cast(ENTITY as char(21)) "Node ",-
cast(AFFECTED as dec(7)) Files, -
cast((BYTES/1024/1024) as dec(9,2)) MB -
from summary -
where ACTIVITY='BACKUP' -
and cast(start_time as date)='2010-11-01' -
order by 4, 1, 2
From the events table it look like this
select domain_name, schedule_name, node_name, actual_start, completed, completed-actual_start Duration from events where and scheduled_start >= '2010-01-01' and scheduled_start >= Current_timestamp - 240 hours and status='Completed'
The first test against 2010-01-01 is necessary to show the events in the past
order by can be added to sort the output
Rene
select domain_name, schedule_name, node_name, actual_start, completed, completed-actual_start Duration from events where and scheduled_start >= '2010-01-01' and scheduled_start >= Current_timestamp - 240 hours and status='Completed'
The first test against 2010-01-01 is necessary to show the events in the past
order by can be added to sort the output
Rene
ASKER
Hi wmp,
I have tried your query & it works perfectly for me. Also, i want to generate a report of 10 days where in i want the average of values in the fields for duration,examined,MB etc..how can i do that.
thanks
virgo
I have tried your query & it works perfectly for me. Also, i want to generate a report of 10 days where in i want the average of values in the fields for duration,examined,MB etc..how can i do that.
thanks
virgo
Always glad to help:
select -
cast(ENTITY as char(21)) "Node ", -
min(cast(START_TIME as DATE)) "DATE-LO", -
max(cast(START_TIME as DATE)) "DATE-HI", -
cast(count(*) as dec(4)) "# Runs", -
avg((END_TIME-START_TIME)minutes) "AVG Minutes", -
cast(avg(AFFECTED) as dec(9,2)) "AVG Files", -
cast(avg(BYTES)/1024/1024 as dec(10,2)) "AVG MB" -
from summary -
where ACTIVITY='BACKUP' -
and cast(start_time as date)>=cast(current timestamp as date)-10 days -
group by Entity -
order by 1, 2, 3
ASKER
Hi wmp,
Thanks for that, do you have any tutorial /lists for different TSM queries , that would be great help to me.
Thanks
virgo
Thanks for that, do you have any tutorial /lists for different TSM queries , that would be great help to me.
Thanks
virgo
Hi,
there are no ready-made or standard queries for TSM, at least not from Tivoli/IBM.
TSM's query language is kind of an emulated, somewhat reduced SQL SELECT statement containing many elements of a classic SQL SELECT.
To find out which tables you can query against issue SELECT * from SYSCAT.TABLES
and to find out a table's columns issue SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME = 'tabname'
Here is a neat introduction containing some examples -
http://www.lascon.co.uk/d005104.htm
This link has much more information about TSM than just about SELECT - take a look.
Besides that, HELP SELECT in dsmadmc should also give a basic understanding.
wmp
there are no ready-made or standard queries for TSM, at least not from Tivoli/IBM.
TSM's query language is kind of an emulated, somewhat reduced SQL SELECT statement containing many elements of a classic SQL SELECT.
To find out which tables you can query against issue SELECT * from SYSCAT.TABLES
and to find out a table's columns issue SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME = 'tabname'
Here is a neat introduction containing some examples -
http://www.lascon.co.uk/d005104.htm
This link has much more information about TSM than just about SELECT - take a look.
Besides that, HELP SELECT in dsmadmc should also give a basic understanding.
wmp
ASKER
ok i will see into that, thanks for the url. One more thing is I want the report just like the one you gave, but the thing is i want it for the nodes schedule wise i.e. schedule name, node name & then this further report. Can u help me in the query for that.
Thanks
virgo
Thanks
virgo
This has to be drawn from the EVENTS table, but this table contains only timestamps or status flags, not bytes or files.
Joining EVENTS and SUMMARY is not possible.
To get the schedule duration see renever's query in http://#a34101071 above.
If you have further questions, how about opening a new thread?
wmp
Joining EVENTS and SUMMARY is not possible.
To get the schedule duration see renever's query in http://#a34101071 above.
If you have further questions, how about opening a new thread?
wmp
ASKER
The above thread link you have given is not opening..?
Yes, sorry, don't know what happened. Seems they made a new interface here which needs a bit reworking.
I meant the fourth comment just above, in this thread, Comment No. 34101071 by renever
I meant the fourth comment just above, in this thread, Comment No. 34101071 by renever
ASKER
I worked on the query & got it working . This is the query i customized :
select entity as Node_name, schedule_name, min(cast(START_TIME as DATE)) "DATE-LO",max(cast(START_T IME as DATE)) "DATE-HI", cast(count(*) as dec(4)) "# Runs", avg((END_TIME-START_TIME)m inutes) "AVG Minutes", cast(avg(AFFECTED) as dec(9,2)) "AVG Files", cast(avg(BYTES)/1024/1024 as dec(10,2)) "AVG MB" from summary where ACTIVITY='BACKUP' and cast(start_time as date)>=cast(current timestamp as date)-10 days group by schedule_name, Entity order by 2
Also, i want some explainations about the query as I am not that much familier with SQL statements. What do you mean by "cast" & entity here, if you can explain me.
Thanks
virgo
select entity as Node_name, schedule_name, min(cast(START_TIME as DATE)) "DATE-LO",max(cast(START_T
Also, i want some explainations about the query as I am not that much familier with SQL statements. What do you mean by "cast" & entity here, if you can explain me.
Thanks
virgo
ENTITY is a column of SUMMARY
cast is used for reformatting e.g. a timestamp to DATE, a number to contain a certain amount of decimal places etc.
Your original requirement was a particular query and not an exhaustive SQL seminar.
If you feel your original question answered I think we should finish this thread here.
cast is used for reformatting e.g. a timestamp to DATE, a number to contain a certain amount of decimal places etc.
Your original requirement was a particular query and not an exhaustive SQL seminar.
If you feel your original question answered I think we should finish this thread here.
ASKER
Yes, I agree with you regarding the closing of thread, i was just asking the information pertaining to my query. Thats fine.
thanks
virgo
thanks
virgo
ASKER
thanks
virgo