SYSCAT.ROUTINES catalog view
Each row represents a user-defined routine (scalar function, table function, sourced function, aggregate interface 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; -1 if parameters specified as VARARGS. | |
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, CLR, COBOL, JAVA, OLE, OLEDB, R, 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 = Uand the source function is a user-defined function, contains the schema name of the specific name of the source function. If ORIGIN = Uand the source function is a built-in function, contains the value SYSIBM. The null value if ORIGIN is not U. |
SOURCEMODULENAME | VARCHAR (128) | Y | Contains the module name of the specific name of the source function if ORIGIN = Uand the source function is a user-defined function defined in a module; the null value otherwise. |
SOURCESPECIFIC | VARCHAR (128) | Y | If ORIGIN = Uand the source function is a user-defined function, contains the unqualified specific name of the source function. If ORIGIN = Uand 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) | Indicates 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 = SQLand routines having parameters with default or ORIGIN = A; 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, ORIGIN is not A, 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 = Fand 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 | The value from the EXTERNAL NAME clause if
ORIGIN=E(an external routine); the name and signature of the source function if ORIGIN = Uand the source function is built-in; the entry point in the library if LIB_ID is not null and LANGUAGE= SQL(a compiled SQL routine); the 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 SQLor the SQL routine is not compiled. |
|
TEXT | CLOB (2M) | Y | If LANGUAGE = SQLor ORIGIN = A, 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 the routine
can be debugged using the debugger that is integrated with the database.
|
|
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 SQLor 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 SQLor 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
|
|
ENVSTRINGUNITS | VARCHAR (11) | Default string units when the object was created. | |
STAYRESIDENT | CHAR (1) | The STAYRESIDENT option of the routine, which determines whether the routine
library is to be deleted from memory when the routine ends
|
|
REMARKS | VARCHAR (254) | Y | User-provided comments, or the null value. |
Note:
|