SYSROUTINES catalog table

The SYSROUTINES table contains a row for every routine. (A routine can be a user-defined function, cast function, or stored procedure.) The schema is SYSIBM.

Table 1. SYSIBM.SYSROUTINES table column descriptions
Column name Data type Description Use
SCHEMA
VARCHAR(128)
NOT NULL
 Schema of the routine. G
OWNER
VARCHAR(128)
NOT NULL
 Owner of the routine. G
NAME
VARCHAR(128)
NOT NULL
 Name of the routine. G
ROUTINETYPE
CHAR(1)
NOT NULL
 Type of routine:
F
User-defined function or cast function
P
Stored procedure
 G
CREATEDBY
VARCHAR(128)
NOT NULL
 Primary authorization ID of the user who created the routine. G
SPECIFICNAME
VARCHAR(128)
NOT NULL
 Specific name of the routine. G
ROUTINEID
INTEGER
NOT NULL
 Internal identifier of the routine. S
RETURN_TYPE
INTEGER
NOT NULL
 Internal identifier of the result data type of the function. The column contains a -2 if the function is a table function. S
ORIGIN
CHAR(1)
NOT NULL
 Origin of the routine:
E
External routine or external SQL procedure
N
Native SQL procedure
Q
SQL function
S
System-generated function
U
Sourced on user-defined function or built-in function
 G
FUNCTION_TYPE
CHAR(1)
NOT NULL
 Type of function:
C
Aggregate function
S
Scalar function
T
Table function
blank
For a stored procedure (ROUTINETYPE = 'P')
 G
PARM_COUNT
SMALLINT
NOT NULL
 Number of parameters for the routine. G
LANGUAGE
VARCHAR(24)
NOT NULL
 Implementation language of the routine:
  • ASSEMBLE
  • C
  • COBOL
  • COMPJAVA
  • JAVA
  • PLI
  • REXX
  • SQL
The value is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E' or not 'Q'.		 G
COLLID
VARCHAR(128)
NOT NULL
 Name of the package collection to be used when the routine is executed. A blank value indicates the package collection is the same as the package collection of the program that invoked the routine. G
SOURCESCHEMA
VARCHAR(128)
NOT NULL
 If ORIGIN is 'U' and ROUTINETYPE is 'F', the schema of the source user-defined function ('SYSIBM' for a source built-in function). Otherwise, the value is blank. G
SOURCESPECIFIC
VARCHAR(128)
NOT NULL
 If ORIGIN is 'U' and ROUTINETYPE is 'F', the specific name of the source user-defined function or source built-in function name. Otherwise, the value is blank. G
DETERMINISTIC
CHAR(1)
NOT NULL
 The deterministic option of an external function or a stored procedure:
N
Indeterminate (results might differ with a given set of input values).
Y
Deterministic (results are consistent).
blank
ROUTINETYPE='F' and ORIGIN is not 'E' or not 'Q' (the routine is a function, but not an external function or an SQL function).
 G
EXTERNAL_ACTION
CHAR(1)
NOT NULL
 The external action option of an external function or SQL function:
N
Function has no side effects.
E
Function has external side effects so that the number of invocations is important.
blank
ORIGIN is not 'E' or 'Q' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').
 G
NULL_CALL
CHAR(1)
NOT NULL
 The CALLED ON NOT NULL INPUT option of an external function or stored procedure:
N
The routine is not called if any parameter has a NULL value.
Y
The routine is called if any parameter has a NULL value.
blank
ROUTINETYPE='F' and ORIGIN is not 'E' (the routine is a function, but not an external function).
 G
CAST_FUNCTION
CHAR(1)
NOT NULL
 Whether the routine is a cast function:
N
The routine is not a cast function.
Y
The routine is a cast function.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').

A cast function is generated by Db2 for a CREATE TYPE statement.

 G
SCRATCHPAD
CHAR(1)
NOT NULL
 The SCRATCHPAD option of an external function:
N
This function does not have a SCRATCHPAD.
Y
This function has a SCRATCHPAD.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').
 G
SCRATCHPAD_LENGTH
INTEGER
NOT NULL
 Length of the scratchpad if the ORIGIN is 'E' for the function (ROUTINETYPE='F') and NO SCRATCHPAD is not specified. Otherwise, the value is 0. G
