By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

How to extract a list of top running queries on a database on a single day

P: n/a
Hi,

the below list shows the top running queries in oracle on a database on
a single day. Can we do something similar in DB2 v8 where we get a list
of top 10 queries in execution time , or CPU utilisation or DISK I/O
every day. Setting up an event monitor is not feasible. Are there any
other options which is feasible?

thanks
raj

DISK_READS BUFFER_GETS EXECUTIONS SQL_TEXT

---------- ----------- ----------
----------------------------------------------------------------

334919 4103 458 SELECT vs.status||' '||vs.sid||' '||
vs.serial#||' '|| vs.usern
ame||' '|| vs.osuser||' '||
vs.terminal||' '|| vs.last_call_et

||' '|| va.disk_reads sessinfo,
vt.sql_text FROM v$sqltext vt,

v$session vs, v$sqlarea va WHERE
vt.address= vs.sql_address and

vt.hash_value = vs.sql_hash_value
and va.address = vs.sql_add
ress and va.hash_value =
vs.sql_hash_value and rawtohex(vs.sql

_address) <> '00' and vs.username
not in ('SYSTEM','SYS') and
vs.status='ACTIVE' and
(vs.last_call_et >2*60) A

ND va.disk_reads > 12000 ORDER BY
vs.last_call_et desc, va.dis
k_reads desc, vt.hash_value,vt.piece

110345 726082 81698 SELECT
deferredpriority,defertodatetime,enddatetime,start datetim
e,status,runplan,requesttype,offeringname,interact iontype,descri
ption,statedatetime,createdatetime,supervisorid,re questsequencen

o,ccsuserid,eicaseid,state,id FROM
v_eicaserequest WHERE eicasei
d = :eicaseid order by
requestsequenceno asc

81920 614433 398922 SELECT
detailkey,detailvalue,eiactivityid,heading,id FROM eiacti

vitydetail WHERE eiactivityid =
:eiactivityid

65088 66471 6 SELECT
documentid,countrycode,groupnum,statecode,contract number,
ccsuserid,artifactid,contractsuffix,casenumber,let tercd,destinat
ionuserid,destinationgrpid,requestdate,channeltype ,statuscd FROM

v_corspsearch where
destinationuserid = -2147483447 and status

cd = '7' order by requestdate desc

63986 1542941 282250 SELECT
deliveryaddressline,firstname,infoattnline,lastnam e,middl
ename1,countrynamecode,cityname,alternatelocationl ine,participan
ttype,participantrefrnc,participantaddress,initiat orflag,ctiiico
de,languageid,eventtype,tin,organization,enddateti me,emailaddres
s,channelspecid,eicaseid,middlename2,middlename3,n amegenerationc
ode,nameprefixcode,namesuffixcode,phoneareacode,so urce,faxtypeco
de,faxextension,faxlinecode,phoneextension,sourcec d,altphonearea
code,postalcode,zipdelsegmentcode,zipdelofficecode ,tintype,state
code,sexcode,phonetypecode,phonelinecode,altphonet ypecode,altpho
nelinecode,altphoneextension,altphoneexchangecode, phoneexchangec
ode,faxareacode,faxexchangecode,datetimestamp,eico mmunicationcom
id,comaddress,comioflag,comtype,collectcallflag,eb cinteractionid

,id FROM v_eicommunication WHERE
ebcinteractionid = :ebcinteract

ionid order by datetimestamp asc

48826 27667706 7158244 INSERT INTO eiactivitydetail
(detailkey,detailvalue,eiactivityid

,heading,id) VALUES
(:detailkey,:detailvalue,:eiactivityid,:head

ing,:id)

43430 44314 7 SELECT
documentid,countrycode,groupnum,statecode,contract number,
ccsuserid,artifactid,contractsuffix,casenumber,let tercd,destinat
ionuserid,destinationgrpid,requestdate,channeltype ,statuscd FROM

v_corspsearch where
destinationuserid = -2147481907 and status

cd = '7' order by requestdate desc

26610 163938 128670 SELECT
ebcinteractionid,startdatetime,artifactid,reasoncd ,status
,runplanid,reportableind,eicasecontractid,enddatet ime,lob,name,e

icaseservicerid,id FROM eiactivity
WHERE eicaseservicerid = :eic
aseservicerid

