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,startdatetim

e,status,runplan,requesttype,offeringname,interactiontype,descri

ption,statedatetime,createdatetime,supervisorid,requestsequencen

                                 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 =

    65088       66471          6 SELECT
documentid,countrycode,groupnum,statecode,contractnumber,

ccsuserid,artifactid,contractsuffix,casenumber,lettercd,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,lastname,middl

ename1,countrynamecode,cityname,alternatelocationline,participan

ttype,participantrefrnc,participantaddress,initiatorflag,ctiiico

de,languageid,eventtype,tin,organization,enddatetime,emailaddres

s,channelspecid,eicaseid,middlename2,middlename3,namegenerationc

ode,nameprefixcode,namesuffixcode,phoneareacode,source,faxtypeco

de,faxextension,faxlinecode,phoneextension,sourcecd,altphonearea

code,postalcode,zipdelsegmentcode,zipdelofficecode,tintype,state

code,sexcode,phonetypecode,phonelinecode,altphonetypecode,altpho

nelinecode,altphoneextension,altphoneexchangecode,phoneexchangec

ode,faxareacode,faxexchangecode,datetimestamp,eicommunicationcom

id,comaddress,comioflag,comtype,collectcallflag,ebcinteractionid

                                 ,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,contractnumber,

ccsuserid,artifactid,contractsuffix,casenumber,lettercd,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,enddatetime,lob,name,e

                                 icaseservicerid,id FROM eiactivity
WHERE eicaseservicerid = :eic
                                 aseservicerid

    22923      952419      55353 INSERT INTO eicommunication
(sexcode,tintype,postalcode,altphone

exchangecode,altphonelinecode,altphoneextension,surveyind,faxlin

ecode,faxexchangecode,faxareacode,sourcecd,altphoneareacode,stat

ecode,ntfysalesofficeind,deliveryaddressline,lastname,middlename

1,infoattnline,phoneexchangecode,tin,organization,emailaddress,e

nddatetime,channelspecid,eicaseid,phonelinecode,phoneextension,f

irstname,countrynamecode,cityname,alternatelocationline,eventtyp

e,languageid,phoneareacode,comaddress,comtype,comioflag,datetime

                                 stamp,id) VALUES
(:sexcode,:tintype,:postalcode,:altphoneexchang

ecode,:altphonelinecode,:altphoneextension,:surveyind,:faxlineco

de,:faxexchangecode,:faxareacode,:sourcecd,:altphoneareacode,:st

atecode,:ntfysalesofficeind,:deliveryaddressline,:lastname,:midd

lename1,:infoattnline,:phoneexchangecode,:tin,:organization,:ema

iladdress,:enddatetime,:channelspecid,:eicaseid,:phonelinecode,:

phoneextension,:firstname,:countrynamecode,:cityname,:alternatel

ocationline,:eventtype,:languageid,:pho

    21713       22158          2 SELECT
documentid,countrycode,groupnum,statecode,contractnumber,

ccsuserid,artifactid,contractsuffix,casenumber,lettercd,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,notes,ref

erralind,complaintind,callupind,description,casenumber,mergedtoc

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