Each row represents a user-defined routine (scalar function, table function, sourced function, method, or procedure). Does not include built-in functions.
Column Name | Data Type | Nullable | Description |
---|---|---|---|
ROUTINESCHEMA | VARCHAR (128) | Schema name of the routine if ROUTINEMODULEID is null; otherwise schema name of the module to which the routine belongs. | |
ROUTINEMODULENAME | VARCHAR (128) | Y | Unqualified name of the module to which the routine belongs. The null value if not a module routine. |
ROUTINENAME | VARCHAR (128) | Unqualified name of the routine. | |
ROUTINETYPE | CHAR (1) | Type of routine.
|
|
OWNER | VARCHAR (128) | Authorization ID of the owner of the routine. | |
OWNERTYPE | CHAR (1) |
|
|
SPECIFICNAME | VARCHAR (128) | Name of the routine instance (might be system-generated). | |
ROUTINEID | INTEGER | Identifier for the routine. | |
ROUTINEMODULEID | INTEGER | Y | Identifier for the module to which the routine belongs. The null value if not a module routine. |
RETURN_TYPESCHEMA | VARCHAR (128) | Y | Schema name of the return type for a scalar function or method. |
RETURN_TYPEMODULE | VARCHAR (128) | Y | The module name of the return type; the null value if the return type does not belong to any module. |
RETURN_TYPENAME | VARCHAR (128) | Y | Unqualified name of the return type for a scalar function or method. |
ORIGIN | CHAR (1) |
|
|
FUNCTIONTYPE | CHAR (1) |
|
|
PARM_COUNT | SMALLINT | Number of routine parameters. | |
LANGUAGE | CHAR (8) | Implementation language for the routine body (or for the source function body, if this function is sourced on another function). Possible values are 'C', 'COBOL', 'JAVA', 'OLE', 'OLEDB', or 'SQL'. Blanks if ORIGIN is not 'E', 'Q', or 'R'. | |
DIALECT | VARCHAR (10) | The source dialect of the SQL routine body:
|
|
SOURCESCHEMA | VARCHAR (128) | Y | If ORIGIN = 'U' and the source function is a user-defined function, contains the schema name of the specific name of the source function. If ORIGIN = 'U' and the source function is a built-in function, contains the value 'SYSIBM'. The null value if ORIGIN is not 'U'. |
SOURCESPECIFIC | VARCHAR (128) | Y | If ORIGIN = 'U' and the source function is a user-defined function, contains the unqualified specific name of the source function. If ORIGIN = 'U' and the source function is a built-in function, contains the value 'N/A for built-in'. The null value if ORIGIN is not 'U'. |
PUBLISHED | CHAR (1) | Inidicates whether the module routine can be invoked outside
its module.
|
|
DETERMINISTIC | CHAR (1) |
|
|
EXTERNAL_ACTION | CHAR (1) |
|
|
NULLCALL | CHAR (1) |
|
|
CAST_FUNCTION | CHAR (1) |
|
|
ASSIGN_FUNCTION | CHAR (1) |
|
|
SCRATCHPAD | CHAR (1) |
|
|
SCRATCHPAD_LENGTH | SMALLINT | Size (in bytes) of the scratchpad for the routine.
|
|
FINALCALL | CHAR (1) |
|
|
PARALLEL | CHAR (1) |
|
|
PARAMETER_STYLE | CHAR (8) | Parameter style that was declared when the routine was created.
Possible values are:
|
|
FENCED | CHAR (1) |
|
|
SQL_DATA_ACCESS | CHAR (1) | Indicates what type of SQL statements, if any, the database
manager should assume is contained in the routine.
|
|
DBINFO | CHAR (1) | Indicates whether a DBINFO parameter is passed to an external
routine.
|
|
PROGRAMTYPE | CHAR (1) | Indicates how the external routine is invoked.
|
|
COMMIT_ON_RETURN | CHAR (1) | Indicates whether the transaction is committed on successful
return from this procedure.
|
|
AUTONOMOUS | CHAR (1) | Indicates whether or not the routine executes autonomously.
|
|
RESULT_SETS | SMALLINT | Estimated maximum number of result sets. | |
SPEC_REG | CHAR (1) | Indicates the special registers values that are used when the
routine is called.
|
|
FEDERATED | CHAR (1) | Indicates whether or not federated objects can be accessed
from the routine.
|
|
THREADSAFE | CHAR (1) | Indicates whether or not the routine can run in the same process
as other routines.
|
|
VALID | CHAR (1) | Applies to LANGUAGE = 'SQL' and routines having parameters
with default; blank otherwise.
|
|
MODULEROUTINEIMPLEMENTED | CHAR (1) |
|
|
METHODIMPLEMENTED | CHAR (1) |
|
|
METHODEFFECT | CHAR (2) |
|
|
TYPE_PRESERVING | CHAR (1) |
|
|
WITH_FUNC_ACCESS | CHAR (1) |
|
|
OVERRIDDEN_METHODID | INTEGER | Y | Identifier for the overridden method when the OVERRIDING option is specified for a user-defined method. The null value if ROUTINETYPE is not 'M'. |
SUBJECT_TYPESCHEMA | VARCHAR (128) | Y | Schema name of the subject type for the user-defined method. The null value if ROUTINETYPE is not 'M'. |
SUBJECT_TYPENAME | VARCHAR (128) | Y | Unqualified name of the subject type for the user-defined method. The null value if ROUTINETYPE is not 'M'. |
CLASS | VARCHAR (384) | Y | For LANGUAGE JAVA, CLR, or OLE, this is the class that implements this routine; null value otherwise. |
JAR_ID | VARCHAR (128) | Y | For LANGUAGE JAVA, this is the JAR_ID of the installed jar file that implements this routine if a jar file was specified at routine creation time; null value otherwise. For LANGUAGE CLR, this is the assembly file that implements this routine. |
JARSCHEMA | VARCHAR (128) | Y | For LANGUAGE JAVA when a JAR_ID is present, this is the schema name of the jar file that implements this routine; null value otherwise. |
JAR_SIGNATURE | VARCHAR (2048) | Y | For LANGUAGE JAVA, this is the method signature of this routine's specified Java™ method. For LANGUAGE CLR, this is a reference field for this CLR routine. Null value otherwise. |
CREATE_TIME | TIMESTAMP | Time at which the routine was created. | |
ALTER_TIME | TIMESTAMP | Time at which the routine was last altered. | |
FUNC_PATH | CLOB (2K) | Y | SQL path in effect when the routine was defined. The null value if LANGUAGE is not 'SQL' and no parameters have defaults. |
QUALIFIER | VARCHAR (128) | Value of the default schema at the time of object definition. Used to complete any unqualified references. | |
IOS_PER_INVOC | DOUBLE | Estimated number of inputs/outputs (I/Os) per invocation; 0 is the default; -1 if not known. | |
INSTS_PER_INVOC | DOUBLE | Estimated number of instructions per invocation; 450 is the default; -1 if not known. | |
IOS_PER_ARGBYTE | DOUBLE | Estimated number of I/Os per input argument byte; 0 is the default; -1 if not known. | |
INSTS_PER_ARGBYTE | DOUBLE | Estimated number of instructions per input argument byte; 0 is the default; -1 if not known. | |
PERCENT_ARGBYTES | SMALLINT | Estimated average percent of input argument bytes that the routine will actually read; 100 is the default; -1 if not known. | |
INITIAL_IOS | DOUBLE | Estimated number of I/Os performed the first time that the routine is invoked; 0 is the default; -1 if not known. | |
INITIAL_INSTS | DOUBLE | Estimated number of instructions executed the first time the routine is invoked; 0 is the default; -1 if not known. | |
CARDINALITY | BIGINT | Predicted cardinality of a table function; -1 if not known, or if the routine is not a table function. | |
SELECTIVITY2 | DOUBLE | For user-defined predicates; -1 if there are no user-defined predicates. | |
RESULT_COLS | SMALLINT | For a table function (ROUTINETYPE = 'F' and FUNCTIONTYPE = 'T'), contains the number of columns in the result table; for a procedure (ROUTINETYPE = 'P'), contains 0; contains 1 otherwise. | |
IMPLEMENTATION | VARCHAR (762) | Y | If ORIGIN = 'E', identifies the path/module/function that implements this function. If ORIGIN = 'U' and the source function is built-in, this column contains the name and signature of the source function. Null value otherwise. |
LIB_ID | INTEGER | Y | Internal identifier for compiled SQL routines. Otherwise the null value. |
TEXT_BODY_OFFSET | INTEGER | If LANGUAGE = 'SQL', the offset to the start of the compiled SQL routine body in the full text of the CREATE statement; -1 if LANGUAGE is not 'SQL' or the SQL routine is not compiled. | |
TEXT | CLOB (2M) | Y | If LANGUAGE = 'SQL', the full text of the CREATE FUNCTION, CREATE METHOD, or CREATE PROCEDURE statement; null value otherwise. |
NEWSAVEPOINTLEVEL | CHAR (1) | Indicates whether the routine initiates a new savepoint level
when it is invoked.
|
|
DEBUG_MODE3 | VARCHAR (8) | Indicates whether or not the routine can be debugged using
the DB2 debugger.
|
|
TRACE_LEVEL | VARCHAR (1) | Y | Reserved for future use. |
DIAGNOSTIC_LEVEL | VARCHAR (1) | Y | Reserved for future use. |
CHECKOUT_USERID | VARCHAR (128) | Y | ID of the user who performed a checkout of the object; the null value if the object is not checked out. |
PRECOMPILE_OPTIONS | VARCHAR (1024) | Y | The precompile and bind options that were in effect when the compiled SQL routine was created. The null value if LANGUAGE is not 'SQL' or if the SQL routine is not compiled. |
COMPILE_OPTIONS | VARCHAR (1024) | Y | The value of the SQL_CCFLAGS special register that was in effect when the compiled SQL routine was created and inquiry directives were present. An empty string if no inquiry directives were present in the compiled SQL routine. The null value if LANGUAGE is not 'SQL' or if the SQL routine is not compiled. |
EXECUTION_CONTROL | CHAR (1) | Execution control mode of a common language runtime (CLR)
routine. Possible values are:
|
|
CODEPAGE | SMALLINT | Routine code page, which specifies the default code page used for all character parameter types, result types, and local variables within the routine body. | |
COLLATIONSCHEMA | VARCHAR (128) | Schema name of the collation for the routine. | |
COLLATIONNAME | VARCHAR (128) | Unqualified name of the collation for the routine. | |
COLLATIONSCHEMA_ORDERBY | VARCHAR (128) | Schema name of the collation for ORDER BY clauses in the routine. | |
COLLATIONNAME_ORDERBY | VARCHAR (128) | Unqualified name of the collation for ORDER BY clauses in the routine. | |
ENCODING_SCHEME | CHAR (1) | Encoding scheme of the routine, as specified in the PARAMETER
CCSID clause. Possible values are:
|
|
LAST_REGEN_TIME | TIMESTAMP | Time at which the SQL routine packed descriptor was last regenerated. | |
INHERITLOCKREQUEST | CHAR (1) |
|
|
DEFINER4 | VARCHAR (128) | Authorization ID of the owner of the routine. | |
SECURE | CHAR (1) | Indicates whether the function is secure for row and column
access control
|
|
REMARKS | VARCHAR (254) | Y | User-provided comments, or the null value. |
Note:
|