ROUTINES

The ROUTINES view contains one row for each routine.

The following table describes the columns in the view:

Table 1. ROUTINES view
Column Name	Data Type	Description
SPECIFIC_CATALOG	VARCHAR(128)	Relational database name
SPECIFIC_SCHEMA	VARCHAR(128)	Schema name of the routine instance.
SPECIFIC_NAME	VARCHAR(128)	Specific name of the routine.
ROUTINE_CATALOG	VARCHAR(128)	Relational database name
ROUTINE_SCHEMA	VARCHAR(128)	Name of the SQL schema that contains the routine.
ROUTINE_NAME	VARCHAR(128)	Name of the routine.
ROUTINE_TYPE	VARCHAR(15)	Type of the routine. PROCEDURE This is a procedure. FUNCTION This is a function. INSTANCE METHOD This is a built-in data type function created for a distinct type.
MODULE_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
MODULE_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
MODULE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
UDT_CATALOG	VARCHAR(128) Nullable	Relational database name. Contains the null value if this is not an INSTANCE METHOD.
UDT_SCHEMA	VARCHAR(128) Nullable	Name of the SQL schema that contains the distinct type related to this function. Contains the null value if this is not an INSTANCE METHOD.
UDT_NAME	VARCHAR(128) Nullable	Name of the distinct type name related to this function. Contains the null value if this is not an INSTANCE METHOD.
DATA_TYPE	VARCHAR(128) Nullable	Type of the result of the function: BIGINT Big number INTEGER Large number SMALLINT Small number DECIMAL Packed decimal NUMERIC Zoned decimal DOUBLE PRECISION Floating point; DOUBLE PRECISION REAL Floating point; REAL DECFLOAT Decimal floating-point CHARACTER Fixed-length character string CHARACTER VARYING Varying-length character string CHARACTER LARGE OBJECT Character large object string GRAPHIC Fixed-length graphic string GRAPHIC VARYING Varying-length graphic string DOUBLE-BYTE CHARACTER LARGE OBJECT Double-byte character large object string NATIONAL CHARACTER National character NATIONAL CHARACTER VARYING Varying-length national character NATIONAL CHARACTER LARGE OBJECT National character large object BINARY Fixed-length binary string BINARY VARYING Varying-length binary string BINARY LARGE OBJECT Binary large object string DATE Date TIME Time TIMESTAMP Timestamp DATALINK Datalink ROWID Row ID XML XML USER-DEFINED Distinct type or Array type Contains the null value if this is not a scalar function.
CHARACTER_MAXIMUM_LENGTH	INTEGER Nullable	Maximum length of the result string of the function for binary, character, and graphic string and XML data types. Contains the null value if this is not a scalar function or the parameter is not a string.
CHARACTER_OCTET_LENGTH	INTEGER Nullable	Number of bytes for the result string of the function for binary, character, and graphic string and XML data types. Contains the null value if this is not a scalar function or the parameter is not a string.
CHARACTER_SET_CATALOG	VARCHAR(128) Nullable	Relational database name of the result of the function. Contains the null value if this is not a scalar function or the result is not a string.
CHARACTER_SET_SCHEMA	VARCHAR(128) Nullable	The schema name of the character set of the result of the function. Contains 'SYSIBM'. Contains the null value if this is not a scalar function or the result is not a string.
CHARACTER_SET_NAME	VARCHAR(128) Nullable	The character set name of the result of the function. Contains the null value if this is not a scalar function or the result is not a string.
COLLATION_CATALOG	VARCHAR(128) Nullable	Relational database name of the result of the function. Contains the null value if this is not a scalar function or the result is not a string.
COLLATION_SCHEMA	VARCHAR(128) Nullable	The schema of the collation of the result of the function. SYSIBM is returned. Contains the null value if this is not a scalar function or the result is not a string.
COLLATION_NAME	VARCHAR(128) Nullable	The collation name of the result of the function. IBMBINARY is returned. Contains the null value if this is not a scalar function or the result is not a string.
NUMERIC_PRECISION	INTEGER Nullable	The precision of the result of the function. Note: This column supplies the precision of all numeric data types, including single-and double-precision floating point and decimal floating-point. The NUMERIC_PRECISION_RADIX column indicates if the value in this column is in binary or decimal digits. Contains the null value if this is not a scalar function or the result is not numeric.
NUMERIC_PRECISION_RADIX	INTEGER Nullable	Indicates if the precision specified in column NUMERIC_PRECISION is specified as a number of binary or decimal digits: 2 Binary; floating-point precision is specified in binary digits. 10 Decimal; all other numeric types are specified in decimal digits. Contains the null value if this is not a scalar function or the result is not numeric.
NUMERIC_SCALE	INTEGER Nullable	Scale of numeric result of the function. Contains the null value if this is not a scalar function or the result is not numeric.
DATETIME_PRECISION	INTEGER Nullable	The fractional part of a date, time, or timestamp result of the function. 0 For DATE and TIME data types 6 For TIMESTAMP data types (number of microseconds). Contains the null value if this is not a scalar function or the result is not a date, time, or timestamp.
INTERVAL_TYPE	VARCHAR(128) Nullable	Reserved. Contains the null value.
INTERVAL_PRECISION	INTEGER Nullable	Reserved. Contains the null value.
TYPE_UDT_CATALOG	VARCHAR(128) Nullable	The relational database name if the result of the function is a distinct type or array type. Contains the null value if this is not a scalar function or the result is not a distinct type or array type.
TYPE_UDT_SCHEMA	VARCHAR(128) Nullable	The name of the schema if the result of the function is a distinct type or array type. Contains the null value if this is not a scalar function or the result is not a distinct type or array type.
TYPE_UDT_NAME	VARCHAR(128) Nullable	The name of the distinct type if the result of the function is a distinct type or array type. Contains the null value if this is not a scalar function or the result is not a distinct type or array type.
SCOPE_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
MAXIMUM_CARDINALITY	BIGINT Nullable	The maximum cardinality of the array type if the result of the function is an array type. Contains the null value is this is not an array type.
DTD_IDENTIFIER	VARCHAR(128) Nullable	A unique internal identifier for the result of the function.
ROUTINE_BODY	VARCHAR(8)	The type of the routine body: EXTERNAL This is an external routine. SQL This is an SQL routine.
ROUTINE_DEFINITION	DBCLOB(2M) CCSID 13488 Nullable	If this is an SQL routine, this column contains the SQL routine body. If this is an obfuscated routine, the text starts with the WRAPPED keyword and is followed by the encoded form of the statement text. Contains the null value if this is not an SQL routine or if the routine body cannot be contained in this column without truncation.
EXTERNAL_NAME	VARCHAR(279) Nullable	If this is an external routine, this column identifies the external program name. For REXX, the external program name is schema-name/source-file-name(member-name). For ILE service programs, the external program name is schema-name/service-program-name(entry-point-name). For Java™ programs, the external program name is an optional jar-id followed by a fully-qualified-class-name!method-name or fully-qualified-class-name.method-name. For all other languages, the external program name is schema-name/program-name. Contains the null value if this is a system-generated function or a function sourced on a built-in function.
EXTERNAL_LANGUAGE	VARCHAR(8) Nullable	If this is an external routine, this column identifies the external program name. C The external program is written in C. C++ The external program is written in C++. CL The external program is written in CL. COBOL The external program is written in COBOL. COBOLLE The external program is written in ILE COBOL. FORTRAN The external program is written in FORTRAN. JAVA The external program is written in JAVA. PLI The external program is written in PL/I. REXX The external program is a REXX procedure. RPG The external program is written in RPG. RPGLE The external program is written in ILE RPG. Contains the null value if this is not an external routine.
PARAMETER_STYLE	VARCHAR(18) Nullable	If this is an external routine, this column identifies the parameter style (calling convention). DB2GENERAL This is the DB2GENERAL calling convention. DB2SQL This is the DB2SQL calling convention. GENERAL This is the GENERAL calling convention. JAVA This is the JAVA calling convention. GENERAL WITH NULLS This is the GENERAL WITH NULLS calling convention. SQL This is the SQL standard calling convention. Contains the null value if this is not an external routine.
IS_DETERMINISTIC	VARCHAR(3)	This column identifies whether the routine is deterministic. That is, whether a call to the routine with the same arguments will always return the same result. NO The routine is not deterministic. YES The routine is deterministic.
SQL_DATA_ACCESS	VARCHAR(17)	This column identifies whether a routine contains SQL and whether it reads or modifies data. NO SQL The routine does not contain any SQL statements. CONTAINS SQL The routine contains SQL statements. READS SQL DATA The routine possibly reads data from a table or view. MODIFIES SQL DATA The routine possibly modifies data in a table or view or issues SQL DDL statements.
IS_NULL_CALL	VARCHAR(3) Nullable	Identifies whether the function needs to be called if an input parameter is the null value. NO This function need not be called if an input parameter is the null value. If this is a scalar function, the result of the function is implicitly null if any of the operands are null. If this is a table function, the result of the function is an empty table if any of the operands are the null value. YES This function must be called even if an input operand is null. Contains the null value if this is not a function.
SQL_PATH	VARCHAR(3483) Nullable	If this is an SQL routine, this column identifies the path. Contains the null value if this is not an SQL routine.
SCHEMA_LEVEL_ROUTINE	VARCHAR(3)	Reserved. Contains 'YES'.
MAX_DYNAMIC_RESULT_SETS	SMALLINT	Identifies the maximum number of result sets returned. 0 indicates that there are no result sets.
IS_USER_DEFINED_CAST	VARCHAR(3) Nullable	Identifies whether the this function is a cast function created when a distinct type was created. NO This function is not a cast function. YES This function is a cast function. Contains the null value if the routine is not a function.
IS_IMPLICITLY_INVOCABLE	VARCHAR(3) Nullable	Identifies whether the this function is a cast function created when a distinct type was created and can be implicitly invoked. NO This function is not a cast function. YES This function is a cast function and can be implicitly invoked. Contains the null value if the routine is not a function.
SECURITY_TYPE	VARCHAR(22) Nullable	Reserved. Contains 'IMPLEMENTATION DEFINED' if this is an external routine. Contains the null value if the routine is not an external routine.
TO_SQL_SPECIFIC_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
TO_SQL_SPECIFIC_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
TO_SQL_SPECIFIC_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
AS_LOCATOR	VARCHAR(3) Nullable	Indicates whether the result was specified as a locator. NO The parameter was not specified as a locator. YES The parameter was specified as a locator. Contains the null value if this is not a scalar function.
CREATED	TIMESTAMP	Identifies the timestamp when the routine was created.
LAST_ALTERED	TIMESTAMP Nullable	Timestamp when routine was last altered. Contains null if the routine has never been altered.
NEW_SAVEPOINT_LEVEL	VARCHAR(3) Nullable	Indicates whether the routine starts a new savepoint level. NO A new savepoint level is not started when the procedure is called. YES A new savepoint level is started when the procedure is called. Contains the null value if this is not a function.
IS_UDT_DEPENDENT	VARCHAR(3)	Indicates whether the routine is dependent on a UDT. NO The routine is not dependent on a UDT. YES The routine is dependent on a UDT.
RESULT_CAST_FROM_DATA_TYPE	VARCHAR(128) Nullable	Type of the parameter: BIGINT Big number INTEGER Large number SMALLINT Small number DECIMAL Packed decimal NUMERIC Zoned decimal DOUBLE PRECISION Floating point; DOUBLE PRECISION REAL Floating point; REAL DECFLOAT Decimal floating-point CHARACTER Fixed-length character string CHARACTER VARYING Varying-length character string CHARACTER LARGE OBJECT Character large object string GRAPHIC Fixed-length graphic string GRAPHIC VARYING Varying-length graphic string DOUBLE-BYTE CHARACTER LARGE OBJECT Double-byte character large object string NATIONAL CHARACTER National character NATIONAL CHARACTER VARYING Varying-length national character NATIONAL CHARACTER LARGE OBJECT National character large object BINARY Fixed-length binary string BINARY VARYING Varying-length binary string BINARY LARGE OBJECT Binary large object string DATE Date TIME Time TIMESTAMP Timestamp DATALINK Datalink ROWID Row ID XML XML USER-DEFINED Distinct Type
RESULT_CAST_AS_LOCATOR	VARCHAR(3) Nullable	Indicates whether the result is cast from a locator. NO The result is not cast from a locator. YES The result is cast from a locator.
RESULT_CAST_CHAR_MAX_LENGTH	INTEGER Nullable	Maximum length of the string for binary, character, and graphic string and XML data types. Contains the null value if the parameter is not a string.
RESULT_CAST_CHAR_OCTET_LENGTH	INTEGER Nullable	Number of bytes for binary, character, and graphic string and XML data types. Contains the null value if the parameter is not a string.
RESULT_CAST_CHAR_SET_CATALOG	VARCHAR(128) Nullable	Relational database name Contains the null value if the column is not a string.
RESULT_CAST_CHAR_SET_SCHEMA	VARCHAR(128) Nullable	The schema name of the character set. Contains 'SYSIBM'. Contains the null value if the column is not a string.
RESULT_CAST_CHAR_SET_NAME	VARCHAR(128) Nullable	The character set name. Contains the null value if the column is not a string.
RESULT_CAST_COLLATION_CATALOG	VARCHAR(128) Nullable	Relational database name Contains the null value if the column is not a string.
RESULT_CAST_COLLATION_SCHEMA	VARCHAR(128) Nullable	The schema of the collation. SYSIBM is returned. Contains the null value if the column is not a string.
RESULT_CAST_COLLATION_NAME	VARCHAR(128) Nullable	The collation name. IBMBINARY is returned. Contains the null value if the column is not a string.
RESULT_CAST_NUMERIC_PRECISION	INTEGER Nullable	The precision of all numeric parameters. Note: This column supplies the precision of all numeric data types, including single-and double-precision floating point and decimal floating-point. The NUMERIC_PRECISION_RADIX column indicates if the value in this column is in binary or decimal digits. Contains the null value if the parameter is not numeric.
RESULT_CAST_NUMERIC_RADIX	INTEGER Nullable	Indicates if the precision specified in column NUMERIC_PRECISION is specified as a number of binary or decimal digits: 2 Binary; floating-point precision is specified in binary digits. 10 Decimal; all other numeric types are specified in decimal digits. Contains the null value if the parameter is not numeric.
RESULT_CAST_NUMERIC_SCALE	INTEGER Nullable	Scale of numeric data. Contains the null value if not decimal, numeric, or binary parameter.
RESULT_CAST_DATETIME_PRECISION	INTEGER Nullable	The fractional part of a date, time, or timestamp. 0 For DATE and TIME data types 6 For TIMESTAMP data types (number of microseconds). Contains the null value if the parameter is not a date, time, or timestamp.
RESULT_CAST_INTERVAL_TYPE	VARCHAR(128) Nullable	Reserved. Contains the null value.
RESULT_CAST_INTERVAL_PRECISION	INTEGER Nullable	Reserved. Contains the null value.
RESULT_CAST_TYPE_UDT_CATALOG	VARCHAR(128) Nullable	The relational database name if this is a distinct type. Contains the null value if this is not a distinct type.
RESULT_CAST_TYPE_UDT_SCHEMA	VARCHAR(128) Nullable	The name of the schema if this is a distinct type. Contains the null value if this is not a distinct type.
RESULT_CAST_TYPE_UDT_NAME	VARCHAR(128) Nullable	The name of the distinct type Contains the null value if this is not a distinct type.
RESULT_CAST_SCOPE_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
RESULT_CAST_SCOPE_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
RESULT_CAST_SCOPE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
RESULT_CAST_MAX_CARDINALITY	INTEGER Nullable	Reserved. Contains the null value.
RESULT_CAST_DTD_IDENTIFIER	VARCHAR(128) Nullable	A unique internal identifier for the parameter.