473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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
7 1717
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
Take a look at the snapshot feature.

-Visu

Nov 12 '05 #3
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
"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
3
by: Kevin Forbes | last post by:
So, I've found how to list all the tables in an Access database (as seen below) and running MS Access queries is easy (similar to executing a stored procedure), but how do I list the names of all...
11
by: Madison Kelly | last post by:
Hi all, I am new to the list and I didn't want to seem rude at all so I wanted to ask if this was okay first. I have a program I have written in perl which uses a postgresSQL database as the...
5
by: rs | last post by:
I have a table with a timestamp field which contains the date and time. ie. 9/13/2004 9:10:00 AM. I would like to split this field into 2 fields, one with just the DATE portion ie 9/13/2004 and...
7
by: mark.a.lemoine | last post by:
Our application currently interfaces to SQL Server and Oracle, we're implementing it into DB2 currently and I have a question to ask. First a little background - We're interfacing into DB2 v8 on...
7
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both...
3
by: Vik Rubenfeld | last post by:
I have to search 2 mySQL tables, and show the user a single sorted list that contains all the results from both mySQL queries. My question is, how do you get all the resulting items from both...
1
by: bhavinnaik | last post by:
Hi I am new to the IT enviro...although i've used QSL query for a while now but on simple or single queries. Here is the problem... I've got two queries looking at a set of tables to extract the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.