SQL Tables › SQL Status Tables › SQL_STATUS_CURRENT (SQC) › Process Types (PROC_TYPE) Description

Process Types (PROC_TYPE) Description

The processes that can generate a row are:

• Binding
• DDL execution
• Record-at-a-time constraint enforcement
• DML execution
• LUW summary

Binding

In the SQL_STATUS_CURRENT table, PROC_TYPE shows the following for bind processes:

BIND IN PROGRESS
BIND: GET HOST VARIABLE INFO (QHOST)
AUTO VIEW REBIND
CONSTRAINT REBIND IN PROGRESS

In the SQL_STATUS_CURRENT table, ROWS_OUT is zero for the preceding processes.

DDL Execution

Tasks executing DDL statements generate a single row. In the SQL_STATUS_CURRENT table, STMT_TYPE indicates the type of statement being executed.

Record-at-a-Time Constraint Enforcement

A task enforcing constraints for record-at-a-time ADDIT, UPDAT, or DELET commands generates a row with a PROC_TYPE (in the SQL_STATUS_CURRENT table) of ENFORCE CONSTRAINTS, while CMD_TYPE in the same table indicates which command is being constrained.

DML Execution (Query Level)

The following rows may appear for cursor open and fetch, and searched insert, update and delete statements. The processes are listed in order of execution. The PROC_TYPE column (in the SQL_STATUS_CURRENT table) indicates the kind of activity, while ROWS_OUT in the same table indicates the number of rows output by the process.

If the process has not taken place yet, ROWS_OUT is zero. For some processes, ROWS_OUT is set while the process is taking place, and in other cases ROWS_OUT is only set at the end of the process.

Some processes can be executed multiple times. EXEC_CNT indicates how many times the process has taken place, including processes in-flight at the time of your request. In these cases, ROWS_OUT is the total for all executions.

• Index Access

  ROWS_OUT for process type READ INDEX is the number of index entries read.

• Index Scanning

  Index scanning includes the following process types:

  INDEX MERGE

  • Rows from multiple indexes qualify
  • Temporary index built to eliminate duplicate rows found in multiple indexes

  INDEX INTERSECTION

  • Rows qualify only if found in multiple indexes
  • Only used if indexes restricted to single value

  INDEX RESTRICTION

  • Rows qualify only if low-order predicates are TRUE
  • Low-order predicates reference columns of scan index, but cannot restrict index scan range because they:
    • Reference other than the first column of index, or
    • Preceding columns are not restricted to single value

  If these processes have not or are not currently taking place, no row is produced.

  Although all of these processes can occur in the same query, only one row is generated where ROWS_OUT is the number of index entries that have been qualified by all processes. If PROC_TYPE is INDEX MERGE, index intersection and restriction may also have taken place. If PROC_TYPE is INDEX INTERSECTION, index restriction may also have taken place.

  You can determine the number of rows rejected by index scanning by subtracting ROWS_OUT from the index access ROWS_OUT.

  If a large percentage of index entries is rejected by this process, you can improve performance when there is a single less than or greater than predicate by rearranging the columns of the index, (or adding a new index), such that the referenced column is either the first column or preceded by columns restricted to a single value.

  Following is an example:

       SELECT *
       FROM T1
       WHERE COL1 >= :HOST1 AND COL2 = :HOST2
  
       Original Index: (COL1, COL2)
       Better Index  : (COL2, COL1)
  

• Read Data

  The number of rows read from the data area is ROWS_OUT for a PROC_TYPE of READ DATA. This is the same value for index scanning, if applicable, or index access, since no rows can be rejected by this process.

• Restrict Data

  This row is generated if there are predicates evaluated by Compound Boolean Selection that reference columns not in the scan index. The number of rows rejected by this process is the difference between ROWS_OUT for this and the previous process step.

  The following predicates are not evaluated by Compound Boolean Selection, so the number of rows rejected is not reflected in this process step:

  • LIKE
  • IN list
  • Operands are expressions or scalar functions (except concatenated contiguous NOT NULL character columns)
  • When there are multiple tables referenced under an OR
  • Correlated or quantified subquery

  If a large percentage of rows is rejected in this step, you can improve performance by including the referenced columns in the scan index. This eliminates the cost of accessing rows in the data area that do not qualify. However, consider the higher cost of the index, especially if the extra columns included are updated frequently.

