SYSPROGRAMSTMTSTAT
The SYSPROGRAMSTMTSTAT view contains one row for each embedded SQL statement in a program, module, or service program.
The following table describes the columns in the SYSPROGRAMSTMTSTAT view:
Column Name | System Column Name | Data Type | Description |
---|---|---|---|
PROGRAM_SCHEMA | COLLID | VARCHAR(128) | Name of the schema. |
PROGRAM_NAME | NAME | VARCHAR(128) | Name of the program, service program, or module. For an SQL procedure, function, or trigger, this is the SQL object name. |
PROGRAM_TYPE | PGMTYPE | VARCHAR(128) | Type of the object :
|
MODULE_NAME | MODNAME | VARCHAR(10) Nullable
|
Module name for ILE program or service
program. Contains the null value if this is not an ILE program or service program. |
STATEMENT_NUMBER | STMTNBR | INTEGER | Statement number in program. |
NUMBER_TIMES_EXECUTED | NBREXEC | INTEGER | Number of times this statement has been executed. This value is not maintained for CALL, SET, or VALUES INTO statements. |
ROWS_AFFECTED | ROWCNT | INTEGER | Total rows fetched, updated, inserted, or deleted for all executions of the statement. Contains 0 if the statement is not a FETCH, SET, VALUES INTO, UPDATE, INSERT, DELETE, or MERGE. Will contain 0 for a SET or VALUES INTO statement that is not implemented using a cursor. |
NUMBER_HOST_VARIABLES | NBRHV | INTEGER | Total number of host variables specified in the statement. This includes input and output host variables. |
NUMBER_INPUT_HOST_VARIABLES | NBRIHV | INTEGER | Number of input host variables specified in the statement. |
WITH_HOLD | WITHHOLD | CHAR(3) Nullable
|
Specifies the WITH HOLD option
for statement:
Contains the null value if the clause was not specified. |
FETCH_ONLY | FETCHONLY | CHAR(3) Nullable
|
Specifies the FOR READ ONLY option
for statement:
Contains the null value if the clause was not specified. |
CONCURRENTACCESSRESOLUTION | CONCURRENT | CHAR(1) Nullable
|
Specifies the concurrent access
resolution for the statement:
Contains the null value if concurrent access was not specified at the statement level. |
NUMBER_REBUILDS | NBRREBLD | INTEGER | Number of times QDT or access plan has been rebuilt. |
ISOLATION | ISOLATION | CHAR(2) Nullable
|
Isolation option specification:
Contains the null value if isolation level was not specified at the statement level. |
NUMBER_ROWS_TO_OPTIMIZE | OPTROWS | INTEGER Nullable
|
Number of rows specified on the
OPTIMIZE FOR n ROWS clause. -1 means that the value *ALL was
specified. Contains the null value if the clause was not specified. |
NUMBER_ROWS_TO_FETCH | FETCHROWS | INTEGER Nullable
|
Number of rows specified on the
FETCH FIRST n ROWS clause. Contains the null value if the clause was not specified. |
LAST_QDT_REBUILD_REASON | QDTRBLD | CHAR(2) Nullable
|
Reason code for last QDT rebuild.
This corresponds to column QVC22 in the STRDBMON outfile when QQRID
= 1000. Contains the null value if the statement does not use a QDT or has never had a QDT rebuilt. |
STATEMENT_TEXT | STMTTEXT | DBCLOB(2M)
CCSID(1200) |
Text of the SQL statement. |
SYSTEM_PROGRAM_NAME | SYS_NAME | CHAR(10) | System name of the program. |
SYSTEM_PROGRAM_SCHEMA | SYS_DNAME | CHAR(10) | System name of the schema containing the program. |
ACCESS_PLAN_LENGTH | AP_LENGTH | INTEGER | Number of bytes that are used for the QDT and access plan for the statement. |
EARLIEST_POSSIBLE_RELEASE | MINRLS | VARCHAR(6) | The earliest IBM® i release that supports this SQL statement (VxRxMx).
Contains the null value if the earliest release is not known. |
SQL_DB2_GROUP_LEVEL | SQL_LEVEL | INTEGER Nullable
|
The latest Db2® PTF Group level that SQL language syntax used in this statement is dependent on. Contains the null value if no SQL syntax in this statement was identified as being dependent on a Db2 PTF Group. |
SERVICES_DB2_GROUP_LEVEL | SERV_LEVEL | INTEGER Nullable
|
The latest Db2 PTF Group level that a service referenced in this statement might be dependent on. This column contains a value if the SQL statement references an IBM i provided service or Db2 for i built-in function or built-in global variable. If the SQL statement includes unqualified references to objects whose names match IBM i provided services, this column considers the unqualified name as the IBM i service. Contains the null value if no built-in function, built-in global variable, or IBM i provided service in this statement was identified as being dependent on a Db2 PTF Group. |