SQL Reference

SYSCAT.PROCEDURES

Contains a row for each stored procedure that is created.

Table 78. SYSCAT.PROCEDURES Catalog View

Column Name Data Type Nullable Description
PROCSCHEMA VARCHAR(128)
Qualified procedure name.
PROCNAME VARCHAR(128)
SPECIFICNAME VARCHAR(18)
The name of the procedure instance (may be system generated).
PROCEDURE_ID INTEGER
Internal ID of stored procedure.
DEFINER VARCHAR(128)
Authorization of the procedure definer.
PARM_COUNT SMALLINT
Number of procedure parameters.
PARM_SIGNATURE VARCHAR(180) FOR BIT DATA
Concatenation of up to 90 parameter types, in internal format. Zero length if procedure takes no parameters.
ORIGIN CHAR(1)
Always 'E' = User defined, external
CREATE_TIME TIMESTAMP
Timestamp of procedure registration.
DETERMINISTIC CHAR(1)

Y = Results are deterministic.
N = Results are not deterministic.

FENCED CHAR(1)

Y = Fenced
N = Not Fenced

NULLCALL CHAR(1)
Always Y = NULLCALL
LANGUAGE CHAR(8)
Implementation language of procedure body. Possible values are:

C
COBOL
JAVA
SQL

IMPLEMENTATION VARCHAR(254) Yes Identifies the path/module/function (LANGUAGE = C or COBOL) or method (LANGUAGE = JAVA) that implements the procedure.
CLASS VARCHAR(128) Yes If LANGUAGE = JAVA then it identifies the class that implements this procedure. Null otherwise.
JAR_ID VARCHAR(128) Yes If LANGUAGE = JAVA then identifies the jar file that implements this procedure. Null otherwise.
PARM_STYLE CHAR(8)

DB2DARI = Language is C
DB2GENRL = Language is Java
DB2SQL = Language is C or COBOL
JAVA = Language is Java or SQL
GENERAL = Language is C or COBOL
GNLRNULL = Language is C or COBOL

CONTAINS_SQL CHAR(1)
Indicates whether a procedure contains SQL.

C = CONTAINS SQL: only SQL that does not read or modify SQL data is allowed.
M = MODIFY SQL DATA: all SQL allowed in procedures is allowed
N = NO SQL: SQL is not allowed
R = READS SQL DATA: only SQL that reads SQL data is allowed

DBINFO CHAR(1)
Indicates whether a DBINFO parameter is passed to the procedure

N = DBINFO is not passed
Y = DBINFO is passed

PROGRAM_TYPE CHAR(1)
Indicates how procedure is invoked.

M = Main
S = Subroutine

RESULT_SETS SMALLINT
Estimated upper limit of returned result sets.
VALID CHAR(1)

blank = not an SQL procedure
Y = SQL procedure is valid
N = SQL procedure is invalid
X = SQL procedure is inoperative because some function instance it requires has been dropped. The SQL procedure must be explicitly dropped and recreated.

TEXT_BODY_OFFSET INTEGER
If this is an SQL procedure, this column contains the offset to the start of the SQL procedure body in the full text of the CREATE PROCEDURE statement. If this is an external procedure, the value is 0.
TEXT CLOB (1M) Yes If this is an SQL procedure, this column contains the full text of the CREATE PROCEDURE statement, exactly as typed. It is null if the full text is longer than 1M, or if this is an external procedure.
REMARKS VARCHAR(254) Yes User supplied comment, or null.

Column Name	Data Type	Nullable	Description
PROCSCHEMA	VARCHAR(128)		Qualified procedure name.
PROCNAME	VARCHAR(128)
SPECIFICNAME	VARCHAR(18)		The name of the procedure instance (may be system generated).
PROCEDURE_ID	INTEGER		Internal ID of stored procedure.
DEFINER	VARCHAR(128)		Authorization of the procedure definer.
PARM_COUNT	SMALLINT		Number of procedure parameters.
PARM_SIGNATURE	VARCHAR(180) FOR BIT DATA		Concatenation of up to 90 parameter types, in internal format. Zero length if procedure takes no parameters.
ORIGIN	CHAR(1)		Always 'E' = User defined, external
CREATE_TIME	TIMESTAMP		Timestamp of procedure registration.
DETERMINISTIC	CHAR(1)		Y = Results are deterministic. N = Results are not deterministic.
FENCED	CHAR(1)		Y = Fenced N = Not Fenced
NULLCALL	CHAR(1)		Always Y = NULLCALL
LANGUAGE	CHAR(8)		Implementation language of procedure body. Possible values are: C COBOL JAVA SQL
IMPLEMENTATION	VARCHAR(254)	Yes	Identifies the path/module/function (LANGUAGE = C or COBOL) or method (LANGUAGE = JAVA) that implements the procedure.
CLASS	VARCHAR(128)	Yes	If LANGUAGE = JAVA then it identifies the class that implements this procedure. Null otherwise.
JAR_ID	VARCHAR(128)	Yes	If LANGUAGE = JAVA then identifies the jar file that implements this procedure. Null otherwise.
PARM_STYLE	CHAR(8)		DB2DARI = Language is C DB2GENRL = Language is Java DB2SQL = Language is C or COBOL JAVA = Language is Java or SQL GENERAL = Language is C or COBOL GNLRNULL = Language is C or COBOL
CONTAINS_SQL	CHAR(1)		Indicates whether a procedure contains SQL. C = CONTAINS SQL: only SQL that does not read or modify SQL data is allowed. M = MODIFY SQL DATA: all SQL allowed in procedures is allowed N = NO SQL: SQL is not allowed R = READS SQL DATA: only SQL that reads SQL data is allowed
DBINFO	CHAR(1)		Indicates whether a DBINFO parameter is passed to the procedure N = DBINFO is not passed Y = DBINFO is passed
PROGRAM_TYPE	CHAR(1)		Indicates how procedure is invoked. M = Main S = Subroutine
RESULT_SETS	SMALLINT		Estimated upper limit of returned result sets.
VALID	CHAR(1)		blank = not an SQL procedure Y = SQL procedure is valid N = SQL procedure is invalid X = SQL procedure is inoperative because some function instance it requires has been dropped. The SQL procedure must be explicitly dropped and recreated.
TEXT_BODY_OFFSET	INTEGER		If this is an SQL procedure, this column contains the offset to the start of the SQL procedure body in the full text of the CREATE PROCEDURE statement. If this is an external procedure, the value is 0.
TEXT	CLOB (1M)	Yes	If this is an SQL procedure, this column contains the full text of the CREATE PROCEDURE statement, exactly as typed. It is null if the full text is longer than 1M, or if this is an external procedure.
REMARKS	VARCHAR(254)	Yes	User supplied comment, or null.

[ Top of Page | Previous Page | Next Page ]