FINAL_CALL
CHAR(1)
NOT NULL
 The FINAL CALL option of an external function:
N
A final call will not be made to the function.
Y
A final call will be made to the function.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').
 G
PARALLEL
CHAR(1)
NOT NULL
 The PARALLEL option of an external function:
A
This function can be invoked by parallel tasks.
D
This function cannot be invoked by parallel tasks.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').
 G
PARAMETER_STYLE
CHAR(1)
NOT NULL
 The PARAMETER STYLE option of an external function or stored procedure:
D
DB2SQL. All parameters are passed to the external function or stored procedure according to the DB2SQL standard convention.
G
GENERAL. All parameters are passed to the stored procedure according to the GENERAL standard convention.
N
GENERAL CALL WITH NULLS. All parameters are passed to the stored procedure according to the GENERAL WITH NULLS convention.
J
JAVA. All parameters are passed to the function or procedure according to the conventions for JAVA and SQLJ specifications.
blank
The column is blank if the ORIGIN is not 'E' or if LANGUAGE is SQL.
 G
FENCED
CHAR(1)
NOT NULL
Y
Indicates that this routine runs separately from the Db2 address space in a WLM managed Db2 address space. All user-defined routines that are not marked with Y in this column run in the Db2 address space.
blank
ORIGIN is 'Q' or ORIGIN is 'N'.
 G
SQL_DATA_ACCESS
CHAR(1)
NOT NULL
 The SQL statements that are allowed in an external function, SQL function, or stored procedure:
C
CONTAINS SQL - Only SQL that does not read or modify data is allowed.
M
MODIFIES SQL DATA - All SQL is allowed, including SQL that reads or modifies data.
N
NO SQL - SQL is not allowed.
R
READS SQL DATA - Only SQL that reads data is allowed.
blank
Not applicable.
 G
DBINFO
CHAR(1)
NOT NULL
 The DBINFO option of an external function or stored procedure:
N
No, the DBINFO parameter will not be passed to the external function or stored procedure.
Y
Yes, the DBINFO parameter will be passed to the external function or stored procedure.
blank
ORIGIN is not 'E'.
 G
STAYRESIDENT
CHAR(1)
NOT NULL
 The STAYRESIDENT option of the routine, which determines whether the routine is to be deleted from memory when the routine ends.
N
The load module is to be deleted from memory after the routine terminates.
Y
The load module is to remain resident in memory after the routine terminates.
blank
ORIGIN is not 'E'.
 G
ASUTIME
INTEGER
NOT NULL
 Number of CPU service units permitted for any single invocation of this routine. If ASUTIME is zero, the number of CPU service units is unlimited. The value is 0 if ROUTINETYPE = 'F' and ORIGIN is not 'E'.

If a routine consumes more CPU service units than the ASUTIME value allows, Db2 cancels the routine.

 G
WLM_ENVIRONMENT
VARCHAR(96)
NOT NULL
 Name of the WLM environment to be used to run this routine.

When ORIGIN = 'N', this is the name of the WLM ENVIRONMENT FOR DEBUG MODE that is to be used when debugging a native SQL procedure.

The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'. If the ROUTINETYPE = 'P', the value might be blank. If this value is blank the stored procedure cannot be run.

 G
WLM_ENV_FOR_
NESTED
CHAR(1)
NOT NULL
 For nested routine calls, indicates whether the address space of the calling stored procedure or user-defined function is used to run the nested stored procedure or user-defined function:
N
The nested stored procedure or user-defined function runs in an address space other than the specified WLM environment if the calling stored procedure or user-defined function is not running in the specified WLM environment. 'WLM ENVIRONMENT name' was specified.
Y
The nested stored procedure or user-defined function runs in the environment used by the calling stored procedure or user-defined function. 'WLM ENVIRONMENT(name,*)' was specified.
blank
WLM_ENVIRONMENT is blank. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.
 G
PROGRAM_TYPE
CHAR(1)
NOT NULL
 Indicates whether the routine runs as a Language Environment® main routine or a subroutine:
M
The routine runs as a main routine.
S
The routine runs as a subroutine.
blank
ORIGIN is not 'E'.
 G
