News
Abstract
This procedure returns details from a plan cache snapshot in the form of an SQL table or a result set.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Performance Enhancements > QSYS2.EXTRACT_STATEMENTS procedure
See the IBM Documentation for details: EXTRACT_STATEMENTS procedure
Note: Extraction takes time, this is not a quick operation.
For parameters 3, 4 & 5 use this resource as a coding guide: Database monitor format QQQ1000
For example, extract the 100 most recent statements from monitor APRIL2014:
CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS', 'APRIL2014');
CALL QSYS2.EXTRACT_STATEMENTS('SNAPSHOTS', 'APRIL2014', '*AUDIT',
'AND QQC21 NOT IN
(''CH'', ''CL'', ''CN'', ''DE'', ''DI'', ''DM'', ''HC'', ''HH'', ''JR'', ''FE'',
''PD'', ''PR'', ''PD'')',
' ORDER BY QQSTIM DESC FETCH FIRST 100 ROWS ONLY ');
Figure 1. Most recent queries
For example, extract all the queries where the query took longer than one second:
CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS', 'APRIL2014');
CALL QSYS2.EXTRACT_STATEMENTS('SNAPSHOTS', 'APRIL2014',
ADDITIONAL_SELECT_COLUMNS => ‘DEC(QQI6)/1000000.0 as Total_time, QVC102 as Current_User_Profile ',
ADDITIONAL_PREDICATES => ' AND QQI6 > 1000000 ',
ORDER_BY => ' ORDER BY QQI6 DESC ');
Figure 2. Expensive queries
[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]
Was this topic helpful?
Document Information
Modified date:
26 March 2025
UID
ibm11171198