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:

Table 1. 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.
PROGRAM_TYPE	PGMTYPE	VARCHAR(128)	Type of the object : PGM The object is a program. MODULE The object is a module. *SRVPGM The object is a service program.
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.
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: YES WITH HOLD clause specified. Contains the null value if the clause was not specified.
FETCH_ONLY	FETCHONLY	CHAR(3) Nullable	Specifies the FOR READ ONLY option for statement: YES FOR READ ONLY clause specified. Contains the null value if the clause was not specified.
CONCURRENTACCESSRESOLUTION	CONCURRENT	CHAR(1) Nullable	Specifies the concurrent access resolution for the statement: W Wait for outcome U Use currently committed S Skip locked data 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: RR Repeatable Read (RR) RS Read Stability (ALL) CS Cursor Stability (CS) UR Uncommitted Read (CHG) NC No Commit (*NONE) 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). ANY The statement is valid on any supported IBM i release. VxRxMx The statement is valid on IBM i VxRxMx release or later. Contains the null value if the earliest release is not known.
SQL_DB2_GROUP_LEVEL	SQL_LEVEL	INTEGER Nullable	The latest DB2 for i 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 for i PTF Group.
SERVICES_DB2_GROUP_LEVEL	SERV_LEVEL	INTEGER Nullable	The latest DB2 for i PTF Group level that a service referenced in this statement could be dependent on. This column contains a value if any function, table function, view, procedure, or global variable directly referenced in the statement has the same name as one provided by IBM as a service. If the SQL statement includes unqualified references to objects whose names match IBM provided services, this column considers the unqualified name as the IBM service. Built-in functions and built-in global variables that were added or had significant changes in a DB2 for i PTF Group are reflected in this column as well. Contains the null value if no built-in function, built-in global variable, or IBM-supplied service in this statement was identified as possibly being dependent on a DB2 for i PTF Group.