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 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
Take a look at the snapshot feature.
-Visu
The default monitor switches at the instance level are not turned on.
So the snap shot is ruled out. Is there any other way?
"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.
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>;
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?
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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.
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |