Mainframegeek: September 2011

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
//*****************************************************************

Mainframegeek

Pages

Tuesday, September 13, 2011

DSNTIAR & DSNTIAC–SQLCA formatter for Batch & CICS

Defining Message output Data name

make sure:

Possible return codes for DSNTIAR/DSNTIAC

Sample program

Compiling and link editing.

Outputs.