DB2 Universal Database for iSeries SQL Reference

ROUTINES

The ROUTINES view contains one row for each routine. The following table describes the columns in the view:

Table 155. 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

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

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

USER-DEFINED
Distinct 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 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 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. 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.

Contains the null value if this is not a scalar function or the result is not a distinct type.

TYPE_UDT_SCHEMA VARCHAR(128)
Nullable
The name of the schema if the result of the function is a distinct type.

Contains the null value if this is not a scalar function or the result is not a distinct type.

TYPE_UDT_NAME VARCHAR(128)
Nullable
The name of the distinct type if the result of the function is a distinct type.

Contains the null value if this is not a scalar function or the result is not a distinct 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 INTEGER
Nullable
Reserved. Contains the null value.
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
Nullable
If this is an SQL routine, this column contains the SQL routine body.

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 Reserved. Contains 'CREATED'.
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

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

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

USER-DEFINED
Distinct Type
RESULT_CAST_AS_LOCATOR VARCHAR(3) 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 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 data types.

Contains the null value if the parameter is not a string.

RESULT_CAST_SET_CATALOG VARCHAR(128)
Nullable
Relational database name

Contains the null value if the column is not a string.

RESULT_CAST_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_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. 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_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_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_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.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]