Mainframegeek: Cobol Db2 run JCL

Showing posts with label Cobol Db2 run JCL. Show all posts

Wednesday, August 17, 2011

CURSORS in DB2

When we are using the DB2 in our applications we can only have one row of data at a time. So what we will do if we don't know which row exactly we need?, what if we have more than 1 row to work with? well the answer is "CURSORS".

Cursor is used when more than one row are to be selected. Cursors has mainly 4 control statements.

1. Declare.

A name will be assigned for particular SQL statement. The name should be unique in the scope of the program. there are no limits for the number of cursors which we can have in one application program. We can declare cursor in Working storage section or Procedure division.
E.g.
EXEC SQL
    DECLARE CURREAD1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
END-EXEC.

2. Open.

This statement builds the resultant table.
E.g.
EXEC SQL
   OPEN CURREAD1
END-EXEC.

3. Fetch.

Fetch statement will returns data from the resultant table (One row at a time) and assigns values to the specified host variables.
E.g.
EXEC SQL
   FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
END-EXEC

4. Close

Empty all the resources used by the cursor.
E.g.
EXEC SQL
   CLOSE CURREAD1
END-EXEC.

All these control statements will throw specific SQLCODES.

Few Snippets.

Read table.

EXEC SQL
    DECLARE CURREAD1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
END-EXEC.
Open
EXEC SQL
   OPEN CURREAD1
END-EXEC.

PERFORM UNTIL SQLCODE = 100
   EXEC SQL
      FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
   END-EXEC
   MOVE SQLCODE             TO WS-SQLCODE
   DISPLAY 'SQLCODE FETCH ' WS-SQLCODE
   IF SQLCODE = 0 THEN
    MOVE SPACES TO TEMP-MSG
    STRING
    'NAME: ' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
    WS-NAME DELIMITED BY SPACE ',' DELIMITED BY SIZE
    'SEQ#' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
    WS-SEQ DELIMITED BY SIZE INTO TEMP-MSG
    DISPLAY TEMP-MSG
   END-IF
END-PERFORM.

Fetch name and SEQ till we hit SQLCODE 100 and display the data.

Update Table

We need to mention FOR UPDATE OF and the field name in declare statement.

EXEC SQL
    DECLARE CURUPDT1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
        WHERE SEQ = :WS-SEQ
        FOR UPDATE OF NAME
END-EXEC.
here I will be updating NAME field of MYNAM table
EXEC SQL
   OPEN CURUPDT1
END-EXEC.
MOVE '002'               TO WS-SEQ.
EXEC SQL
   FETCH CURUPDT1 INTO :WS-NAME,:WS-SEQ-TMP
END-EXEC
EXEC SQL
   UPDATE IBMGRP.MYNAM
   SET NAME = :WS-NAME
   WHERE CURRENT OF CURUPDT1
END-EXEC.
"CURRENT OF CURUPDT1" statement will pick the current row to update.
EXEC SQL
   CLOSE CURUPDT1
END-EXEC.

Delete Record

Like cursor for updating a record we need to mention FOR UPDATE OF in cursor declaration statement.

EXEC SQL
    DECLARE CURDELT1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
        WHERE SEQ = :WS-SEQ
        FOR UPDATE OF NAME
END-EXEC.
EXEC SQL
   OPEN CURDELT1
END-EXEC.
EXEC SQL
   FETCH CURDELT1 INTO :WS-NAME,:WS-SEQ-TMP
END-EXEC
EXEC SQL
   DELETE FROM IBMGRP.MYNAM
   WHERE SEQ = :WS-SEQ
END-EXEC.
EXEC SQL
   CLOSE CURDELT1
END-EXEC.

