Stored Procedures

 

DB2 CATALOG TABLES:

   SYSIBM.SYSROUTINES - 1 row / SP

   SYSIBM.SYSPARMS       - 1 row / parm / SP

 

DROP   PROCEDURE  spname  RESTRICT;

 

CREATE  PROCEDURE  spname

     (IN         parm1       CHAR(10)

    , IN         parm2      VARCHAR(10)

   ,  INOUT  parm3      CHAR(05

    , OUT      parm4      CHAR(25)

   ,  OUT      retrncd    SMALLINT

     )

EXTERNAL  LANGUAGE  COBOL

PARAMETER STYLE  GENERAL                     (i/p parms NEVER null            R1=A(:p1), A(:p2), A(:p3)

                                 GENERAL W/NULLS    (all i/p parms are nullable)       R1=A(:p1), A(:p2), A(:p3), A(:NI-tbl)

                                 DB2SQL                       (all i/p parms are nullable)       R1=A(:p1), A(:p2), A(:p3),
                                                                                                                         A(:NI-p1),  A(:NI-p2),  A(:NI-p3),
                                                                                                                         A(sqlstate), A(qualifiedname),
                                                                                                                         A(specificname),  A(errmsg)

DYNAMIC RESULT SET 10

MODIFIES SQL  DATA

READS        SQL DATA
CONTAINS
SQL
NO SQL

COMMIT ON RETURN NO              (default - local)
                                    YES             (distributed - commit issued - cursor must be defined 'WITH HOLD')

PARAMETER  CCSID  ASCII 
                                  EBCDIC
                                  UNICODE

COLLID   package-name                     (link between plan & packages)

PROGRAM TYPE SUB                        (initialize and cleanup variables yourself)

                          MAIN                     (LE does initialize and cleanup)

WLM ENVIRONMENT zt01swlm;      (Work Load Manager)

 

Sqlstate                   Char(05)

Qualified SP name    Varchar(27)
Specific   SP name    Varchar(18)

Errmsg                     Varchar(70)      (SQLMC)

 

 

CALLING MODULE:

Working Storage:

01  C1LOC1   SQL TYPE  IS  RESULT-SET-LOCATOR  VARYING.  (pointer to first result set / cursor)

 

use 01 level for parms passed to SP, if nullable, use null-indicator (NI) for each

 

Procedure Division:

EXEC SQL

    CONNECT TO db2tst01 

END-EXEC.

set null-indicators to -1 for null parms

 

CALL spname (:parm1 :ni-parm1, :parm2 :ni-parm2, :parm3 :ni-parm3...)

SQLCODE in caller = result of call

SQLCODE passed from SP = result of SP's sql

 

If passed sqlcode = +466, result set was created

 

EXEC SQL

    ASSOCIATE LOCATORS (:c1loc1) WITH PROCEDURE spname

END-EXEC

EXEC SQL

    ALLOCATE csrname CURSOR FOR RESULT SET :c1loc1

END-EXEC

.

.

EXEC SQL

    RELEASE db2tst01

END-EXEC

 

 

STORED PROCEDURE:

Linkage Section   (use multiple '01' levels for addressability)

01  LS-xxxxxx.

     49                                     PIC S9(04)  comp.

     49  LS-xxxxxx-txt             PIC   X(15).

01  LS-RTNCD                        PIC S9(04)  comp.

01  LS-NI-GRP.

     05  NI-xxxxxx                   PIC S9(04)  comp.

     05  NI-RTNCD                  PIC S9(04)  comp.

 

EXEC SQL

    DECLARE  csrname  CURSOR

    WITH HOLD

    WITH RETURN

    FOR .....

END-EXEC

 

PROCEDURE DIVISION USING LS-xxxxxx,  LS-RTNCD,   LS-IV-GRP

 

If i/p parm is nullable, check NI-xxxxxx < 0  (-1 is null)

If o/p parm is nullable, move 0 to NI-xxxxxx  if  populated

 

 

 


DB2
CICS
IMS
MQ
Stored Procedures
Resume


HOME