SQL Reference

SYSCAT.PROCEDURES

Contains a row for each stored procedure that is created.

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

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
GENERAL = Language is C or COBOL
GNLRNULL = Language is C or COBOL

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

C = Contains simple SQL statements only with no subqueries
M = Contains SQL statements that modify data
N = Does not contain SQL statements
R = Contains read-only SQL statements

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)
Reserved for future use.
TEXT_BODY_OFFSET INTEGER
Reserved for future use.
TEXT CLOB (1M) Yes Reserved for future use.
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
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 GENERAL = Language is C or COBOL GNLRNULL = Language is C or COBOL
CONTAINS_SQL	CHAR(1)		Indicates whether a procedure contains SQL. C = Contains simple SQL statements only with no subqueries M = Contains SQL statements that modify data N = Does not contain SQL statements R = Contains read-only SQL statements
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)		Reserved for future use.
TEXT_BODY_OFFSET	INTEGER		Reserved for future use.
TEXT	CLOB (1M)	Yes	Reserved for future use.
REMARKS	VARCHAR(254)	Yes	User supplied comment, or null.

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

[ DB2 List of Books | Search the DB2 Books ]