DB2 10.5 for Linux, UNIX, and Windows

SYSCAT.ROUTINES catalog view

Each row represents a user-defined routine (scalar function, table function, sourced function, method, or procedure). Does not include built-in functions.

Table 1. SYSCAT.ROUTINES Catalog View
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.
  • F = Function
  • M = Method
  • P = Procedure
OWNER VARCHAR (128)   Authorization ID of the owner of the routine.
OWNERTYPE CHAR (1)  
  • S = The owner is the system
  • U = The owner is an individual user
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)  
  • B = Built-in
  • E = User-defined, external
  • M = Template function
  • F = Federated procedure
  • Q = SQL-bodied1
  • R = System-generated SQL-bodied routine
  • S = System-generated
  • T = System-generated transform function (not directly invokable)
  • U = User-defined, based on a source
FUNCTIONTYPE CHAR (1)  
  • C = Column or aggregate
  • R = Row
  • S = Scalar
  • T = Table
  • Blank = Procedure
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:
  • DB2® SQL PL
  • PL/SQL
  • Blank = Not an SQL routine
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'.
SOURCEMODULENAME VARCHAR (128) Y Contains the module name of the specific name of the source function if ORIGIN = 'U' and the source function is a user-defined function defined in a module; the null value otherwise.
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.
  • N = The module routine is not published
  • Y = The module routine is published
  • Blank = Not applicable
DETERMINISTIC CHAR (1)  
  • N = Results are not deterministic (same parameters might give different results in different routine calls)
  • Y = Results are deterministic
  • Blank = ORIGIN is not 'E', 'F', 'Q', or 'R'
EXTERNAL_ACTION CHAR (1)  
  • E = Function has external side-effects (therefore, the number of invocations is important)
  • N = No side-effects
  • Blank = ORIGIN is not 'E', 'F', 'Q', or 'R'
NULLCALL CHAR (1)  
  • N = RETURNS NULL ON NULL INPUT (function result is implicitly the null value if one or more operands are null)
  • Y = CALLED ON NULL INPUT
  • Blank = ORIGIN is not 'E', 'Q', or 'R'
CAST_FUNCTION CHAR (1)  
  • N = Not a cast function
  • Y = Cast function
  • Blank = ROUTINETYPE is not 'F'
ASSIGN_FUNCTION CHAR (1)  
  • N = Not an assignment function
  • Y = Implicit assignment function
  • Blank = ROUTINETYPE is not 'F'
SCRATCHPAD CHAR (1)  
  • N = Routine has no scratchpad
  • Y = Routine has a scratchpad
  • Blank = ORIGIN is not 'E' or ROUTINETYPE is 'P'
SCRATCHPAD_LENGTH SMALLINT   Size (in bytes) of the scratchpad for the routine.
  • -1 = LANGUAGE is 'OLEDB' and SCRATCHPAD is 'Y'
  • 0 = SCRATCHPAD is not 'Y'
FINALCALL CHAR (1)  
  • N = No final call is made
  • Y = Final call is made to this routine at the runtime end-of-statement
  • Blank = ORIGIN is not 'E' or ROUTINETYPE is 'P'
PARALLEL CHAR (1)  
  • N = Routine cannot be executed in parallel
  • Y = Routine can be executed in parallel
  • Blank = ORIGIN is not 'E'
PARAMETER_STYLE CHAR (8)   Parameter style that was declared when the routine was created. Possible values are:
  • DB2DARI
  • DB2GENRL
  • DB2SQL
  • GENERAL
  • GNRLNULL
  • JAVA
  • SQL
  • Blanks if ORIGIN is not 'E'
FENCED CHAR (1)  
  • N = Not fenced
  • Y = Fenced
  • Blank = ORIGIN is not 'E'
SQL_DATA_ACCESS CHAR (1)   Indicates what type of SQL statements, if any, the database manager should assume is contained in the routine.
  • C = Contains SQL (simple expressions with no subqueries only)
  • M = Contains SQL statements that modify data
  • N = Does not contain SQL statements
  • R = Contains read-only SQL statements
  • Blank = ORIGIN is not 'E', 'F', 'Q', or 'R'
DBINFO CHAR (1)   Indicates whether a DBINFO parameter is passed to an external routine.
  • N = DBINFO is not passed
  • Y = DBINFO is passed
  • Blank = ORIGIN is not 'E'
PROGRAMTYPE CHAR (1)   Indicates how the external routine is invoked.
  • M = Main
  • S = Subroutine
  • Blank = ORIGIN is 'F'
