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:
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. |
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) or if the program is an external procedure without SQL statements. |
ISOLATION | ISOLATION | CHAR(2) Nullable
|
Isolation option specification:
Contains null if the program is an external procedure without SQL statements. |
CONCURRENTACCESSRESOLUTION | CONCURRENT | CHAR(1) Nullable
|
Specifies the concurrent access resolution:
Contains null if the program is an external procedure 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 null for modules or if the program is an external procedure without SQL statements. |
STATEMENT_CONTENTION_COUNT | CONTENTION | BIGINT Nullable
|
Number of times contention occurred
when attempting to store a new access plan. Contains null for modules or if the program is an external procedure 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 null for SQL routines or if the program is an external procedure 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 null for SQL routines or if the program is an external procedure without SQL statements. |
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) Nullable
|
Indicates the EXTIND attribute:
Contains null if the program is an external procedure without SQL statements. |
C_NUL_REQUIRED | CNULRQD | VARCHAR(10) Nullable
|
Indicates the CNULRQD attribute:
Contains null if the program is an external procedure without SQL statements. |
NAMING | NAMING | VARCHAR(4) Nullable
|
Indicates the NAMING attribute:
Contains null if the program is an external procedure without SQL statements. |
TARGET_RELEASE | TGTRLS | VARCHAR(6) Nullable
|
Indicates the target release of the
program, service program, or module (VxRxMx). Contains null if the program is an external procedure 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).
Contains null if the earliest release has not yet been determined or if the program is an external procedure without SQL statements. |
RDB | RDB | VARCHAR(18) Nullable
|
Indicates the RDB specified for the
program, service program, or module.
Contains null if the program is an external procedure without SQL statements. |
CONSISTENCY_TOKEN | CONTOKEN | VARBINARY(8) Nullable
|
Indicates the consistency token of
the program. Contains null if the program is an external procedure without SQL statements. |
ALLOW_COPY_DATA | ALWCPYDTA | VARCHAR(9) Nullable
|
Indicates the ALWCPYDTA attribute:
Contains null if the program is an external procedure without SQL statements. |
CLOSE_SQL_CURSOR | CLOSQLCSR | VARCHAR(10) Nullable
|
Indicates the CLOSQLCSR attribute:
Contains null if the program is an external procedure without SQL statements. |
LOB_FETCH_OPTIMIZATION | OPTLOB | VARCHAR(9) | Indicates the LOB optimization attribute:
|
DECIMAL_POINT | DECPNT | VARCHAR(7) | Indicates the decimal point for numeric
constants used in SQL statements.
|
SQL_STRING_DELIMITER | STRDLM | VARCHAR(9) | Indicates the character used as the
string delimiter in the SQL statements.
|
DATE_FORMAT | DATFMT | VARCHAR(4) Nullable
|
Indicates the DATFMT attribute:
Contains null if the program is an external procedure without SQL statements. |
DATE_SEPARATOR | DATSEP | CHAR(1) Nullable
|
Indicates the date separator. Contains null if the program is an external procedure without SQL statements. |
TIME_FORMAT | TIMFMT | VARCHAR(4) Nullable
|
Indicates the TIMFMT attribute:
Contains null if the program is an external procedure without SQL statements. |
TIME_SEPARATOR | TIMSEP | CHAR(1) Nullable
|
Indicates the time separator. Contains null if the program is an external procedure 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:
Contains null if a default schema was not specified (DFTRDBCOL) or if the program is an external procedure without SQL statements. |
CURRENT_RULES | SQLCURRULE | VARCHAR(4) Nullable
|
Indicates the SQLCURRULE attribute:
Contains null if the program is an external procedure without SQL statements. |
ALLOW_BLOCK | ALWBLK | VARCHAR(8) Nullable
|
Indicates the ALWBLK attribute:
Contains null if the program is an external procedure without SQL statements. |
DELAY_PREPARE | DLYPRP | VARCHAR(4) Nullable
|
Indicates the DLYPRP attribute:
Contains null if the program is an external procedure without SQL statements. |
USER_PROFILE | USRPRF | VARCHAR(7) Nullable
|
Specifies the user profile used for
authority checking:
Contains null if the program is an external procedure without SQL statements. |
DYNAMIC_USER_PROFILE | DYNUSRPRF | VARCHAR(6) Nullable
|
Specifies the user profile used for
dynamic SQL statements:
Contains null if the program is an external procedure without SQL statements. |
SORT_SEQUENCE | SRTSEQ | VARCHAR(12) Nullable
|
Indicates whether the program, service
program, or module uses a collating sequence:
Contains null if the program is an external procedure without SQL statements. |
LANGUAGE_IDENTIFIER | LANGID | CHAR(3) Nullable
|
The language ID sort sequence. Contains null if the sort sequence is not *LANGIDSHR or *LANGIDUNQ or if the program is an external procedure without SQL statements. |
SORT_SEQUENCE_SCHEMA | SRTSEQSCH | CHAR(10) Nullable
|
The sort sequence table system schema. Contains null if the sort sequence is hex or if the program is an external procedure without SQL statements. |
SORT_SEQUENCE_NAME | SRTSEQNAME | CHAR(10) Nullable
|
The sort sequence table name. Contains null if the sort sequence is hex or if the program is an external procedure without SQL statements. |
RDB_CONNECTION_METHOD | RDBCNNMTH | VARCHAR(4) Nullable
|
Specifies the semantics used for
CONNECT statements:
Contains null if the program is an external procedure without SQL statements. |
DECRESULT_MAXIMUM_PRECISION | DECMAXPRC | SMALLINT Nullable
|
Specifies the maximum precision.
Contains null if the program is an external procedure 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 null if the program is an external procedure 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 null if the program is an external procedure without SQL statements. |
DECFLOAT_ROUNDING_MODE | DECFLTRND | VARCHAR(8) Nullable
|
Indicates the DECFLOAT rounding mode:
Contains null if the program is an external procedure without SQL statements. |
DECFLOAT_WARNING | DECFLTWRN | VARCHAR(3) Nullable
|
Indicates whether DECFLOAT warnings
are returned.
Contains null if the program is an external procedure 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 procedure without SQL statements. |
DBGVIEW | DBGVIEW | VARCHAR(9) Nullable
|
Specifies the type of source debug
information:
Contains null if the program is an external procedure without SQL statements. |
DBGKEY | DBGKEY | VARCHAR(3) Nullable
|
Specifies the type of source debug
information:
Contains null if DBGENCKEY is not supported or if the program is an external procedure without SQL statements. |
LAST_USED_TIMESTAMP | LASTUSED | TIMESTAMP Nullable
|
The timestamp of the last time the
program, service program, or module was used. Contains null 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 null 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. |