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, 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:
Contains the null value if the program is an external routine without SQL statements. |
CONCURRENTACCESSRESOLUTION | CONCURRENT | CHAR(1) Nullable
|
Specifies the concurrent access resolution:
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.
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:
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.
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.
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:
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:
Contains the null value if the program is an external routine without SQL statements. |
NAMING | NAMING | VARCHAR(4) Nullable
|
Indicates the NAMING attribute:
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).
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.
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:
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:
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:
|
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 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:
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:
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:
Contains the null value if the program is an external routine without SQL statements. |
ALLOW_BLOCK | ALWBLK | VARCHAR(8) Nullable
|
Indicates the ALWBLK attribute:
Contains the null value if the program is an external routine without SQL statements. |
DELAY_PREPARE | DLYPRP | VARCHAR(4) Nullable
|
Indicates the DLYPRP attribute:
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:
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:
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:
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:
Contains the null value if the program is an external routine without SQL statements. |
DECRESULT_MAXIMUM_PRECISION | DECMAXPRC | SMALLINT Nullable
|
Specifies the maximum precision.
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:
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.
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:
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:
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.
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. |