Sample DB2-COBOL-CURSOR Code
Please refer http://mainframegeek.wordpress.com/2011/05/12/steps-in-a-cobol-db2-program to get JCL for compiling and executing DB2-COBOL-CURSOR program

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
       IDENTIFICATION DIVISION.
       PROGRAM-ID. DBPGM01.
       AUTHOR    . SHIBU.T.
      *
       DATA DIVISION.
       WORKING-STORAGE SECTION.
           EXEC SQL
               INCLUDE MYNAM
           END-EXEC.
           EXEC SQL
               INCLUDE SQLCA
           END-EXEC.
       01 WS-TEMP-VAR.
           05 TEMP                    PIC X(30).
           05 TEMP-MSG                PIC X(60).
       01 WS-TBLE-DTA.
           05 WS-SEQ                  PIC X(3).
           05 WS-SEQ-TMP              PIC X(3).
           05 WS-NAME                 PIC X(15).
       01 WS-SQLCODE                  PIC ------9.
      *
       PROCEDURE DIVISION.
           PERFORM A00100-READ-PARA.
           PERFORM A00200-UPDATE-PARA.
           PERFORM A00100-READ-PARA.
           PERFORM A00400-INSERT-PARA.
           PERFORM A00100-READ-PARA.
           PERFORM A00300-DELETE-PARA.
           PERFORM A00100-READ-PARA.
           STOP RUN.
       A00100-READ-PARA.
           DISPLAY '         A00100-READ-PARA.           '
           EXEC SQL
               DECLARE CURREAD1 CURSOR FOR
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM
           END-EXEC.
           EXEC SQL
              OPEN CURREAD1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE OPEN ' WS-SQLCODE.
           DISPLAY '****DATA FROM TABLE***'
           PERFORM UNTIL SQLCODE = 100
              EXEC SQL
                 FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
              END-EXEC
              MOVE SQLCODE             TO WS-SQLCODE
              DISPLAY 'SQLCODE FETCH ' WS-SQLCODE
              IF SQLCODE = 0 THEN
               MOVE SPACES TO TEMP-MSG
               STRING
               'NAME: ' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
               WS-NAME DELIMITED BY SPACE ',' DELIMITED BY SIZE
               'SEQ#' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
               WS-SEQ DELIMITED BY SIZE INTO TEMP-MSG
               DISPLAY TEMP-MSG
              END-IF
           END-PERFORM.
           DISPLAY '****END OF TABLE DATA****'
           EXEC SQL
              CLOSE CURREAD1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE CLOSE ' WS-SQLCODE.
      *
       A00200-UPDATE-PARA.
           DISPLAY '         A00200-UPDATE-PARA.         '
           EXEC SQL
               DECLARE CURUPDT1 CURSOR FOR
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM
                   WHERE SEQ = :WS-SEQ
                   FOR UPDATE OF NAME
           END-EXEC.
           EXEC SQL
              OPEN CURUPDT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE OPEN ' WS-SQLCODE.
           MOVE '002'               TO WS-SEQ.
           EXEC SQL
              FETCH CURUPDT1 INTO :WS-NAME,:WS-SEQ-TMP
           END-EXEC
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE FETCH ' WS-SQLCODE.
           MOVE 'SHYAM-KUMAR'       TO WS-NAME.
           EXEC SQL
              UPDATE IBMGRP.MYNAM
              SET NAME = :WS-NAME
              WHERE SEQ = :WS-SEQ
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE UPDT ' WS-SQLCODE.
           EXEC SQL
              CLOSE CURUPDT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE CLOSE ' WS-SQLCODE.
           EXIT.
      *
       A00400-INSERT-PARA.
           DISPLAY '         A00400-INSERT-PARA.         '
           MOVE 'TEMP-NAME'         TO WS-NAME.
           MOVE 007                 TO WS-SEQ.
           EXEC SQL
               INSERT INTO IBMGRP.MYNAM
               VALUES ( :WS-SEQ,:WS-NAME)
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE INSRT ' WS-SQLCODE.
           EXIT.
      *
       A00300-DELETE-PARA.
           DISPLAY '         A00300-DELETE-PARA.         '
           EXEC SQL
               DECLARE CURDELT1 CURSOR FOR
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM
                   WHERE SEQ = :WS-SEQ
                   FOR UPDATE OF NAME
           END-EXEC.
           EXEC SQL
              OPEN CURDELT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE OPEN ' WS-SQLCODE.
           MOVE '007'               TO WS-SEQ.
           EXEC SQL
              FETCH CURDELT1 INTO :WS-NAME,:WS-SEQ-TMP
           END-EXEC
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE FETCH ' WS-SQLCODE.
           EXEC SQL
              DELETE FROM IBMGRP.MYNAM
              WHERE SEQ = :WS-SEQ
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE DELT ' WS-SQLCODE.
           EXEC SQL
              CLOSE CURDELT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE CLOSE ' WS-SQLCODE.
           EXIT.

