Svenson's  Bulletin Board Forum Index Svenson's Bulletin Board
Bulletin Board for boat building and model airplane building enthusiasts.
 
AlbumAlbum   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SQL to display snapshot data collected in dba tables.

 
Post new topic   Reply to topic    Svenson's Bulletin Board Forum Index -> DB2
View previous topic :: View next topic  
Author Message
craig
Site Admin


Joined: 19 Feb 2006
Posts: 40

PostPosted: Tue Feb 28, 2006 6:33 am    Post subject: SQL to display snapshot data collected in dba tables. Reply with quote

Select sn.snapshot_timestamp, sn.agent_id as agnt, appl_name, auth_id as auth,
execution_id as clntid, sn.uow_log_space_used as logspc,
case when sn.pool_index_l_reads <> 0 then
cast(100*(1-(cast(sn.pool_index_p_reads as float)/cast(sn.pool_index_l_reads as float))) as int)
else 1 end as IXRDP,
case when sn.pool_data_l_reads <> 0 then
cast(100*(1-(cast(sn.pool_data_p_reads as float)/cast(sn.pool_data_l_reads as float))) as int)
else 1 end as DtRDP,
sn.total_sorts as sorts, elapsed_exec_time_ms as exctme, rows_selected as slctd,
rows_inserted as nsrtd, rows_updated as pdtd, rows_deleted as dltd, case stmt_operation
when 1 then ' SQL Prepare '
when 2 then ' SQL Execute '
when 3 then ' SQL Execute Immediate '
when 4 then ' SQL Open '
when 5 then ' SQL Fetch '
when 6 then ' SQL Close '
when 7 then ' SQL Describe '
when 8 then ' SQL Static Commit '
when 9 then ' SQL Static Rollback'
when 10 then ' SQL Free Locator'
when 11 then ' Prepare to commit (2-phase commit)'
when 12 then ' Call a stored procedure '
when 15 then ' SELECT statement'
when 16 then ' Prep. and open (DB2 Connect only) '
when 17 then ' Prep. and execute (DB2 Connect)'
when 18 then ' Compile (DB2 Connect only) '
when 19 then ' SET statement'
when 20 then ' Runstats '
when 21 then ' Reorg '
when 22 then ' Rebind package '
when 23 then ' Redistribute '
when 24 then ' Get Table Authorization '
when 25 then ' Get Administrative Authorization '
when 26 then ' DRDA Get Next Chunk command '
when 27 then ' Drop Package '
end as operation ,st.stmt_text
from DBA.SNAPSHOT_APPL sn, DBA.SNAPSHOT_APPL_INFO si, DBA.SNAPSHOT_STATEMENT st
where sn.snapshot_timestamp > current timestamp - 1 hour
-- sn.snapshot_timestamp > '2005-06-30-08.00.00.00'
-- and sn.snapshot_timestamp < '2005-06-30-08.59.00.00'
-- sn.agent_id = 156
-- and sn.uow_log_space_used > 3000
-- and sn.uow_stop_time is null
and sn.agent_id = si.agent_id
and sn.agent_id = st.agent_id
and si.snapshot_timestamp >= sn.snapshot_timestamp - 1 minute
and si.snapshot_timestamp <= sn.snapshot_timestamp + 1 minute
and sn.snapshot_timestamp >= st.snapshot_timestamp - 1 minute
and sn.snapshot_timestamp <= st.snapshot_timestamp + 1 minute
ORDER BY sn.SNAPSHOT_TIMESTAMP desc,
sn.AGENT_ID;


Last edited by craig on Thu Feb 12, 2009 11:15 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
craig
Site Admin


Joined: 19 Feb 2006
Posts: 40

PostPosted: Tue May 23, 2006 12:28 pm    Post subject: sql to display snapshot data for log space used and statemen Reply with quote

Select sn.snapshot_timestamp, sn.agent_id as agnt, appl_name, auth_id as auth,
execution_id as clntid, sn.uow_log_space_used as logspc,
case when sn.pool_index_l_reads <> 0 then
cast(100*(1-(cast(sn.pool_index_p_reads as float)/cast(sn.pool_index_l_reads as float))) as int)
else 1 end as IXRDP,
case when sn.pool_data_l_reads <> 0 then
cast(100*(1-(cast(sn.pool_data_p_reads as float)/cast(sn.pool_data_l_reads as float))) as int)
else 1 end as DtRDP,
sn.total_sorts as sorts, elapsed_exec_time_ms as exctme, rows_selected as slctd,
rows_inserted as nsrtd, rows_updated as pdtd, rows_deleted as dltd, st.stmt_text
from DBA.SNAPSHOT_APPL sn, DBA.SNAPSHOT_APPL_INFO si, DBA.SNAPSHOT_STATEMENT st
where
sn.snapshot_timestamp > '2006-04-25 02:00:00.00'
and sn.snapshot_timestamp < '2007-04-25 02:13:00.00'
-- and (stmt_text is not null and sn.uow_log_space_used <> 0)
-- sn.agent_id = 156
-- and sn.uow_log_space_used > 3000
-- and sn.uow_stop_time is null
and sn.agent_id = si.agent_id
and sn.agent_id = st.agent_id
and si.snapshot_timestamp >= sn.snapshot_timestamp - 1 minute
and si.snapshot_timestamp <= sn.snapshot_timestamp + 1 minute
and sn.snapshot_timestamp >= st.snapshot_timestamp - 1 minute
and sn.snapshot_timestamp <= st.snapshot_timestamp + 1 minute
ORDER BY sn.SNAPSHOT_TIMESTAMP desc,
sn.AGENT_ID;
Back to top
View user's profile Send private message Send e-mail
Jamez99



Joined: 29 Jul 2013
Posts: 1
Location: UAS

PostPosted: Mon Jul 29, 2013 10:53 pm    Post subject: Ynt: Garanti &#350;artlar&#305; Reply with quote

I'm on it started working and after some peplums on dell forums I found out that it was possible something with the bios
_________________
"ONe Day I will be Part of your World.")
Back to top
View user's profile Send private message AIM Address MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    Svenson's Bulletin Board Forum Index -> DB2 All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group