EXTERNAL_SECURITY
CHAR(1)
NOT NULL
 Specifies the authorization ID to be used if the routine accesses resources protected by an external security product:
D
Db2 - The authorization ID associated with the WLM-established stored procedure address space.
U
SESSION_USER - The authorization ID of the SQL user that invoked the routine.
C
DEFINER - The authorization ID of the owner of the routine.
blank
ORIGIN is not 'E'.
 G
COMMIT_ON_RETURN
CHAR(1)
NOT NULL
 If ROUTINETYPE = 'P', whether the transaction is always to be committed immediately on successful return (non-negative SQLCODE) from this stored procedure:
N
The unit of work is to continue.
Y
The unit of work is to be committed immediately.
A
The unit of work of the autonomous procedure is committed immediately, but other work of the calling application is not committed.
If ROUTINETYPE = 'F', the value is blank.		 G
RESULT_SETS
SMALLINT
NOT NULL
 If ROUTINETYPE = 'P', the maximum number of ad hoc result sets that this stored procedure can return.

If no ad hoc result sets exist or ROUTINETYPE = 'F', the value is zero.

 G
LOBCOLUMNS
SMALLINT
NOT NULL
 If ORIGIN = 'E' or 'Q', the number of LOB columns found in the parameter list for this user-defined function.

If no LOB columns are found in the parameter list or ORIGIN is not 'E' or not 'Q', the value is 0.

 S
CREATEDTS
TIMESTAMP
NOT NULL
 Start of changeTime when the routine was created or re-created.End of change G
ALTEREDTS
TIMESTAMP
NOT NULL
 Time when the last ALTER statement was executed for this routine. G
IBMREQD
CHAR(1)
NOT NULL
 A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators.

The value in this field is not a reliable indicator of release dependencies. RELCREATED should be used instead.

 G
PARM1
SMALLINT
NOT NULL
 Internal use only. I
PARM2
SMALLINT
NOT NULL
 Internal use only. I
PARM3
SMALLINT
NOT NULL
 Internal use only. I
PARM4
SMALLINT
NOT NULL
 Internal use only. I
PARM5
SMALLINT
NOT NULL
 Internal use only. I
PARM6
SMALLINT
NOT NULL
 Internal use only. I
PARM7
SMALLINT
NOT NULL
 Internal use only. I
PARM8
SMALLINT
NOT NULL
 Internal use only. I
PARM9
SMALLINT
NOT NULL
 Internal use only. I
PARM10
SMALLINT
NOT NULL
 Internal use only. I
PARM11
SMALLINT
NOT NULL
 Internal use only. I
PARM12
SMALLINT
NOT NULL
 Internal use only. I
PARM13
SMALLINT
NOT NULL
 Internal use only. I
PARM14
SMALLINT
NOT NULL
 Internal use only. I
PARM15
SMALLINT
NOT NULL
 Internal use only. I
PARM16
SMALLINT
NOT NULL
 Internal use only. I
PARM17
SMALLINT
NOT NULL
 Internal use only. I
PARM18
SMALLINT
NOT NULL
 Internal use only. I
PARM19
SMALLINT
NOT NULL
 Internal use only. I
PARM20
SMALLINT
NOT NULL
 Internal use only. I
PARM21
SMALLINT
NOT NULL
 Internal use only. I
PARM22
SMALLINT
NOT NULL
 Internal use only. I
PARM23
SMALLINT
NOT NULL
 Internal use only. I
PARM24
SMALLINT
NOT NULL
 Internal use only. I
PARM25
SMALLINT
NOT NULL
 Internal use only. I
PARM26
SMALLINT
NOT NULL
 Internal use only. I
PARM27
SMALLINT
NOT NULL
 Internal use only. I
PARM28
SMALLINT
NOT NULL
 Internal use only. I
PARM29
SMALLINT
NOT NULL
 Internal use only. I
PARM30
SMALLINT
NOT NULL
 Internal use only. I
IOS_PER_INVOC
FLOAT
NOT NULL WITH
DEFAULT -1
 Estimated number of I/Os that required to execute the routine. The value is -1 if the estimated number is not known. S
INSTS_PER_INVOC
FLOAT
NOT NULL WITH
DEFAULT -1
 Estimated number of machine instructions that required to execute the routine. The value is -1 if the estimated number is not known. S
