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:
| 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
|
| 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:
|
CONCURRENTACCESSRESOLUTION![]() |
CONCURRENT![]() |
CHAR(1)![]() |
Specifies the concurrent access resolution:
![]() |
| 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. |
ORIGINAL_SOURCE_FILE_CCSID![]() |
SRC_CCSID![]() |
INTEGERNullable ![]() |
The CCSID of the source file that
was used to create the program or module.Contains null for SQL routines. ![]() |
| ROUTINE_TYPE | RTNTYPE | VARCHAR(9) Nullable |
Type of the routine.
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:
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.
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.
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. |
EXTENDED_INDICATOR![]() |
EXTIND![]() |
VARCHAR(9)![]() |
Indicates the EXTIND attribute:
![]() |
| C_NUL_REQUIRED | CNULRQD | VARCHAR(10) | Indicates the CNULRQD attribute:
|
| NAMING | NAMING | VARCHAR(4) | Indicates the NAMING attribute:
|
| 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.
|
| ALLOW_COPY_DATA | ALWCPYDTA | VARCHAR(9) | Indicates the ALWCPYDTA attribute:
|
| CLOSE_SQL_CURSOR | CLOSQLCSR | VARCHAR(10) | Indicates the CLOSQLCSR attribute:
|
| DATE_FORMAT | DATFMT | VARCHAR(4) | Indicates the DATFMT attribute:
|
| DATE_SEPARATOR | DATSEP | CHAR(1) | Indicates the date separator. |
| TIME_FORMAT | TIMFMT | VARCHAR(4) | Indicates the TIMFMT attribute:
|
| 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:
|
| CURRENT_RULES | SQLCURRULE | VARCHAR(4) | Indicates the SQLCURRULE attribute:
|
| ALLOW_BLOCK | ALWBLK | VARCHAR(8) | Indicates the ALWBLK attribute:
|
| DELAY_PREPARE | DLYPRP | VARCHAR(4) | Indicates the DLYPRP attribute:
|
| USER_PROFILE | USRPRF | VARCHAR(7) | Specifies the user profile used for
authority checking:
|
| DYNAMIC_USER_PROFILE | DYNUSRPRF | VARCHAR(6) | Specifies the user profile used for
dynamic SQL statements:
|
| SORT_SEQUENCE | SRTSEQ | VARCHAR(12)![]() |
Indicates whether the program, service
program, or module uses a collating sequence:
|
| LANGUAGE_IDENTIFIER | LANGID | CHAR(3) Nullable |
The language ID sort sequence. Contains null if the sort sequence is not *LANGIDSHR or *LANGIDUNQ. |
SORT_SEQUENCE_SCHEMA![]() |
SRTSEQSCH![]() |
CHAR(10)Nullable ![]() |
The sort sequence table system schema.
Contains null if the sort sequence is hex.![]() |
SORT_SEQUENCE_NAME![]() |
SRTSEQNAME![]() |
CHAR(10)Nullable ![]() |
The sort sequence table name. Contains
null if the sort sequence is hex.![]() |
| RDB_CONNECTION_METHOD | RDBCNNMTH | VARCHAR(4) | Specifies the semantics used for
CONNECT statements:
|
| DECRESULT_MAXIMUM_PRECISION | DECMAXPRC | SMALLINT | Specifies the maximum precision.
|
| 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:
|
| DECFLOAT_WARNING | DECFLTWRN | VARCHAR(3) | Indicates whether 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:
|
DBGKEY![]() |
DBGKEY![]() |
VARCHAR(3)![]() |
Specifies the type of source debug
information:
![]() |
| 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. |