EXTRACT_STATEMENTS procedure
The EXTRACT_STATEMENTS procedure returns details from an SQL plan cache snapshot, an SQL performance monitor, or an SQL plan cache event monitor in the form of an SQL table or a result set.
- *EXECUTE authority to the library containing the database monitor file, and
- *OBJOPR, *READ, and *UPDATE authorities to the database monitor file.
- *OBJOPR, *READ, *EXECUTE, and *ADD authorities to output-schema, and
- *USE to the Create Physical File (CRTPF) command
- *USE authority to output-schema, and
- *OBJOPR and *ADD authorities to output-table
- monitor-schema
- A character or graphic string expression that identifies the name of the library containing monitor-name.
- monitor-name
- A character or graphic string expression that identifies the name of an existing database monitor file.
- additional-select-columns
- A character or graphic string of up to 5000 characters containing additional columns or
expressions to be appended to the generated SELECT clause. A value of *AUDIT will cause the
procedure to return the merged statement and columns that are normally interesting for
auditing.
If additional-select-columns is not specified or is the null value, no additions are made to the generated SELECT clause.
- additional-predicates
- A character or graphic string of up to 5000 characters containing additional predicates to be
appended to the generated WHERE clause.
If additional-predicates is not specified or is the null value, no additions are made to the generated WHERE clause.
- order-by
- A character or graphic string of up to 5000 characters containing additional options to be
appended to the end of the generated query. This can include the ORDER BY clause or other clauses
such as FETCH FIRST n ROWS.
If order-by is not specified or is the null value, no additions are made to the end of the query.
- output-schema
- The schema name for the output table.
If output-schema is not specified, the null value is used.
- output-table
- The table name to contain the output. If the table identified by output-schema and
output-table does not exist, it will be created. If the table exists, the result of this
calling this procedure will be appended to the table. For an existing table, the number of selected
columns must match the selected columns when the table was generated.
If output-name is not specified, the null value is used.
If output-schema or output-table is the null value, a result set containing the extracted statement information is returned.
Examples
- 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 ');
- 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 => 'DECIMAL(QQI6)/1000000.0 as Total_time, QVC102 as Current_User_Profile ', ADDITIONAL_PREDICATES => ' AND QQI6 > 1000000 ', ORDER_BY => ' ORDER BY QQI6 DESC ');