INITIAL_IOS
FLOAT
NOT NULL WITH
DEFAULT -1
 Estimated number of I/O's that are performed the first time or the last time the routine is invoked. The value is -1 if the estimated number is not known. S
INITIAL_INSTS
FLOAT
NOT NULL WITH
DEFAULT -1
 Estimated number of machine instructions that are performed the first time or the last time the routine is invoked. The value is -1 if the estimated number is not known. S
CARDINALITY
FLOAT
NOT NULL WITH
DEFAULT -1
 The predicted cardinality of the routine, -1 to trigger the use of the default value (10,000). S
RESULT_COLS
SMALLINT
NOT NULL
DEFAULT 1
 For a table function, the number of columns in the result table. Otherwise, the value is 1. S
EXTERNAL_NAME
VARCHAR(762)
NOT NULL
 The path/module/function that Db2 should load to execute the routine. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'. G
—
VARCHAR(150)
NOT NULL
FOR BIT DATA
 Internal use only. I
RUNOPTS
VARCHAR(762)
NOT NULL
 The Language Environment run time options to be used for this routine. An empty string indicates that the installation default Language Environment run time options are to be used. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'. G
REMARKS
VARCHAR(762)
NOT NULL
 A character string provided by the user with the COMMENT statement. G
JAVA_SIGNATURE
VARCHAR(3072)
NOT NULL WITH
DEFAULT
 The signature of the JAR file.
blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.
 G
CLASS
VARCHAR(384)
NOT NULL WITH
DEFAULT
 The class name contained in the JAR file.
blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.
G
JARSCHEMA
VARCHAR(128)
NOT NULL WITH
DEFAULT
 The schema of the JAR file.
blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.
G
JAR_ID
VARCHAR(128)
NOT NULL WITH
DEFAULT
 The name of the JAR file.
blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.
 G
SPECIAL_REGS
CHAR(1)
NOT NULL WITH
DEFAULT 'I'
 The SPECIAL REGISTER option for a routine.
I
INHERIT SPECIAL REGISTERS
D
DEFAULT SPECIAL REGISTERS
blank
ROUTINETYPE = 'F' and ORIGIN is not 'E' or not 'Q'.
 G
NUM_DEP_MQTS
SMALLINT
NOT NULL WITH
DEFAULT
 Number of dependent materialized query tables. The value is 0 if the row does not describe a user-defined table function, or if no materialized query tables are defined on the table function. G
MAX_FAILURE
SMALLINT
NOT NULL WITH
DEFAULT -1
 Allowable failures for this routine (0-32767). If zero is specified, the routine will never be stopped. If no value is specified for this routine, the default will be -1 to indicate that the Db2 installation parameter (STORMXAB) will be used. G
PARAMETER_CCSID
INTEGER
NOT NULL WITH
DEFAULT
A CCSID that specifies how character, graphic, date, time, and timestamp data types for system generated parameters to the routine such as message tokens and DBINFO should be passed. The value is dependent on the encoding scheme specified implicitly or explicitly for the PARAMETER CCSID clause defined at the system for that encoding scheme. The following list describes the CCSID for each encoding scheme:
ASCII
If mixed data is allowed, this CCSID is for mixed ASCII data, SBCS data uses the corresponding SBCS CCSID, and graphic data uses the corresponding DBCS CCSID. Otherwise, this CCSID is for SBCS ASCII data.
EBCDIC
If mixed data is allowed, this CCSID is for mixed EBCDIC data, SBCS data uses the corresponding SBCS CCSID, and graphic data uses the corresponding DBCS CCSID. Otherwise, this is the CCSID for SBCS EBCDIC data.
Unicode
This CCSID is for mixed data (1208).

A value of zero means that the CCSIDs used are those CCSIDs for the encoding scheme of other string or datetime parameters in the parameter list or RETURNS clause CCSID clauses, or the value in the DEF ENCODING SCHEME on installation panel DSNTIPF.

G
VERSION
VARCHAR(122)
NOT NULL WITH
DEFAULT

The version identifier for a native SQL procedure (indicated by the value 'N' in the column ORIGIN) or a non-inline SQL scalar function (indicated by the value 'Q' in the column ORIGIN and 'N' in the column INLINE).

