| 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.
|
| 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:
- RR
- Repeatable Read (*RR)
- RS
- Read Stability (*ALL)
- CS
- Cursor Stability (*CS)
- UR
- Uncommitted Read (*CHG)
- NC
- No Commit (*NONE)
Contains null if the program is an external
procedure 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 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_CMPNullable |
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.
- 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.
|
| EXTENDED_INDICATOR |
EXTIND |
VARCHAR(9)Nullable |
Indicates the EXTIND attribute:
- *EXTIND
- Extended indicator support is enabled.
- *NOEXTIND
- Extended indicator support is not enabled.
Contains null if the program is an external
procedure 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 null if the program is an external
procedure without SQL statements.
|
| NAMING |
NAMING |
VARCHAR(4)Nullable |
Indicates the NAMING attribute:
- *SYS
- This is system naming.
- *SQL
- This is SQL naming.
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).
- ANY
- The statements are valid on any supported IBM i release.
- VxRxMx
- The statement is valid on IBM i VxRxMx release or later.
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.
- rdb-name
- The name of the relational database.
- *NONE
- A relational database was not specified.
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:
- *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 null if the program is an external
procedure 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 null if the program is an external
procedure 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 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:
- *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 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:
- *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) or if the program is an external procedure
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 null if the program is an external
procedure 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 null if the program is an external
procedure 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 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:
- *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 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:
- *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 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:
- 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 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:
- *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 null if the program is an external
procedure without SQL statements.
|
| DECRESULT_MAXIMUM_PRECISION |
DECMAXPRC |
SMALLINTNullable |
Specifies the maximum precision.
- 31
- The maximum precision is 31.
- 63
- The maximum precision is 63.
Contains null if the program is an external
procedure without SQL statements.
|
| DECRESULT_MAXIMUM_SCALE |
DECMAXSCL |
SMALLINTNullable |
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 |
SMALLINTNullable |
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:
- 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 null if the program is an external
procedure 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 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:
- *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 null if the program is an external
procedure 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 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. |