SYSPROGRAMSTAT

The SYSPROGRAMSTAT view contains one row for each program, service program, and module that contains SQL statements.

The following table describes the columns in the SYSPROGRAMSTAT view:

Table 1. SYSPROGRAMSTAT 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 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, or if the program or service program is not a procedure or function.
PROGRAM_OWNER	OWNER	VARCHAR(128)	Owner of the program, service program, or module
PROGRAM_CREATOR	CREATOR	VARCHAR(128)	Creator of the program, service program, or module
CREATION_TIMESTAMP	TIMESTAMP	TIMESTAMP	Timestamp of when the program, service program, or module was created
DEFAULT_SCHEMA	QUALIFIER	VARCHAR(128) Nullable	Implicit name for unqualified tables, views, and indexes. Contains the null value if a default schema was not specified (DFTRDBCOL) or if the program is an external routine without SQL statements.
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 the program is an external routine without SQL statements.
CONCURRENTACCESSRESOLUTION	CONCURRENT	CHAR(1) Nullable	Specifies the concurrent access resolution: blank Not specified W Wait for outcome U Use currently committed Contains the null value if the program is an external routine without SQL statements.
NUMBER_STATEMENTS	NBRSTMTS	INTEGER	Number of SQL statements in the program, service program or module.
PROGRAM_USED_SIZE	PGMSIZE	INTEGER	Number of bytes that are used for SQL statements and access plans in the program, service program or module.
NUMBER_COMPRESSIONS	PGM_CMP Nullable	INTEGER Nullable	Number of times the program or service program has been compressed. Contains the null value for modules or if the program is an external routine without SQL statements.
STATEMENT_CONTENTION_COUNT	CONTENTION	BIGINT Nullable	Number of times contention occurred when attempting to store a new access plan. Contains the null value for modules or if the program is an external routine without SQL statements.
ORIGINAL_SOURCE_FILE	SOURCE	VARCHAR(128) Nullable	The fully qualified source file and member that was used to create the program or module. Contains the null value for SQL routines or if the program is an external routine without SQL statements.
ORIGINAL_SOURCE_FILE_CCSID	SRC_CCSID	INTEGER Nullable	The CCSID of the source file that was used to create the program or module. Contains the null value for SQL routines or if the program is an external routine without SQL statements.
ROUTINE_TYPE	RTNTYPE	VARCHAR(9) Nullable	Type of the routine. PROCEDURE This is a procedure. FUNCTION This is a function. TRIGGER This is a trigger. Contains the null value for modules or if the program or service program is not a procedure, function, or trigger. An external procedure will not be identified as PROCEDURE unless NUMBER_EXTERNAL_ROUTINES is greater than zero.
ROUTINE_BODY	BODY	VARCHAR(8) Nullable	The type of the routine body: EXTERNAL This is an external routine. SQL This is an SQL routine. Contains the null value for modules or if the program or service program is not a procedure or function.
FUNCTION_ORIGIN	ORIGIN	CHAR(1) Nullable	Identifies the type of function. If this is a procedure, this column contains a blank. B This is a built-in function (defined by Db2® for i). E This is a user-defined function. U This is a user-defined function that is sourced on another function. S This is a system-generated function. Contains the null value for modules or if the program or service program is not a procedure or function.
FUNCTION_TYPE	TYPE	CHAR(1) Nullable	Identifies the form of the function. If this is a procedure, this column contains a blank. S This is a scalar function. C This is a column function. T This is a table function. Contains the null value for modules or if the program or service program is not a procedure, function, or trigger.
NUMBER_EXTERNAL_ROUTINES	NBREXTRTN	SMALLINT Nullable	Indicates the number of procedure and function definitions stored in the program or service program. Contains the null value for modules, triggers, or SQL routines.
EXTENDED_INDICATOR	EXTIND	VARCHAR(9) Nullable	Indicates the EXTIND attribute: EXTIND Extended indicator support is enabled. NOEXTIND Extended indicator support is not enabled. Contains the null value if the program is an external routine without SQL statements.
C_NUL_REQUIRED	CNULRQD	VARCHAR(10) Nullable	Indicates the CNULRQD attribute: CNULRQD C nuls are required. NOCNULRQD C nuls are not required. Contains the null value if the program is an external routine without SQL statements.
NAMING	NAMING	VARCHAR(4) Nullable	Indicates the NAMING attribute: SYS This is system naming. SQL This is SQL naming. Contains the null value if the program is an external routine without SQL statements.
TARGET_RELEASE	TGTRLS	VARCHAR(6) Nullable	Indicates the target release of the program, service program, or module (VxRxMx). Contains the null value if the program is an external routine without SQL statements.
EARLIEST_POSSIBLE_RELEASE	MINRLS	VARCHAR(6) Nullable	Indicates the earliest IBM i release that supports all the SQL statements in the program, service program, or module (VxRxMx). *ANY The statements are 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 has not yet been determined or if the program is an external routine without SQL statements.
RDB	RDB	VARCHAR(18) Nullable	Indicates the RDB specified for the program, service program, or module. rdb-name The name of the relational database. LOCAL The database on the local system. NONE A relational database was not specified. Contains the null value if the program is an external routine without SQL statements.
CONSISTENCY_TOKEN	CONTOKEN	VARBINARY(8) Nullable	Indicates the consistency token of the program. Contains the null value if the program is an external routine without SQL statements.
ALLOW_COPY_DATA	ALWCPYDTA	VARCHAR(9) Nullable	Indicates the ALWCPYDTA attribute: NO A copy of the data is not allowed. OPTIMIZE A copy of the data is allowed whenever it might result in better performance. *YES A copy of the data is allowed, but only when necessary. Contains the null value if the program is an external routine without SQL statements.
CLOSE_SQL_CURSOR	CLOSQLCSR	VARCHAR(10) Nullable	Indicates the CLOSQLCSR attribute: ENDACTGRP SQL cursors are closed and SQL prepared statements are implicitly discarded, and LOCK TABLE locks are released when the activation group ends. ENDJOB SQL cursors are closed and SQL prepared statements are implicitly discarded, and LOCK TABLE locks are released when the job ends. ENDMOD SQL cursors are closed and SQL prepared statements are implicitly discarded when the module is exited. LOCK TABLE locks are released when the first SQL program on the call stack ends. ENDPGM SQL cursors are closed and SQL prepared statements are implicitly discarded when the program ends. LOCK TABLE locks are released when the first SQL program on the call stack ends. Contains the null value if the program is an external routine without SQL statements.
LOB_FETCH_OPTIMIZATION	OPTLOB	VARCHAR(9)	Indicates the LOB optimization attribute: OPTLOB The first FETCH for a cursor determines how the cursor will be used for LOB and XML result columns on all subsequent FETCHes. NOOPTLOB Any FETCH may retrieve a LOB or XML result column into either a locator or variable.
DECIMAL_POINT	DECPNT	VARCHAR(7)	Indicates the decimal point for numeric constants used in SQL statements. PERIOD The decimal point is a period. COMMA The decimal point is a comma.
SQL_STRING_DELIMITER	STRDLM	VARCHAR(9)	Indicates the character used as the string delimiter in the SQL statements. APOSTSQL The string delimiter is an apostrophe ('). QUOTESQL The string delimiter is a quote (").
DATE_FORMAT	DATFMT	VARCHAR(4) Nullable	Indicates the DATFMT attribute: JOB The date format specified in the job at runtime is used. USA The date format is USA. ISO The date format is ISO. EUR The date format is EUR. JIS The date format is JIS. MDY The date format is MDY. DMY The date format is DMY. YMD The date format is YMD. JUL The date format is *JUL. Contains the null value if the program is an external routine without SQL statements.
DATE_SEPARATOR	DATSEP	CHAR(1) Nullable	Indicates the date separator. Contains the null value if the program is an external routine without SQL statements.
TIME_FORMAT	TIMFMT	VARCHAR(4) Nullable	Indicates the TIMFMT attribute: JOB The time format specified in the job at runtime is used. USA The time format is USA. ISO The time format is ISO. EUR The time format is EUR. JIS The time format is JIS. HMS The date format is *HMS. Contains the null value if the program is an external routine without SQL statements.
TIME_SEPARATOR	TIMSEP	CHAR(1) Nullable	Indicates the time separator. Contains the null value if the program is an external routine without SQL statements.
DYNAMIC_DEFAULT_SCHEMA	DYNDFTCOL	VARCHAR(4) Nullable	Indicates whether the value for DFTRDBCOL should be used for implicit qualification on dynamic SQL statements: NO The schema specified in DFTDRBCOL is not used for dynamic SQL statements. YES The schema specified in DFTDRBCOL is used for dynamic SQL statements. Contains the null value if a default schema was not specified (DFTRDBCOL) or if the program is an external routine without SQL statements.
CURRENT_RULES	SQLCURRULE	VARCHAR(4) Nullable	Indicates the SQLCURRULE attribute: DB2 The semantics of all SQL statements will default to the rules established for DB2®. STD The semantics of all SQL statements will default to the rules established by the ISO and ANSI SQL standards. Contains the null value if the program is an external routine without SQL statements.
ALLOW_BLOCK	ALWBLK	VARCHAR(8) Nullable	Indicates the ALWBLK attribute: ALLREAD Rows are blocked for read-only cursors. NONE Rows are not blocked for retrieval of data for cursors. READ Records are blocked for read-only retrieval of data for cursors when: NONE is specified for the Commitment control (COMMIT) parameter. The cursor is declared with a FOR READ ONLY clause or there are no dynamic statements that could run a positioned UPDATE or DELETE statement for the cursor. Contains the null value if the program is an external routine without SQL statements.
DELAY_PREPARE	DLYPRP	VARCHAR(4) Nullable	Indicates the DLYPRP attribute: NO Dynamic statement validation is performed when the dynamic statements are prepared. YES Dynamic statement validation is delayed until the dynamic statements are used. Contains the null value if the program is an external routine without SQL statements.
USER_PROFILE	USRPRF	VARCHAR(7) Nullable	Specifies the user profile used for authority checking: USER The profile of the user running the program is used. OWNER The profiles of both the owner of the program and the user running the program is used. NAMING If the naming convention is SQL, OWNER is used. If the naming convention is SYS, *USER is used. Contains the null value if the program is an external routine without SQL statements.
DYNAMIC_USER_PROFILE	DYNUSRPRF	VARCHAR(6) Nullable	Specifies the user profile used for dynamic SQL statements: USER Local dynamic SQL statements are run under the profile of the job or thread. Distributed dynamic SQL statements are run under the profile of the application server job. OWNER Local dynamic SQL statements are run under the profile of the program's owner. Distributed dynamic SQL statements are run under the profile of the SQL package's owner. Contains the null value if the program is an external routine without SQL statements.
SORT_SEQUENCE	SRTSEQ	VARCHAR(12) Nullable	Indicates whether the program, service program, or module uses a collating sequence: BY HEX VALUE The SQL index does not use a collating table. LANGIDSHR The SQL index uses a shared weight sort sequence (SRTSEQ). LANGIDUNQ The SQL index uses a unique weight sort sequence (SRTSEQ). ALTSEQ The SQL index uses an alternate collating sequence (ALTSEQ). Contains the null value if the program is an external routine without SQL statements.
LANGUAGE_IDENTIFIER	LANGID	CHAR(3) Nullable	The language ID sort sequence. Contains the null value if the sort sequence is not LANGIDSHR or LANGIDUNQ or if the program is an external routine without SQL statements.
SORT_SEQUENCE_SCHEMA	SRTSEQSCH	CHAR(10) Nullable	The sort sequence table system schema. Contains the null value if the sort sequence is hex or if the program is an external routine without SQL statements.
SORT_SEQUENCE_NAME	SRTSEQNAME	CHAR(10) Nullable	The sort sequence table name. Contains the null value if the sort sequence is hex or if the program is an external routine without SQL statements.
RDB_CONNECTION_METHOD	RDBCNNMTH	VARCHAR(4) Nullable	Specifies the semantics used for CONNECT statements: RUW CONNECT (Type 1) semantics are used to support remote unit of work. DUW CONNECT (Type 2) semantics are used to support distributed unit of work. Contains the null value if the program is an external routine without SQL statements.
DECRESULT_MAXIMUM_PRECISION	DECMAXPRC	SMALLINT Nullable	Specifies the maximum precision. 31 The maximum precision is 31. 63 The maximum precision is 63. Contains the null value if the program is an external routine without SQL statements.
DECRESULT_MAXIMUM_SCALE	DECMAXSCL	SMALLINT Nullable	The maximum scale (number of decimal positions to the right of the decimal point) that should be returned for result data types. Contains the null value if the program is an external routine without SQL statements.
DECRESULT_MINIMUM_DIVIDE_SCALE	DECMINDIV	SMALLINT Nullable	The minimum divide scale (number of decimal positions to the right of the decimal point) that should be returned for both intermediate and result data types. Contains the null value if the program is an external routine without SQL statements.
DECFLOAT_ROUNDING_MODE	DECFLTRND	VARCHAR(8) Nullable	Indicates the DECFLOAT rounding mode: CEILING ROUND_CEILING DOWN ROUND_DOWN FLOOR ROUND_FLOOR HALFDOWN ROUND_HALF_DOWN HALFEVEN ROUND_HALF_EVEN HALFUP ROUND_HALF_UP UP ROUND_UP Contains the null value if the program is an external routine without SQL statements.
DECFLOAT_WARNING	DECFLTWRN	VARCHAR(3) Nullable	Indicates whether DECFLOAT warnings are returned. NO DECFLOAT warnings are not returned. YES DECFLOAT warnings are returned. Contains the null value if the program is an external routine without SQL statements.
SQLPATH	SQLPATH	VARCHAR(3483) Nullable	Identifies the SQL path. Contains the null value if an SQL path is not specified or if the program is an external routine without SQL statements.
DBGVIEW	DBGVIEW	VARCHAR(9) Nullable	Specifies the type of source debug information: NONE No debug. SOURCE Debug view includes source and SQL INCLUDE statements. STMT Debug view includes precompiler generated statements. LIST Debug view includes the compiled listing. *LSTDBG Debug view includes the compiled listing of an OPM program. ALLOW Source debug allowed by the Unified Debugger. DISALLOW Source debug not allowed by the Unified Debugger. DISABLE Source debug not allowed by the Unified Debugger and the DEBUG MODE cannot be altered. Contains the null value if the program is an external routine without SQL statements.
DBGKEY	DBGKEY	VARCHAR(3) Nullable	Specifies the type of source debug information: NO No encryption key was specified on the debug encryption key (DBGENCKEY) parameter. YES A key was specified on the debug encryption key (DBGENCKEY) parameter. Contains the null value if DBGENCKEY is not supported or if the program is an external routine without SQL statements.
LAST_USED_TIMESTAMP	LASTUSED	TIMESTAMP Nullable	The timestamp of the last time the program, service program, or module was used. Contains the null value if the program, service program, or module has never been used.
DAYS_USED_COUNT	DAYSUSED	INTEGER	The number of days the program, service program, or module was used since the last time the usage statistics were reset. If the program, service program, or module has never been used since the last time the usage statistics were reset, contains 0.
LAST_RESET_TIMESTAMP	LASTRESET	TIMESTAMP Nullable	The timestamp of the last time the usage statistics were reset. Contains the null value if the statistics have never been reset.
SYSTEM_PROGRAM_NAME	SYS_NAME	CHAR(10)	System name of the program, service program, or module.
SYSTEM_PROGRAM_SCHEMA	SYS_DNAME	CHAR(10)	System name of the schema containing the program, service program, or module.
IASP_NUMBER	IASPNUMBER	INTEGER	Specifies the independent auxiliary storage pool (IASP) number.
SYSTEM_TIME_SENSITIVE	SYSTIME	VARCHAR(3) Nullable	Specifies whether the CURRENT TEMPORAL SYSTEM_TIME special register affects static and dynamic SQL statements in the program. YES Any references to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. NO Any references to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. Contains the null value, which is treated like NO, if the program was compiled prior to 7.3, or if the program is an external routine without SQL statements.