A zero length string for the rows that are created prior to Version 9 and for the rows that correspond to neither native SQL procedures or non-inline SQL scalar functions.

 G
CONTOKEN
CHAR(8)
NOT NULL WITH
DEFAULT FOR
BIT DATA

The consistency token for the routine. The column is set to X'20' if the value of ORIGIN is not 'N'

 G
ACTIVE
CHAR(1)
NOT NULL WITH
DEFAULT
Identifies the active version of the routine:
Y
This version is active.
N
This version is not active.
blank
The value of ORIGIN is not 'N' or the row was created prior to Version 9.
 G
DEBUG_MODE
CHAR(1)
NOT NULL WITH
DEFAULT
Identifies whether or not this routine is enabled for debugging:
1
This routine is enabled for debugging and can be debugged in a client debug session using the Db2 Unified Debugger.
0
This routine is not enabled for debugging.
N
This routine can never be enabled for debugging.
blank
The LANGUAGE is not specified as JAVA, the value of ORIGIN is not 'N', or the row was created prior to Version 9.
 G
TEXT_ENVID
INTEGER
NOT NULL WITH
DEFAULT

Start of changeInternal identifier of the environment. The column value is 0 if the value of ORIGIN is not 'N' or 'Q', or if the row was created prior to Version 9.End of change

 G
TEXT_ROWID
ROWID
NOT NULL
GENERATED
ALWAYS

ID to support LOB columns for source text.

 G
TEXT
CLOB(2M)
NOT NULL WITH
DEFAULT

The text of the statement that created the SQL routine, including the body of the routine. In some cases, this column might instead contain the text of a statement that altered the SQL routine. In many cases, changes to routines or to objects on which routines are dependent do not update this value.

The value is a zero-length string if the value of ORIGIN is not 'N' or if the row was created prior to Version 9.

 G
OWNERTYPE
CHAR(1)
NOT NULL WITH
DEFAULT

Indicates the type of owner:

blank
Authorization ID
L
Role
 G
PARAMETER_
VARCHARFORM
INTEGER
NOT NULL WITH
DEFAULT

A non-zero value that indicates the actual representation, to a LANGUAGE C routine, of any varying length string parameter that appears in the parameter list or RETURNS clause for that routine.

 G
RELCREATED
CHAR(1)
NOT NULL
 The release of Db2 that is used to create the object. Blank if created prior to Version 9. See Release dependency indicators for all other values. G
PACKAGEPATH
VARCHAR(4096)
 The value of the PACKAGE PATH option of the CREATE FUNCTION, CREATE PROCEDURE, ALTER FUNCTION, or ALTER PROCEDURE statement that created or last changed the routine. PACKAGE PATH identifies the package path to use when the routine is executed. A blank value indicates the package path is the same as the package path of the program that invoked the routine. G
SECURE
CHAR(1)
NOT NULL
WITH DEFAULT 'N'
 Indicates if the routine is secured:
N
The routine is not secured
Y
The routine is secured
 G
INLINE
CHAR(1)
NOT NULL
WITH DEFAULT
 Specifies if the SQL function is inline:
Y
The SQL function is inline when referenced. No package is associated with this type of routine.
N
The SQL function has an associated package.
blank
Not an SQL function (the ORIGIN column has a value other than 'Q')
 G
—
BLOB(1G)
NOT NULL
WITH DEFAULT
 Internal use only. I
SYSTEM_DEFINED
CHAR(1)
NOT NULL
WITH DEFAULT
 Identifies whether this routine is system defined:
blank
This routine is not system defined
S
This routine is system defined
 G
Start of changeWRAPPEDEnd of change Start of changeCHAR(1)End of change Start of change
'Y'
The routine text is obfuscated. Obfuscating source code of SQL procedures, SQL functions, and triggers.
blank
The routine text is not obfuscated.
End of change		 Start of changeGEnd of change
Start of changeREGENERATETSEnd of change Start of changeTIMESTAMP(12) NOT NULLEnd of change Start of changeThe time when the object was regenerated. The value is valid only for objects that can be regenerated. If no regeneration has occurred, this column contains the same value as the CREATEDTS column.End of change Start of changeGEnd of change