Mainframegeek: DB2

Showing posts with label DB2. Show all posts

Tuesday, September 13, 2011

DSNTIAR & DSNTIAC–SQLCA formatter for Batch & CICS

We must check for the SQLCODE before we issue commit on a table in DB2, handle the errors- Display description or whatever. handling/remembering reason for each and every sqlcode every time is a time pressing task always. There is DSNTIAR and DSNTIAC comes in picture. DSNTIAR and DSNTIAC are two assembler routine which helps to get a formatted version of SQLCA and the text message based on the sqlcode in the SQLCA.

DSNTIAR takes data from SQLCA formats and puts into a data area provided by the calling program. DSNTIAR will overwrite the contents of the data area before it moves the data.

DSTIAR expects the SQLCA in its original for, so before you call DSNTIAR/DSNTIAC make sure that you haven’t modified SQLCA.

Defining Message output Data name

Calling program must allocate output message data name and pass it,

make sure:

1. First 2 bytes are length – a pic s9(4) comp will be ideal for the purpose.

2.You must define enough space for the message in character type, data should be minimum of 10 lines of 72 bytes. A PIC x(72) occurs 10 times. will be quite enough for the message.

e.g.

01 ERROR-MESSAGE.
        02 ERROR-LEN   PIC S9(4) COMP VALUE +720.
        02 ERROR-TEXT PIC X(72) OCCURS 10 TIMES.

keep the length in another variable.

77 ERROR-TEXT-LEN PIC S9(9) COMP VALUE +72.

Once we executed a SQL statement, we are ready to issue a call to DSNTIAR,

CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN

display the resultant message by displaying “ ERROR-TEXT() “ table.

Possible return codes for DSNTIAR/DSNTIAC

0	Successful execution.
4	More data available than could fit into the provided message area.
8	Error record length is not between 72 & 240
12	Message area is not large enough, we need to provide more space needs to be defined and must be passed.
16	Error in message routine.

Sample program

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 ERROR-MESSAGE.
         02 ERROR-LEN   PIC S9(4) COMP VALUE +720.
         02 ERROR-TEXT PIC X(72) OCCURS 10 TIMES.
77 ERROR-TEXT-LEN      PIC S9(9) COMP VALUE +72.

01 WS-TEMP-VAR.
     05 TEMP                    PIC X(30).
     05 TEMP-MSG                PIC X(60).
     05 WS-IND                  PIC X VALUE 'Y'.
01 WS-TBLE-DTA.
     05 WS-SEQ                  PIC X(3).
     05 WS-SEQ-TMP              PIC X(3).
     05 WS-NAME                 PIC X(15).

77 WS-I                        PIC S9(4) COMP.

01 WS-SQLCODE                  PIC ------9.
*
PROCEDURE DIVISION.
     PERFORM A00100-READ-PARA.

Z00100-EXIT-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.
     EVALUATE TRUE
     WHEN SQLCODE = 0
      PERFORM A00150-READ-PARA
     WHEN SQLCODE > 0
      DISPLAY 'FETCH WAS SUCCESS WITH A WARNING'
      CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN
      PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I = 10
        DISPLAY ERROR-TEXT(WS-I)
      END-PERFORM
     WHEN OTHER
      CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN
      PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I = 10
        DISPLAY ERROR-TEXT(WS-I)
      END-PERFORM
      PERFORM Z00100-EXIT-PARA
     END-EVALUATE.
     MOVE SQLCODE             TO WS-SQLCODE.
     DISPLAY 'SQLCODE OPEN ' WS-SQLCODE.
A00150-READ-PARA.
     DISPLAY '****DATA FROM TABLE***'
     PERFORM UNTIL SQLCODE = 100
        EXEC SQL
           FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
        END-EXEC
        MOVE SQLCODE             TO WS-SQLCODE
        EVALUATE TRUE
        WHEN SQLCODE = 0
         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
        WHEN SQLCODE > 0
         DISPLAY 'FETCH WAS SUCCESS WITH A WARNING'
         CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN
         PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I = 10
         DISPLAY ERROR-TEXT(WS-I)
      END-PERFORM
        WHEN OTHER
         CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN
         PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I = 10
           DISPLAY ERROR-TEXT(WS-I)
         END-PERFORM
         PERFORM Z00100-EXIT-PARA
        END-EVALUATE
     END-PERFORM.
     DISPLAY '****END OF TABLE DATA****'
     EXEC SQL
        CLOSE CURREAD1
     END-EXEC.
     MOVE SQLCODE             TO WS-SQLCODE.
     DISPLAY 'SQLCODE CLOSE ' WS-SQLCODE.
     EXIT
     .

Compiling and link editing.

remember you must concatenate <HLQ>.SDSNLOAD of DB2 installed in your host mainframe to the STEPLIB of compile step, otherwise it will result in DSNTIAR module not found error while compiling. Read http://mainframegeek.wordpress.com/2011/05/12/steps-in-a-cobol-db2-program/ for the jcl for compile, link edit and exec ut the DB2 cobol program. Don’t forget to change the JCL as mentioned below.

e.g.

//DB2COBA 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(DBPGM02),DISP=SHR
//SYSIN     DD DSN=TSHRCI.PGMG.COBOL1(DBPGM02),DISP=SHR
//SYSLIB    DD DSN=TSHRCI.PGMG.COBOL1,DISP=SHR
//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
//*****************************************************************

Outputs.

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.

Pages