Stored procedures in DB2 follow two steps process Viz: writing and registering to the DB2. Generally, developers write stored procedures, and the sysadmin registers them to DB2.
What is the process of register?
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).
DB2 stored procedure example
Check out the sample stored procedure written in COBOL – How to write a COBOL Program in DB2 stored procedure
How a Stored procedure registers prior to DB2 v6
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.
Current process to register stored procedure in the database
This method of registering stored procedures changes 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.
Also, 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.
Related
Srinimf 