Re: SQL Plan Cache Statements - NOT LIKE ?

I can definitely work with that, although I don't see the point of the
STRDBMON. IBM's layout for the result set leaves a lot to be desired, but
by comparing it to SQL Plan Cache Statements I think I got it.
- https://www.ibm.com/docs/en/i/7.5?topic=services-dump-plan-cache-procedure
- https://www.ibm.com/docs/en/i/7.5?topic=formats-sql-table

Here is what I came up with to query the Db2 plan cache.

CALL QSYS2.DUMP_PLAN_CACHE('QTEMP','DUMP_PLAN');

with Query as (
select QQTIME as Last_Time_Run
, *cast*(QVP15D / *1000000* as *numeric*(*15*,*4*)) as
Most_Expensive_Time
, *cast*(QQI4 / *1000* as *numeric*(*15*,*4*)) as Total_Processing_Time
, QVP15F as Total_Times_Run
, *cast*(QQI4 / QVP15F / *1000* as *numeric*(*15*,*4*)) as
Average_Processing_Time
, QQ1000 as Statement
, QVC102 as Plan_Creation_User_Name
, QQJOB as Job_Name
, QQUSER as Job_User
from QTEMP.DUMP_PLAN P
where QVP15D <> *0* and QQ1000 is not null
order by QVP15F desc
fetch first *25* rows only
)
select * from Query
order by Most_Expensive_Time desc

Thank you


date: Fri, 19 May 2023 09:50:49 -0400

from: Rob Berendt <robertowenberendt@xxxxxxxxx>
subject: Re: SQL Plan Cache Statements - NOT LIKE ?

You mean something like
*cl:* *STRDBMON* OUTFILE(ROB/ROBDBMON) JOB(*ALL) FTRFILE((ERPLXF/*ALL));
-- do some sql
*cl:* *ENDDBMON* JOB(*ALL);

CALL QSYS2.DUMP_PLAN_CACHE('ROB','CACHEDUMP');
SELECT *
FROM ROB.CACHEDUMP
WHERE QQTIME > *CURRENT TIMESTAMP* - *1* *HOUR*
AND QQJOB='QZDASOINIT' AND QQUSER='QUSER' AND QQJNUM=*352046*;