IBM Support

QSYS2.EXTRACT_STATEMENTS procedure

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 i Knowledge Center 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 APRIL1014:

  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

image-20200116133104-1

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

image-20200116133113-2

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
21 January 2020

UID

ibm11171198

Page Feedback