22923 952419 55353 INSERT INTO eicommunication
(sexcode,tintype,postalcode,altphone
exchangecode,altphonelinecode,altphoneextension,su rveyind,faxlin
ecode,faxexchangecode,faxareacode,sourcecd,altphon eareacode,stat
ecode,ntfysalesofficeind,deliveryaddressline,lastn ame,middlename
1,infoattnline,phoneexchangecode,tin,organization, emailaddress,e
nddatetime,channelspecid,eicaseid,phonelinecode,ph oneextension,f
irstname,countrynamecode,cityname,alternatelocatio nline,eventtyp
e,languageid,phoneareacode,comaddress,comtype,comi oflag,datetime

stamp,id) VALUES
(:sexcode,:tintype,:postalcode,:altphoneexchang
ecode,:altphonelinecode,:altphoneextension,:survey ind,:faxlineco
de,:faxexchangecode,:faxareacode,:sourcecd,:altpho neareacode,:st
atecode,:ntfysalesofficeind,:deliveryaddressline,: lastname,:midd
lename1,:infoattnline,:phoneexchangecode,:tin,:org anization,:ema
iladdress,:enddatetime,:channelspecid,:eicaseid,:p honelinecode,:
phoneextension,:firstname,:countrynamecode,:cityna me,:alternatel
ocationline,:eventtype,:languageid,:pho

21713 22158 2 SELECT
documentid,countrycode,groupnum,statecode,contract number,
ccsuserid,artifactid,contractsuffix,casenumber,let tercd,destinat
ionuserid,destinationgrpid,requestdate,channeltype ,statuscd FROM

v_corspsearch where
destinationuserid = -2138474351 and status

cd = '7' order by requestdate desc

20131 6837664 1235704 INSERT INTO ebcinteractioncom
(eicommunicationid,ebcinteractioni

d,id) VALUES
(:eicommunicationid,:ebcinteractionid,:id)

15821 974277 634534 SELECT
creationdatetime,status,lastmodified,origsystem,no tes,ref
erralind,complaintind,callupind,description,casenu mber,mergedtoc

aseid,grouprunplan,updatedatetime,id
FROM eicase WHERE casenumbe
r = :casenumber

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
You want the dynamic sql table function:

sysproc.snapshot_dyn_sql('databasename', NULL)

You can use SQL to order by number of exectuions, and select only 10
rows to be returned etc.

rajdb2 wrote:
Hi,

the below list shows the top running queries in oracle on a database on
a single day. Can we do something similar in DB2 v8 where we get a list
of top 10 queries in execution time , or CPU utilisation or DISK I/O
every day. Setting up an event monitor is not feasible. Are there any
other options which is feasible?

thanks
raj

DISK_READS BUFFER_GETS EXECUTIONS SQL_TEXT

---------- ----------- ----------
----------------------------------------------------------------

334919 4103 458 SELECT vs.status||' '||vs.sid||' '||
vs.serial#||' '|| vs.usern
ame||' '|| vs.osuser||' '||
vs.terminal||' '|| vs.last_call_et

||' '|| va.disk_reads sessinfo,
vt.sql_text FROM v$sqltext vt,

v$session vs, v$sqlarea va WHERE
vt.address= vs.sql_address and

vt.hash_value = vs.sql_hash_value
and va.address = vs.sql_add
ress and va.hash_value =
vs.sql_hash_value and rawtohex(vs.sql

_address) <> '00' and vs.username
not in ('SYSTEM','SYS') and
vs.status='ACTIVE' and
(vs.last_call_et >2*60) A

ND va.disk_reads > 12000 ORDER BY
vs.last_call_et desc, va.dis
k_reads desc, vt.hash_value,vt.piece

110345 726082 81698 SELECT
deferredpriority,defertodatetime,enddatetime,start datetim
e,status,runplan,requesttype,offeringname,interact iontype,descri
ption,statedatetime,createdatetime,supervisorid,re questsequencen

o,ccsuserid,eicaseid,state,id FROM
v_eicaserequest WHERE eicasei
d = :eicaseid order by
requestsequenceno asc

81920 614433 398922 SELECT
detailkey,detailvalue,eiactivityid,heading,id FROM eiacti

vitydetail WHERE eiactivityid =
:eiactivityid

65088 66471 6 SELECT
documentid,countrycode,groupnum,statecode,contract number,
ccsuserid,artifactid,contractsuffix,casenumber,let tercd,destinat
ionuserid,destinationgrpid,requestdate,channeltype ,statuscd FROM

v_corspsearch where
destinationuserid = -2147483447 and status

cd = '7' order by requestdate desc

63986 1542941 282250 SELECT
deliveryaddressline,firstname,infoattnline,lastnam e,middl
ename1,countrynamecode,cityname,alternatelocationl ine,participan
ttype,participantrefrnc,participantaddress,initiat orflag,ctiiico
de,languageid,eventtype,tin,organization,enddateti me,emailaddres
s,channelspecid,eicaseid,middlename2,middlename3,n amegenerationc
ode,nameprefixcode,namesuffixcode,phoneareacode,so urce,faxtypeco
de,faxextension,faxlinecode,phoneextension,sourcec d,altphonearea
code,postalcode,zipdelsegmentcode,zipdelofficecode ,tintype,state
code,sexcode,phonetypecode,phonelinecode,altphonet ypecode,altpho
nelinecode,altphoneextension,altphoneexchangecode, phoneexchangec
ode,faxareacode,faxexchangecode,datetimestamp,eico mmunicationcom
id,comaddress,comioflag,comtype,collectcallflag,eb cinteractionid

,id FROM v_eicommunication WHERE
ebcinteractionid = :ebcinteract

ionid order by datetimestamp asc

48826 27667706 7158244 INSERT INTO eiactivitydetail
(detailkey,detailvalue,eiactivityid

,heading,id) VALUES
(:detailkey,:detailvalue,:eiactivityid,:head

ing,:id)

43430 44314 7 SELECT
documentid,countrycode,groupnum,statecode,contract number,
ccsuserid,artifactid,contractsuffix,casenumber,let tercd,destinat
ionuserid,destinationgrpid,requestdate,channeltype ,statuscd FROM

v_corspsearch where
destinationuserid = -2147481907 and status

cd = '7' order by requestdate desc

26610 163938 128670 SELECT
ebcinteractionid,startdatetime,artifactid,reasoncd ,status
,runplanid,reportableind,eicasecontractid,enddatet ime,lob,name,e

icaseservicerid,id FROM eiactivity
WHERE eicaseservicerid = :eic
aseservicerid

22923 952419 55353 INSERT INTO eicommunication
(sexcode,tintype,postalcode,altphone
exchangecode,altphonelinecode,altphoneextension,su rveyind,faxlin
ecode,faxexchangecode,faxareacode,sourcecd,altphon eareacode,stat
ecode,ntfysalesofficeind,deliveryaddressline,lastn ame,middlename
1,infoattnline,phoneexchangecode,tin,organization, emailaddress,e
nddatetime,channelspecid,eicaseid,phonelinecode,ph oneextension,f
irstname,countrynamecode,cityname,alternatelocatio nline,eventtyp
e,languageid,phoneareacode,comaddress,comtype,comi oflag,datetime

stamp,id) VALUES
(:sexcode,:tintype,:postalcode,:altphoneexchang
ecode,:altphonelinecode,:altphoneextension,:survey ind,:faxlineco
de,:faxexchangecode,:faxareacode,:sourcecd,:altpho neareacode,:st
atecode,:ntfysalesofficeind,:deliveryaddressline,: lastname,:midd
lename1,:infoattnline,:phoneexchangecode,:tin,:org anization,:ema
iladdress,:enddatetime,:channelspecid,:eicaseid,:p honelinecode,:
phoneextension,:firstname,:countrynamecode,:cityna me,:alternatel
ocationline,:eventtype,:languageid,:pho

21713 22158 2 SELECT
documentid,countrycode,groupnum,statecode,contract number,
ccsuserid,artifactid,contractsuffix,casenumber,let tercd,destinat
ionuserid,destinationgrpid,requestdate,channeltype ,statuscd FROM

v_corspsearch where
destinationuserid = -2138474351 and status

cd = '7' order by requestdate desc

20131 6837664 1235704 INSERT INTO ebcinteractioncom
(eicommunicationid,ebcinteractioni

d,id) VALUES
(:eicommunicationid,:ebcinteractionid,:id)

15821 974277 634534 SELECT
creationdatetime,status,lastmodified,origsystem,no tes,ref
erralind,complaintind,callupind,description,casenu mber,mergedtoc

aseid,grouprunplan,updatedatetime,id
FROM eicase WHERE casenumbe
r = :casenumber

Nov 12 '05 #2

P: n/a
Take a look at the snapshot feature.

-Visu

Nov 12 '05 #3

P: n/a
The default monitor switches at the instance level are not turned on.
So the snap shot is ruled out. Is there any other way?

Nov 12 '05 #4

P: n/a
"rajdb2" <vr************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
The default monitor switches at the instance level are not turned on.
So the snap shot is ruled out. Is there any other way?

You only need to turn on the Statement monitor and (I believe) the timestamp
monitor. Timestamp monitor is on by default.

Just do it.
Nov 12 '05 #5

P: n/a
Ian
rajdb2 wrote:
The default monitor switches at the instance level are not turned on.
So the snap shot is ruled out. Is there any other way?


update monitor switches using statement on ...;
!sleep 86400;
get snapshot for dynamic sql on <db>;

Nov 12 '05 #6

P: n/a
The problem is i cant turn on the monitor switches at the instance
level. So i will need to turn on the statement every day for a session,
and i should not close that session and keep it open to collect the
information i need. it is not feasible. You guys get what i am saying?

Nov 12 '05 #7

P: n/a
Ian
rajdb2 wrote:
The problem is i cant turn on the monitor switches at the instance
level. So i will need to turn on the statement every day for a session,
and i should not close that session and keep it open to collect the
information i need. it is not feasible. You guys get what i am saying?


Why is this not feasible? Use cron (or an equivalent if you aren't on
unix/linux) to run a script in the background. You don't have to open
a window on your desktop and wait for 24 hours!
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.