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 +72
0.             
         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                       &nb
sp;        
         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    &nb
sp;                  
//*                                                                   
//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.

image

2 thoughts on “DSNTIAR & DSNTIAC–SQLCA formatter for Batch & CICS

  1. Hi, in the ws-sqlcode declaration, do you mention pic —9? is this same as s9 (signed numeric, 1 byte)? Isnt the sqlcode pic s9(3) Can you clarify this please? Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + fifteen =