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.

Authorization: The caller must have:
  • *EXECUTE authority to the library containing the database monitor file, and
  • *OBJOPR, *READ, and *UPDATE authorities to the database monitor file.
If output-schema and output-table are used to create a table, the caller must have:
  • *OBJOPR, *READ, *EXECUTE, and *ADD authorities to output-schema, and
  • *USE to the Create Physical File (CRTPF) command
If output-schema and output-table are used to append to a table, the caller must have:
  • *USE authority to output-schema, and
  • *OBJOPR and *ADD authorities to output-table
Read syntax diagramSkip visual syntax diagramEXTRACT_STATEMENTS( MONITOR_SCHEMA =>  monitor-schema, MONITOR_NAME =>  monitor-name,ADDITIONAL_SELECT_COLUMNS => additional-select-columns,ADDITIONAL_PREDICATES => additional-predicates,ORDER_BY => order-by,OUTPUT_SCHEMA => output-schema,OUTPUT_TABLE => output-table)
The schema is QSYS2.
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 ');