DB2 Stored Procedures - Setup

DB2 Stored Procedures
Stored procedures are subroutines that are registered in the DB2 catalog, and when called, execute in a special stored procedure address space within DB2. Stored procedures are not considered batch or online programs. They can be called from any program with access to DB2. This means that anything from mainframe programs to Java programs on a UNIX server can call it , as long as they can connect to the DB2 system on the mainframe. With stored procedures, we can allow external applications to access our database without giving them direct access, and we can control the access that they have. It also insulates the external applications from database structure changes impacting their applications.

Given below the steps involved in creating WLM store procedure and running it .

1. Create the Stored Procedure in DB2 using below DDL sample.

CREATE PROCEDURE SCHEMA.XXXXXX
( IN PARM1 CHARACTER(10)
,IN PARM2 INTEGER
)
RESULT SET 0
EXTERNAL NAME 'XXXXXX'
LANGUAGE COBOL
PARAMETER STYLE GENERAL WITH NULLS
NOT DETERMINISTIC
NULL CALL
FENCED
MODIFIES SQL DATA
NO DBINFO
COLLID PKGCOLLID
WLM ENVIRONMENT XXXXSPN1
ASUTIME NO LIMIT
STAY RESIDENT YES
PROGRAM TYPE SUB
SECURITY DB2
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS
;

2. GRANT EXEC to PUBLIC ( or preferred id’s) on the Stored Procedure

3. Perform dummy package bind and GRANT EXEC to Package

4. Compile the calling program using “INCLUDE SYSLIB(DSNELI)” in SYSIN of Link edit step. ( Note : DSNELI is DB2 TSO )

5. Compile the external stored program using “INCLUDE SYSLIB(DSNRLI)” in SYSIN of Link edit step. ( Note : DSNRLI is DB2 CAF )

The main and some subprograms are linked with the regular DSNELI attachment and are started from TSO DSN. Some dynamically called subprograms links with the DSNALI (CAF) or DSNRLI attachment facility. Include DSNALI (for DB2-SPAS) or DSNRLI (for WLM-SPAS).

6. WLM address space JCL which run at back ground should have your store procedure loadlib dataset concatenated ( One time activity )

7. You can run the store procedure by running the store procedure calling program.

8. WLM Refresh can be done by using the store procedure WLM_REFRESH using the below SQL
EXEC SQL CALL SYSPROC.WLM_REFRESH(:WLMENV, :SSID, :MSGTEXT, :RC);


( Note : Only required when WLM need to be refreshed to pickup the latest store procedure load module)