SYSPROCS

The SYSPROCS view contains one row for each procedure created by the CREATE PROCEDURE statement. The following table describes the columns in the SYSPROCS view:

Table 127. SYSPROCS view
Column Name System Column Name Data Type Description
SPECIFIC_SCHEMA SPECSCHEMA VARCHAR(128) Schema name of the routine (procedure) instance.
SPECIFIC_NAME SPECNAME VARCHAR(128) Specific name of the routine instance.
ROUTINE_SCHEMA PROCSCHEMA VARCHAR(128) Name of the SQL schema (schema) that contains the routine.
ROUTINE_NAME PROCNAME VARCHAR(128) Name 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
This is an external routine.
SQL
This is an SQL routine.
EXTERNAL_NAME EXTNAME VARCHAR(279)
Nullable
This column identifies the external program name.
  • For ILE service programs, the external program name is schema-name/service-program-name(entry-point-name).
  • For REXX, the external program name is schema-name/source-file-name(member-name).
  • For Java(TM) 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.
EXTERNAL_LANGUAGE 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 PARM_STYLE VARCHAR(7)
Nullable
If this is an external routine, this column identifies the parameter style (calling convention).
DB2GNRL
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.
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 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.
NO
The routine is not deterministic.
YES
The routine is deterministic.
SQL_DATA_ACCESS DATAACCESS VARCHAR(8) This column identifies whether a routine contains SQL and whether it reads or modifies data.
NONE
The routine does not contain any SQL statements.
CONTAINS
The routine contains SQL statements.
READS
The routine possibly reads data from a table or view.
MODIFIES
The routine possibly modifies data in a table or view or issues SQL DDL statements.
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.
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.
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 VARCHAR(24000)
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.

DBINFO DBINFO VARCHAR(3)
Nullable
Identifies whether information about the database is passed to the procedure.
NO
No database information is passed to the procedure.
YES
Information about the database is passed to the procedure.
COMMIT_ON_RETURN CMTONRET VARCHAR(3)
Nullable
This column identifies whether the procedure commits on a successful return from the procedure.
NO
A commit is not performed on successful return from the procedure.
YES
A commit is performed on successful return from the procedure.
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.
NO
A new savepoint level is not started.
YES
A new savepoint level is started.