Mainframegeek: Utility Programs

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.

IEBCOPY is a dataset utility which used to copy or merge members between Partitioned datasets (PDS) or Partitioned datasets Extended (PDSE), fully or partially.

Common uses of IEBCOPY

1. Copy selected or all members of one PDS into another.

2. Copy a Partitioned dataset into a unique sequential format known as “ Unloaded Partitioned dataset” which can be FTP, manipulate as a sequential dataset or written to Tape.

3. To read an unloaded Partitioned dataset or restore into a partitioned dataset.

4.Compress PDS( c ) in place to recover free space.

The ISPF copy options (3.3) use IEBCOPY under the cover. Moving a PDS or PDSE can be easily achieved by IEBCOPY.

Simple IEBCOPY job

//COPYJOB5 JOB SHIBU,REGION=1024K
//         EXEC PGM=IEBCOPY
//SYSPRINT DD SYSOUT=*
//SYSIN    DD DUMMY
//SYSUT1   DD DISP=SHR,DSN=PGMG.COBOL.SOURCE
//SYSUT2   DD DISP=(NEW,KEEP),UNIT=TAPE,DSN=ARCHIVE.SOURCE,
//   VOL=SER=XXXXXX

SYSUT1 is the input dataset and SYSUT2 is the output dataset. The above job unload the SYSUT1 pds (PGMG.COBOL.SOURCE ) and write into TAPE, with the PDS name provided in SYSUT2.

Note: In the above job output dataset name is not same as the input dataset name, however there is no harm in using the same name.

Job to Restore PDS in a volume.

//COPYJOB6 JOB SHIBU,MSGCLASS=X,REGION=1024K
//         EXEC PGM=IEBCOPY
//SYSPRINT DD SYSOUT=*
//SYSIN    DD DUMMY
//SYSUT1   DD DISP=OLD,UNIT=TAPE,DSN=ARCHIVE.SOURCE,
//   VOL=SER=XXXXXX
//SYSUT2 DD DISP=(NEW,CATLG),DSN=PGMG.COBOL.SOURCE,UNIT=3390,
//   SPACE=(TRK,(10,10,20)),VOL=SER=XXXXXX

This Job read unloaded partitioned dataset from TAPE and write the contents into vol XXXXXX, with the name provided in SYSUT1, 10 tracks and 20 Directory blocks.

We can also use the above job to move datasets from one volume into another with smaller changes.

//COPYJOB7 JOB 1,SHIBU,MSGCLASS=X,NOTIFY=&SYSUID
//         EXEC PGM=IEBCOPY
//SYSPRINT DD SYSOUT=*
//SYSIN    DD DUMMY
//SYSUT1   DD DISP=OLD,DSN=PROD.PGMG.C,UNIT=3390,
//   VOL=SER=VOL1
//SYSUT2 DD DISP=(NEW,CATLG),DSN=SYSI.PGMG.C1,UNIT=3390,
//   SPACE=(TRK,(10,10,20)),VOL=SER=NEWVOL

Instead of using Dummy in the sysin we can specify additional selection criteria to IEBCOPY

E.g.

//SYSIN DD *
  COPY OUTDD=SYSUT2,INDD=SYSUT1
  SELECT MEMBER=(PGM1,PGM2)
/*

SELECT statement specifies the members to be processed with the job. If you have used any other ddnames for the datasets other than SYSUT1 or SYSUT2 then you can use OUTDD, INDD parameters to pass the dataset names.

Restoring a partitioned data set from an unloaded copy automatically compresses (recovers lost space) the data set.

Mainframegeek

Pages

Tuesday, September 13, 2011

DSNTIAR & DSNTIAC–SQLCA formatter for Batch & CICS