• Sort for Merge

  If a sort is performed to join tables using the sort-merge technique, a row with the process type SORTED FOR MERGE or SORTING FOR MERGE is generated. ROWS_OUT is the number of rows sorted.

• Reading sort-merge temporary table

  If a table is sorted for a sort-merge join, a row with process step READ SORTED TEMP TABLE is generated. ROWS_OUT is the number of rows read. This value can be greater than the number of rows in the table for the inner table when there are multiple rows with the same join value in the outer table.

• Quantified Subquery Temporary Table

  A quantified subquery can have multiple rows in its result table. These rows are stored in a temporary table. A row with process step SUBQUERY TEMP TABLE is generated when the subquery is executed. EXEC_CNT can be greater than one if the subquery is correlated, in which case ROWS_OUT is the total rows for all executions of the subquery.

• Non-quantified Subquery

  A row is generated with the process step NON-QUANTIFIED SUBQUERY for non-quantified subqueries. Since a non-quantified subquery can have at most one row, ROWS_OUT is either zero or one.

  For correlated, non-quantified subqueries, EXEC_CNT indicates the number of times the subquery has been evaluated, and ROWS_OUT is the sum of all executions.

• Group By

  If a sort is required for GROUP BY, a row is generated with process type SORT FOR GROUP BY.

  If an index is used to process the GROUP BY, a row is generated with process type INDEXED GROUP BY.

  In either case, ROWS_OUT is the number of rows in the grouped table.

• HAVING

  If HAVING is specified, a row is generated with process type HAVING, where ROWS_OUT is the number of qualified grouped rows.

• DISTINCT, UNION or ORDER BY

  DISTINCT and UNION, and ORDER BY not using an index, cause a sort. A row is generated with process type SELECT DISTINCT, SORT SUBSELECT OF UNION, or ORDER BY is generated in these cases. ROWS_OUT indicates the number of rows in the sorted result temporary table.

  Although there are these three reasons for the sort, there is only one sort per subselect and only one row generated. When process type is ORDER BY, DISTINCT or UNION may have been specified, also. When process type is SORT SUBSELECT OF UNION, DISTINCT may also have been specified.

• UNION MERGE

  When there are multiple subselects, a row is generated with process type UNION MERGE. ROWS_OUT is the total number of rows output from the union of all subselects.

DML Execution (Statement Level)

ROWS_OUT for the following process types are for the final result at the statement level. Query-level columns are not significant.

• FETCH ROWS—rows returned
• UPDATE CURSOR—rows updated
• DELETE CURSOR—rows deleted
• INSERT ROWS—rows inserted
• DELETE ROWS—rows deleted
• UPDATE ROWS—rows updated

LUW Summary (Diagnostic Activity)

LUW summary rows have process steps 9900 and higher. Plan and statement level columns are not significant.

Writing SYSADM.SYSMSG and Statistics and Diagnostics Area (PXX) diagnostic information can cause a job to run slowly when triggered by:

• Plan option MSG (SYSMSG and Compound Boolean Selection Diagnostic Report to Statistics and Diagnostics Area (PXX))
• DBUTLTY COMM OPTION=ALTER,TRACE/DUMPS= PXX tracing requests
• PXX dumps for certain return codes

Diagnostic activity is shown in process steps and types:

• 9998—SYSMSG ROWS INSERTED/DELETED
• 9997—SQL AND CBS TRACE LINES

Note: ROWS_OUT for the CBS Diagnostic Report is an estimate.

PXX return code dumps are not included.

LUW Summary (Non-Diagnostic Activity)

The total requests to MUF for the LUW is indicated in process step 9999, process type REQUESTS TO SQL SUBSYSTEM in column ROWS_OUT.