Screen Shots

Please tryout the code yourself and let me know if you have any concerns.

Thursday, May 12, 2011

Steps in a COBOL-DB2 Program

When I started exploring DB2-COBOL programs over internet, I realized that we haven’t got a lot of tutorials for Cobol-db2 programs over there. So I thought I can share some of my programs with all of you. My Cobol-DB2 program is very small, but it could help us to understand the structure, how to compile, how to bind and how to run very well.

Step 1. DB2 Table structure.

---------+---------+
SEQ NAME
---------+---------+
001 SHIBU
002 SABU
003 BALANKRISHNAN
004 SREEMATHY

This is my table it has only two columns, first 3 var char for sequence numbrs and next 15 var char for Name field.

Create table:

Go to SPUFI create our table using the following SQL statements.

CREATE TABLE MYNAM
    ( SEQ    VARCHAR(03),
      NAME   VARCHAR(15));

INSERT INTO MYNAM VALUES(‘001’,’SHIBU’);
INSERT INTO MYNAM VALUES(‘002’,’SABU’);
INSERT INTO MYNAM VALUES(‘003’,’BALAKRISHNAN’);
INSERT INTO MYNAM VALUES(‘004’,’SREEMATHY’);

COMMIT;

Step 2. Execute DCL gen to create copybook for table.

Navigate to DB2I primary options and select DCLGEN

Now Enter table name (mynam) in source table name, location for copybook ( 'TSHRCI.PGMG.CICOB(mynam)' in our program )on dataset name field and type ADD in action field .You can leave default values in rest of the fields. Then press enter.

You can verify that, whether copy book generated on the given pds.

Step 3. Code the application. ( cobol db2 sample code)

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.
       PROGRAM-ID. MYTAB.
       AUTHOR.     SHIBU.T
       DATA DIVISION.
       WORKING-STORAGE SECTION.
           EXEC SQL
             INCLUDE MYNAM
           END-EXEC.
           EXEC SQL
             INCLUDE SQLCA
           END-EXEC.
       01 WS-ROLL-NO                         PIC X(3) VALUE IS '000'.
       01 WS-ROLL-N1                         PIC 9(3).
       01 WS-NAME                            PIC X(15).
     01 WS-SQLCODE                         PIC --------9.
       PROCEDURE DIVISION.
       PARA-00100-MAIN.
           DISPLAY 'EXECUTION STARTED.'.
           MOVE 0                             TO WS-ROLL-N1.
           PERFORM UNTIL WS-ROLL-NO = '001'
             COMPUTE WS-ROLL-N1 = WS-ROLL-N1 + 1
             MOVE WS-ROLL-N1                  TO WS-ROLL-NO
             PERFORM PARA-00200-FETCH THRU PARA-00200-FETCH-EXIT
           END-PERFORM.
           STOP RUN.

       PARA-00200-FETCH.
           DISPLAY 'PARA-00200-FETCH.'.
           EXEC SQL
             SELECT *
               INTO : DCLMYNAM
             FROM MYNAM
             WHERE SEQ = : WS-ROLL-NO
           END-EXEC.
           IF SQLCODE = 0
             DISPLAY 'EXECUTION SUCESS!'
             PERFORM PARA-00300-PRINT THRU PARA-00300-PRINT-EXIT
           ELSE
             DISPLAY 'FETCH FAILED!, PROGRAMM TERMINATING.'
             MOVE SQLCODE                     TO WS-SQLCODE
             DISPLAY 'SQL CODE FROM LAST FETCH: ' WS-SQLCODE
           END-IF.
       PARA-00200-FETCH-EXIT.
           EXIT.

       PARA-00300-PRINT.
           DISPLAY 'NAME IS >> ' NAME ' <<'.
       PARA-00300-PRINT-EXIT.
           EXIT.

Step 4. key elements of COBOL DB2 program.

a) Include tables copy book and SQLCA.

We have to include the copy book of table which we desired to use on our app. syntax is follows

EXEC SQL
INCLUDE MYNAM
END-EXEC.