COMMIT_ON_RETURN CHAR (1)   Indicates whether the transaction is committed on successful return from this procedure.
  • N = The unit of work is not committed
  • Y = The unit of work is committed
  • Blank = ROUTINETYPE is not 'P'
AUTONOMOUS CHAR (1)   Indicates whether or not the routine executes autonomously.
  • N = Routine does not execute autonomously from invoking transaction
  • Y = Routine executes autonomously from invoking transaction
  • Blank = ROUTINETYPE is not 'P'
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.
  • I = Inherited special registers
  • Blank = PARAMETER_STYLE is 'DB2DARI' or ORIGIN is not 'E', 'Q', or 'R'
FEDERATED CHAR (1)   Indicates whether or not federated objects can be accessed from the routine.
  • Y = Federated objects can be accessed
  • Blank = ORIGIN is not 'F'
THREADSAFE CHAR (1)   Indicates whether or not the routine can run in the same process as other routines.
  • N = Routine is not threadsafe
  • Y = Routine is threadsafe
  • Blank = ORIGIN is not 'E'
VALID CHAR (1)   Applies to LANGUAGE = 'SQL' and routines having parameters with default; blank otherwise.
  • N = Routine needs rebinding
  • X = Routine is inoperative and must be recreated
  • Y = Routine is valid
MODULEROUTINEIMPLEMENTED CHAR (1)  
  • N = Module routine body is not implemented
  • Y = Module routine body is implemented
  • Blank = ROUTINEMODULENAME is null value
METHODIMPLEMENTED CHAR (1)  
  • N = Method body is not implemented
  • Y = Method body is implemented
  • Blank = ROUTINETYPE is not 'M' or ROUTINEMODULENAME is not the null value
METHODEFFECT CHAR (2)  
  • CN = Constructor method
  • MU = Mutator method
  • OB = Observer method
  • Blanks = Not a system method
TYPE_PRESERVING CHAR (1)  
  • N = Return type is the declared return type of the method
  • Y = Return type is governed by a "type-preserving" parameter; all system-generated mutator methods are type-preserving
  • Blank = ROUTINETYPE is not 'M'
WITH_FUNC_ACCESS CHAR (1)  
  • N = This method cannot be invoked by using functional notation
  • Y = This method can be invoked by using functional notation; that is, the "WITH FUNCTION ACCESS" attribute is specified
  • Blank = ROUTINETYPE is not 'M'
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.
  • N = A new savepoint level is not initiated when the routine is invoked; the routine uses the existing savepoint level
  • Y = A new savepoint level is initiated when the routine is invoked
  • Blank = Not applicable
DEBUG_MODE3 VARCHAR (8)   Indicates whether the routine can be debugged using the debugger that is integrated with the database.
  • DISALLOW = Routine is not debuggable
  • ALLOW = Routine is debuggable, and can participate in a client debug session with the integrated debugger
  • DISABLE = Routine is not debuggable, and this setting cannot be altered without dropping and recreating the routine
  • Blank = Routine type is not currently supported by the integrated 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:
  • N = Network
  • R = Fileread
  • S = Safe
  • U = Unsafe
  • W = Filewrite
  • Blank = LANGUAGE is not 'CLR'
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:
  • A = ASCII
  • U = UNICODE
  • Blank = PARAMETER CCSID clause was not specified
LAST_REGEN_TIME TIMESTAMP   Time at which the SQL routine packed descriptor was last regenerated.
INHERITLOCKREQUEST CHAR (1)  
  • N = This function or method cannot be invoked in the context of an SQL statement that includes a lock-request-clause as part of a specified isolation-clause
  • Y = This function or method inherits the isolation level of the invoking statement; it also inherits the specified lock-request-clause
  • Blank = LANGUAGE is not 'SQL' or ROUTINETYPE is 'P'
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
  • N = Not secure
  • Y = Secure
  • Blank = ROUTINETYPE is not 'F'
ENVSTRINGUNITS VARCHAR (11)   Default string units when the object was created.
REMARKS VARCHAR (254) Y User-provided comments, or the null value.
Note:
  1. For SQL procedures created before Version 8.2 and upgraded to Version 9, 'E' (instead of 'Q').
  2. During database upgrade, the SELECTIVITY column will be set to -1 in the packed descriptor and system catalogs for all user-defined routines. For a user-defined predicate, the selectivity in the system catalog will be -1. In this case, the selectivity value used by the optimizer is 0.01.
  3. For Java routines, the DEBUG_MODE setting does not indicate whether the Java routine was actually compiled in debug mode, or whether a debug Jar was installed at the server.
  4. The DEFINER column is included for backwards compatibility. See OWNER.