The SYSROUTINES table contains one row for each procedure created by the CREATE PROCEDURE statement and each function created by the CREATE FUNCTION statement. The following table describes the columns in the SYSROUTINES table:
Column Name | System Column Name | Data Type | Description |
---|---|---|---|
SPECIFIC_SCHEMA | SPECSCHEMA | VARCHAR(128) | Schema name of the routine instance. |
SPECIFIC_NAME | SPECNAME | VARCHAR(128) | Specific name of the routine instance. |
ROUTINE_SCHEMA | RTNSCHEMA | VARCHAR(128) | Name of the SQL schema (schema) that contains the routine. |
ROUTINE_NAME | RTNNAME | VARCHAR(128) | Name of the routine. |
ROUTINE_TYPE | RTNTYPE | VARCHAR(9) | Type of the routine.
|
ROUTINE_CREATED | RTNCREATE | TIMESTAMP | Identifies the timestamp when the routine was created. |
ROUTINE_DEFINER | DEFINER | VARCHAR(128) | Name of the user that defined the routine. |
ROUTINE_BODY | BODY | VARCHAR(8) | The type of the routine body:
|
EXTERNAL_NAME | EXTNAME | VARCHAR(279)
Nullable |
This column identifies the external program
name.
Contains the null value if this is a system-generated function. |
EXTERNAL_LANGUAGE | LANGUAGE | VARCHAR(8)
Nullable |
If this is an external routine, this column
identifies the external program name.
Contains the null value if this is not an external routine. |
PARAMETER_STYLE | PARM_STYLE | VARCHAR(7)
Nullable |
If this is an external routine, this column
identifies the parameter style (calling convention).
Contains the null value if this is not an external routine. |
IS_DETERMINISTIC | DETERMINE | 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.
|
SQL_DATA_ACCESS | DATAACCESS | VARCHAR(8)
Nullable |
This column identifies whether a routine
contains SQL and whether it reads or modifies data.
|
SQL_PATH | 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. |
PARM_SIGNATURE | SIGNATURE | VARCHAR(2048) | This column identifies the routine signature. |
NUMBER_OF_RESULTS | NUMRESULTS | SMALLINT | Identifies the number of results. |
MAX_DYNAMIC_RESULT_SETS | RESULTS | SMALLINT | Identifies the maximum number of result sets returned. 0 indicates that there are no result sets. |
IN_PARMS | IN_PARMS | SMALLINT | Identifies the number of input parameters. 0 indicates that there are no input parameters. |
OUT_PARMS | OUT_PARMS | SMALLINT | Identifies the number of output parameters. 0 indicates that there are no output parameters. |
INOUT_PARMS | INOUT_PARM | SMALLINT | Identifies the number of input/output parameters. 0 indicates that there are no input/output parameters. |
PARSE_TREE | PARSE_TREE | VARCHAR(1024) FOR BIT DATA | If this is a routine, this column identifies the parse tree of the CREATE FUNCTION or CREATE PROCEDURE statement. It is only used internally. |
PARM_ARRAY | PARM_ARRAY | BLOB(320000) | If this is an external routine, this column identifies the parameter array built from the CREATE FUNCTION or CREATE PROCEDURE statement. It is only used internally. |
LONG_COMMENT | REMARKS | VARCHAR(2000)
Nullable |
A character string supplied with the COMMENT
statement.
Contains the null value if there is no long comment. |
ROUTINE_DEFINITION | ROUTINEDEF | DBCLOB(2M) CCSID 13488
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. |
FUNCTION_ORIGIN | ORIGIN | CHAR(1) | Identifies the type of function. If this
is a procedure, this column contains a blank.
|
FUNCTION_TYPE | TYPE | CHAR(1) | Identifies the form of the function. If this
is a procedure, this column contains a blank.
|
EXTERNAL_ACTION | EXTACTION | CHAR(1)
Nullable |
Identifies whether the invocation of the
function has external effects.
Contains the null value if the routine is a procedure. |
IS_NULL_CALL | NULL_CALL | VARCHAR(3)
Nullable |
Identifies whether the function needs to
be called if an input parameter is the null value.
Contains the null value if the routine is a procedure. |
SCRATCH_PAD | SCRATCHPAD | INTEGER
Nullable |
Identifies whether the address of a static
memory area (scratch pad) is passed to the function.
Contains the null value if the routine is a procedure. |
FINAL_CALL | FINAL_CALL | VARCHAR(3)
Nullable |
Indicates whether a final call to the function
should be made to allow the function to clean up its work areas (scratch pads).
Contains the null value if the routine is a procedure. |
PARALLELIZABLE | PARALLEL | VARCHAR(3)
Nullable |
Identifies whether the function can be run
in parallel.
Contains the null value if the routine is a procedure. |
DBINFO | DBINFO | VARCHAR(3)
Nullable |
Identifies whether information about the
database is passed to the routine.
Contains the null value if the routine is a procedure. |
SOURCE_SPECIFIC_SCHEMA | SRCSCHEMA | VARCHAR(128)
Nullable |
If this is sourced function and the source
is user-defined, this column contains the name of the source schema. If this
is a sourced function and the source is built-in, this column contains 'QSYS2'.
Contains the null value if the routine is not a sourced function. |
SOURCE_SPECIFIC_NAME | SRCNAME | VARCHAR(128)
Nullable |
If this is sourced function and the source
is user-defined, this column contains the specific name of the source function
name.
Contains the null value if the routine is not a sourced function. |
IS_USER_ DEFINED_CAST | CAST_FUNC | VARCHAR(3)
Nullable |
Identifies whether the this function is a
cast function created when a distinct type was created.
Contains the null value if the routine is a procedure. |
CARDINALITY | CARD | BIGINT
Nullable |
Specifies the cardinality for a table function.
Contains the null value if the function is not a table function or if cardinality was not specified. |
FENCED | FENCED | VARCHAR(3)
Nullable |
Identifies whether a function is fenced.
Contains the null value if the routine is a procedure. |
COMMIT_ON_RETURN | CMTONRET | VARCHAR(3)
Nullable |
This column identifies whether the procedure
commits on a successful return from the procedure.
Contains the null value if the routine is a function. |
IASP_NUMBER | IASPNUMBER | SMALLINT | Specifies the independent auxiliary storage pool (IASP) number. |
NEW_SAVEPOINT_LEVEL | NEWSAVEPTL | VARCHAR(3)
Nullable |
This column identifies whether the routine
starts a new savepoint level.
Contains the null value if the routine is a function. |
LAST_ALTERED | ALTEREDTS | TIMESTAMP
Nullable |
Timestamp when routine was last altered. Contains null if the routine has never been altered. |
DEBUG_MODE | DEBUG_MODE | CHAR(1) | Identifies whether the routine is debuggable.
|
DEBUG_DATA | DEBUG_DATA |
CLOB(1048576)
Nullable |
Reserved. Contains the null value. |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.