Also it is important that including SQL communication area copybook into our program. Syntax follows.

EXEC SQL
INCLUDE SQLCA
END-EXEC.

Step 4 Compile and BIND COBOL-DB2 Program

Above Diagram is pretty well describes the whole process of compiling a cobol DB2 program. As I mentioned in the image, precompile step I sno longer required in the latest releases of Cobol, the compiler itself is capable of doing this task.

Step 5. RUN Program

When COBOL-DB2 Program executes, the plan and Package must specified in the SYSIN, When the first sql statement of our program executes, db2 search the collections and consistency token in the provided plan using the provided package name, if the consistency token is not matching gDB2 throw a -805 Error.

Cobol-DB2-Compile-BIND-RUN JCL.

//R0318BDJ JOB (12345678),MSGCLASS=H,REGION=4M,
//        MSGLEVEL=(1,1),CLASS=A,NOTIFY=&SYSUID
//*
//JOBLIB    DD DSN=DSN910.DB9G.SDSNEXIT,DISP=SHR
//          DD DSN=DSN910.SDSNLOAD,DISP=SHR
//*****************************************************************
//* SQL PREPROC AND COBOL COMPILATION:
//*****************************************************************
//*-NB---SQL PREPROC NOW IS NOW DONE BY THE COBOL COMPILER:
//*****************************************************************
//COB     EXEC PGM=IGYCRCTL,
//            PARM=(SQL,LIB,NOTERM,NOSEQUENCE,LIB,XREF,DYN,'')
//STEPLIB   DD DSN=IGY410.SIGYCOMP,DISP=SHR
//          DD DSN=DSN910.SDSNLOAD,DISP=SHR
//DBRMLIB   DD DSN=TSHRCI.PGMG.DBRM(MYTAB),DISP=SHR <<Desired DBRM Loc
//SYSIN     DD DSN=TSHRCI.PGMG.COBOL(MYTAB),DISP=SHR <<SRCE LIB
//SYSLIB    DD DSN=TSHRCI.PGMG.COBOL,DISP=SHR <<CPYBK LIB
//SYSLIN    DD DSN=&&LOADSET,DISP=(MOD,PASS),UNIT=SYSDA,
//             SPACE=(800,(500,500))
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1    DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT2    DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT3    DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT4    DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT5    DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT6    DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT7    DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//*****************************************************************
//*            LINKEDIT
//*****************************************************************
//LKED    EXEC PGM=IEWL,PARM='XREF',COND=(4,LT,COB)
//SYSLIB   DD DISP=SHR,DSN=CEE.SCEELKED
//         DD DISP=SHR,DSN=DSN910.SDSNLOAD
//         DD DISP=SHR,DSN=DFH320.CICS.SDFHLOAD
//         DD DISP=SHR,DSN=ISP.SISPLOAD
//         DD DISP=SHR,DSN=GDDM.SADMMOD
//SYSLIN    DD DSN=&&LOADSET,DISP=(OLD,DELETE)
//          DD DDNAME=SYSIN
//SYSLMOD   DD DSN=TSHRCI.LOAD.LIB,DISP=SHR <<desired LOAD LIB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1    DD SPACE=(1024,(50,50)),UNIT=SYSDA
//SYSIN     DD *
     NAME MYTAB(R) << Program Name
/*
//*****************************************************************
//* BIND
//*****************************************************************
//BIND    EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//DBRMLIB   DD DSN=TSHRCI.PGMG.DBRM,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT    DD SYSOUT=*
//SYSTSIN   DD *
DSN SYSTEM(DB9G) <<Change this to your DB2 subsystem>>
BIND PLAN(MYTABP) MEMBER(MYTAB) -
        ACT(REP) ISO(CS) ENCODING(EBCDIC)
END
/*
//*****************************************************************
//* RUN PGM
//*****************************************************************
//RUNPGM   EXEC PGM=IKJEFT01,DYNAMNBR=20 COND=(4,LT)
//STEPLIB DD DSN=DSN910.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB9G) <<Change it to your db2 subsystem>>
RUN PROGRAM(MYTAB) PLAN(MYTABP) LIB('TSHRCI.LOAD.LIB')
END
//*

Mainframegeek

Pages