Stored Procedure Registration

Prior to DB2 Version 6, after coding a stored procedure, you must register information about that stored procedures in the DB2 system catalog. This process is in sharp contrast to the manner in which other database objects are recorded in the system catalog. Typically, when an object is created, DB2 automatically stores the metadata description of that object in the appropriate DB2 catalog tables. For example, to create a new table, the CREATE TABLE statement is issued and DB2 automatically records the information in multiple system catalog tables ( SYSIBM.SYSTABLES , SYSIBM.SYSCOLUMNS , SYSIBM.SYSTABLESPACE , and possibly SYSIBM.SYSFIELDS , SYSIBM.SYSCHECKS , SYSIBM.SYSCHECKDEP , SYSIBM.SYSRELS , and SYSIBM.SYSFOREIGNKEYS ). Because stored procedures were not created within DB2, nor were they created using DDL, the database administrator had to use SQL INSERT statements to populate the SYSIBM.SYSPROCEDURES system catalog table with the metadata for the stored procedure.

The following SQL provides an example of an INSERT to register a stored procedure:

 INSERT INTO SYSIBM.SYSPROCEDURES   (PROCEDURE, AUTHID, LUNAME, LOADMOD, LINKAGE,    COLLID, LANGUAGE, ASUTIME, STAYRESIDENT,    IBMREQD, RUNOPTS, PARMLIST, RESULT_SETS,    WLM_ENV, PGM_TYPE, EXTERNAL_SECURITY,    COMMIT_ON_RETURN)  VALUES   ('PROCNAME', ' ', ' ', 'LOADNAME', ' ',    'COLL0001', 'COBOL', 0, 'Y',    'N', ' ', 'NAME CHAR(20) INOUT', 1,    ' ', 'M', 'N', 'N'); 

This SQL statement registers a stored procedure written in COBOL and named PROCNAME with a load module named LOADNAME . It uses a package with a collection ID of COLL0001 . Any location can execute this procedure. The program stays resident and uses the DB2 SPAS (not Workload Manager), and no limit is set on the amount of time it can execute before being canceled . Furthermore, the stored procedure uses one input/output parameter, and the parameter cannot be null.

This method of registering stored procedures changed in DB2 V6. Instead of the INSERT statement, CREATE and ALTER statements are provided for registering stored procedures to the DB2 system catalog. Additionally, a new catalog table named SYSIBM.SYSROUTINES replaces SYSIBM.SYSPROCEDURES . This new table will store information on triggers, user -defined functions, and stored procedures. The metadata for all of these "routines" will be provided to the system catalog by means of DDL statements.

Many organizations have procedures for creating and updating stored procedures that include registration. These procedures will need to be modified to work with DB2 V6 and later releases.