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
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.
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 null if a default schema was not specified (DFTRDBCOL).
ISOLATION ISOLATION CHAR(2) Isolation option specification:
RR
Repeatable Read (*RR)
RS
Read Stability (*ALL)
CS
Cursor Stability (*CS)
UR
Uncommitted Read (*CHG)
NC
No Commit (*NONE)
Start of changeCONCURRENTACCESSRESOLUTIONEnd of change Start of changeCONCURRENTEnd of change Start of changeCHAR(1)End of change Start of changeSpecifies the concurrent access resolution:
blank
Not specified
W
Wait for outcome
U
Use currently committed
End of change
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 INTEGER

Nullable

Number of times the program or service program has been compressed.

Contains null for modules.

STATEMENT_CONTENTION_COUNT CONTENTION BIGINT

Nullable

Number of times contention occurred when attempting to store a new access plan.

Contains null for modules.

ORIGINAL_SOURCE_FILE SOURCE VARCHAR(128)

Nullable

The fully qualified source file and member that was used to create the program or module.

Contains null for SQL routines.

Start of changeORIGINAL_SOURCE_FILE_CCSIDEnd of change Start of changeSRC_CCSIDEnd of change Start of changeINTEGER

Nullable

End of change
Start of changeThe CCSID of the source file that was used to create the program or module.

Contains null for SQL routines.

End of change
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 null 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 null 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 null 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 null 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 null for modules, triggers, or SQL routines.

Start of changeEXTENDED_INDICATOREnd of change Start of changeEXTINDEnd of change Start of changeVARCHAR(9)End of change Start of changeIndicates the EXTIND attribute:
*EXTIND
Extended indicator support is enabled.
*NOEXTIND
Extended indicator support is not enabled.
End of change
C_NUL_REQUIRED CNULRQD VARCHAR(10) Indicates the CNULRQD attribute:
*CNULRQD
C nuls are required.
*NOCNULRQD
C nuls are not required.
NAMING NAMING VARCHAR(4) Indicates the NAMING attribute:
*SYS
This is system naming.
*SQL
This is SQL naming.
TARGET_RELEASE TGTRLS VARCHAR(6) Indicates the target release of the program, service program, or module (VxRxMx).
RDB RDB VARCHAR(18) Indicates the RDB specified for the program, service program, or module.
rdb-name
The name of the relational database.
*NONE
A relational database was not specified.
ALLOW_COPY_DATA ALWCPYDTA VARCHAR(9) 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.
CLOSE_SQL_CURSOR CLOSQLCSR VARCHAR(10) 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.
DATE_FORMAT DATFMT VARCHAR(4) 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.
DATE_SEPARATOR DATSEP CHAR(1) Indicates the date separator.
TIME_FORMAT TIMFMT VARCHAR(4) 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.
TIME_SEPARATOR TIMSEP CHAR(1) Indicates the time separator.
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 null if a default schema was not specified (DFTRDBCOL).
CURRENT_RULES SQLCURRULE VARCHAR(4) 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.
ALLOW_BLOCK ALWBLK VARCHAR(8) 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.
DELAY_PREPARE DLYPRP VARCHAR(4) 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.
USER_PROFILE USRPRF VARCHAR(7) 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.
DYNAMIC_USER_PROFILE DYNUSRPRF VARCHAR(6) 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.
SORT_SEQUENCE SRTSEQ Start of changeVARCHAR(12)End of change 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).
Start of changeALTSEQEnd of change
The SQL index uses an alternate collating sequence (ALTSEQ).
LANGUAGE_IDENTIFIER LANGID CHAR(3)

Nullable

The language ID sort sequence.

Contains null if the sort sequence is not *LANGIDSHR or *LANGIDUNQ.

Start of changeSORT_SEQUENCE_SCHEMAEnd of change Start of changeSRTSEQSCHEnd of change Start of changeCHAR(10)

Nullable

End of change
Start of changeThe sort sequence table system schema. Contains null if the sort sequence is hex.End of change
Start of changeSORT_SEQUENCE_NAMEEnd of change Start of changeSRTSEQNAMEEnd of change Start of changeCHAR(10)

Nullable

End of change
Start of changeThe sort sequence table name. Contains null if the sort sequence is hex.End of change
RDB_CONNECTION_METHOD RDBCNNMTH VARCHAR(4) 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.
DECRESULT_MAXIMUM_PRECISION DECMAXPRC SMALLINT Specifies the maximum precision.
31
The maximum precision is 31.
63
The maximum precision is 63.
DECRESULT_MAXIMUM_SCALE DECMAXSCL SMALLINT The maximum scale (number of decimal positions to the right of the decimal point) that should be returned for result data types.
DECRESULT_MINIMUM_DIVIDE_SCALE DECMINDIV SMALLINT 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.
DECFLOAT_ROUNDING_MODE DECFLTRND VARCHAR(8) 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
DECFLOAT_WARNING DECFLTWRN VARCHAR(3) Indicates whether DECFLOAT warnings are returned.
NO
DECFLOAT warnings are not returned.
YES
DECFLOAT warnings are returned.
SQLPATH SQLPATH VARCHAR(3483)

Nullable

Identifies the SQL path.

Contains the null value if an SQL path is not specified.

DBGVIEW DBGVIEW VARCHAR(9) 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.
Start of change*LSTDBGEnd of change
Start of changeDebug view includes the compiled listing of an OPM program.End of change
Start of changeALLOWEnd of change
Source debug allowed by the Unified Debugger.
Start of changeDISALLOWEnd of change
Source debug not allowed by the Unified Debugger.
Start of changeDISABLEEnd of change
Source debug not allowed by the Unified Debugger and the DEBUG MODE cannot be altered.
Start of changeDBGKEYEnd of change Start of changeDBGKEYEnd of change Start of changeVARCHAR(3)End of change Start of changeSpecifies 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.
A null value is returned when DBGENCKEY is not supported.End of change
LAST_USED_TIMESTAMP LASTUSED TIMESTAMP

Nullable

The timestamp of the last time the program, service program, or module was used. If the program, service program, or module has never been used, contains null.
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. If the statistics have never been reset, contains null.
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.