Db2-cobol Stored Procedure 6k5143

Stored Procedures •

A stored procedure is a program which runs inside a DB2 subsystem and can update DB2 objects. It can be in any language, but for our purposes will be written in Cobol. It can be called from application programs which connect with DB2 including MVS batch, CICS and web applications. Pre-defined. parameters are ed to and from the stored procedure.

•

Stored procedures must be defined to DB2 by means of a "CREATE PROCEDURE" DDL statement. This statement includes the definitions of the parameters ed to and from the stored procedure. Figure 1:

CREATE PROCEDURE SFIIDSVR ( IN CALL_TYPE ,IN SYSTEM_NAME ,IN ID ,IN TIME ,IN SYSTEM_ID ,IN INDIVIDUAL_ID ,IN REFERRED_ID ,IN STATUS_IN ,IN ACCESS_FLAG_IN ,IN CLOAKED_FLAG_IN ,IN ISA_DATA ,IN QUALS_COUNT_IN ,IN QUALIFIERS_IN ,OUT STATUS_OUT ,OUT RETURN_CODE ,OUT RETURNEE_COUNT ,OUT RETURNEE_DATA ) LANGUAGE COBOL DYNAMIC RESULT SET 0 EXTERNAL NAME SFIIDSVR PARAMETER STYLE DB2SQL COLLID SP2ECOLL STAY RESIDENT YES NO WLM ENVIRONMENT;

•

CHAR(08) CHAR(08) CHAR(08) TIMESTAMP CHAR(50) CHAR(09) CHAR(09) CHAR(09) CHAR(01) CHAR(01) CHAR(220) SMALLINT VARCHAR(1080) SMALLINT SMALLINT SMALLINT VARCHAR(30822)

Stored procedures are compiled and then linkedited into special loadlibs. There are some specific compile and link parameters required for stored procedures. The PPS production loadlib for stored procedures is A3257.PPS.PROD.SPLOAD. The DBRM are saved in

A3257.PPS.PROD.SPDBRM. In Endevor the stored procedure processing group is COBSP. •

The loadlib for stored procedures must be defined to DB2 in STEPLIB of the DB2xSPAS job (SPAS means Stored Procedure Address Space)

•

Stored procedures are bound as packages into specific stored procedure collection IDs, eg. SP2ACOLL.

•

After a stored procedure has been recompiled, it is necessary to stop and restart the procedure using the DB2 COMMANDS dialog (DB2.7). Occasionally after an abend it may be necessary to restart a stored procedure in the same way. Example: -STOP PROCEDURE(SFHRSDD.SFIIDSVR) -START PROCEDURE(SFHRSDD.SFIIDSVR)

Calling Stored Procedures • •

Any DB2 COBOL program can call a stored procedure which has been correctly defined, compiled and bound using an "EXEC SQL CALL" command. Other non-COBOL applications can call the stored procedure using the DB2 syntax for the application type. Figure 2: EXEC SQL CALL SFIIDSVR(:PRM-CALL-TYPE-IN ,:PRM-SYSTEM-NAME-IN ,:PRM-ID-IN ,:PRM-TIME-IN ,:PRM-SYSTEM-IID-IN ,:PRM-INDIVIDUAL-ID-IN ,:PRM-REFERRED-ID-IN ,:PRM-STATUS-IN ,:PRM-ACCESS-FLAG-IN ,:PRM-CLOAKED-FLAG-IN ,:PRM-ISA-DATA-IN ,:PRM-QUALS-COUNT-IN ,:PRM-QUALIFIERS-IN ,:PRM-STATUS-OUT ,:PRM-RETURN-CODE-OUT ,:PRM-RETURNEE-COUNT-OUT ,:PRM-RETURNEE-DATA-OUT) END-EXEC.

Programs Called By Stored Procedures •

A stored procedure may call another program which may not itself be a stored procedure using a regular call statement (not EXEC SQL CALL). All programs called by stored procedures must also reside in the stored procedure loadlib. These called programs are compiled using the COBSP processing group, with DBRM saved in the stored procedure DBRM library and linkedited to the stored procedure loadlib.

Tri-Use Programs •

In some cases, a program may be called by a stored procedure, but also called by a CICS or batch program. These are called tri-use programs. They are compiled and bound 3 times: once for batch, once for CICS and once as a stored procedure. In each case, the DBRM information is stored in a separate DBRMLIB, eg. A1555.AIS.PROD.DBRMLIB for batch, A1555.AIS.PROD.C ICSDBRM for CICS and A1555.AIS.PROD.SPDBRM for stored procedure. The Endevor processing type for tri-use programs is COB3.

•

Tri-use programs must also be bound 3 times pointing to the appropriate DBRM library. For batch, the called program is bound into the plan for the calling program(s). For CICS the called program is bound into a package with a CICS collection ID, eg. PP2ACOLL. For stored procedure use, the called program is bound into a package with a stored procedure collection ID, eg. SP2ACOLL.

Debugging Stored Procedures •

Cobol Displays can be added to stored procedures. Adding the following line to the very beginning of a program will control the output DD of the displays: PROCESS OUTDD(xxxxxxxx). It is recommended that xxxxxxxx be the name of the program. Then the displays can be seen in the xxxxxxxx output DD of the DB2xSPAS job.

•

Abends: When a stored procedure abends, there may be information about the abend in the SYSOUT output DD of the DB2xSPAS job. However, if a CICS program calls a stored procedure and the EXEC SQL CALL statement gets an error SQLCODE, that information will appear in the output of the CICS job.

•

When SFIIDSVR abends, the EXEC SQL CALL statement from UCIID100 will usually get a -430 SQLCODE. Frequently, after that, ANY call to SFIIDSVR will result in a -471 SQLCODE which says that the stored procedure is stopped. This means that a -START PROCEDURE command must be issued for the stored procedure before it can be used again.

Triggers •

Triggers can be defined for DB2 tables to perform editing or maintenance whenever a record is added, modified or deleted. This allows consistent edits or maintenance to be performed regardless of how the data is updated.

•

Triggers are defined using a "CREATE TRIGGER" DDL statement. A trigger can contain statements to act on a table, or it can call a stored procedure to do the operations.

•

In the ID system, there are 3 triggers defined for the SF0INM table, one for inserts, one for updates and one for deletes. Below is the DDL for defining the insert trigger. Figure 3: CREATE TRIGGER SF0INMIN AFTER INSERT ON SF0INM REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE SF0INM SET INM_FULL_NAME = RTRIM(UPPER(INM_SURNAME)) || '#' || RTRIM(UPPER(INM_GIVEN_NAME)) || '#' || RTRIM(UPPER(INM_MIDDLE_NAME)) WHERE INM_INDIVIDUAL_ID = N.INM_INDIVIDUAL_ID AND INM_SOURCE = N.INM_SOURCE ; CALL SFHRSPA.SFINMTR (N.INM_INDIVIDUAL_ID) ; END$ • • •

All operations take place after the "BEGIN ATOMIC" statement The first action is to modify the INM_FULL_NAME field of the SF0INM table with a concatenation of the 3 name components. Then the stored procedure SFINMTR is called to perform additional operations.

Specific Stored Procedures in use in the ID System •

There are currently 2 stored procedures defined for the ID system: o SFIIDSVR o SFINMTR

•

In addition, there are several programs which are called by SFIIDSVR. o SFIDIDBU o SFIDICFU (tri-use) o SFIDIDQU o SFIDINMU o SFIDISAU o SFIDIXBU o SFIDVRED o SFIIDCHK o SFIIDMTC o SFIIDNUM o SFIIDUID o SFTBLEDT (tri-use) Of these, SFIDICFU and SFTBLEDT are tri-use, that is they can be called from either a stored procedure or a regular CICS or batch program.

•

SFINMTR is called from the triggers for the SF0INM table. Its purpose is to update the IDB_MIN_INM_SOURCE in the SF0IDB table with the minimum value for the source field in all SF0INM records for the ID. o It is defined to DB2 in DDLLIB member SPINM00C.

•

SFIIDSVR is a utility for updating ID system tables. It is currently called from CICS programs and also from web applications. o It is defined to DB2 in DDLLIB member SPIDS00C - see Figure 1 above. o The input and output parameters are laid out in copylib member SFPRMIDS. See Figure 4 below. o Copylib member SFLNKIDS is used as a work area for formatting the parameters. o SFIIDSVR is called by UCIID100 and UCIID200 (see Figure 2 above) which are CICS programs called from both the PPS and ID systems. These two programs format the parameters and interpret the results for the programs which called them.

Figure 4: Copylib member SFPRMIDS 01 01 01 01 01 01 01 01 01 01

01 01 01 01 01

PRM-SYSTEM-IID -IN PRM-INDIVIDUAL-ID-IN PRM-REFERRED-ID-IN PRM-STATUS-IN PRM-ACCESS-FLAG-IN PRM-CLOAKED-FLAG-IN PRM-ISA-DATA-IN PRM-QUALS-COUNT-IN PRM-QUALIFIERS-IN FILLER REDEFINES PRM-QUALIFIERS-IN. 05 PRM-QUALIFIERS-IN-LEN 05 PRM-QUALIFIERS-IN-TXT PRM-STA TUS-OUT PRM-RETURN-CODE-OUT PRM-RETURNEE-COUNT-OUT PRM-RETURNEE-DATA-OUT FILLER REDEFINES PRM-RETURNEE-DATA-OUT. 05 PRM-RETURNEE-DATA-OUT-LEN 05 PRM-RETURNEE-DATA-OUT-TXT. 10 PRM-RETURNEE-OUT OCCURS 20. 15 PRM-RETURNEE-FIELDS 15 PRM-ISA-DATA 15 PRM-RETURNEE-QUALS-COUNT-OUT 15 PRM-RETURNEE-QUALIFIERS-OUT

PIC X(50). PIC X(09). PIC X(09). PIC X(09). PIC X(01). PIC X(01). PIC X(220). PIC S9(04) COMP. PIC X(1082). PIC 9(4) BINARY. PIC X(1080). PIC S9(04) COMP. PIC S9(04) COMP. PIC S9(04) COMP. PIC X(30822). PIC 9(4) BINARY.

PIC X(79). PIC X(220). PIC 9(02). PIC X(1240).

Figure 5 SFIIDSVR CALLING PATH

PPAPEUAB PPAPEUCA PPWEEID SFAPCLFH SFAPIDFP SFAPIDKS SFBLDAUT SFURFAB0 SFURFCA0 SFURFKS0 SFURFUP1 SFWHSDS SFWHSUP SFWIASN SFWIDBR SFWIDIS SFWRUID UCAPIDFE UCIIDASN UCIIDMNT UCWIDAS UCWIDBS UCWIDDS UCWIDUP

UCAPIDFE UCAPIDKS UCIIDASN UCIIDMNT

UCIID200

UCIID100

SFIIDSVR

SFIIDUID

SFIIDMTC

SFIIDNUM

SFIIDCHK

SFIDIDBU

SFIDINMU

SFIDIDQU

SFIDIXBU

SFIDISAU

SFIDVRED