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,
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.
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
|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.|
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.
//DB2COBA JOB (12345678),MSGCLASS=H,REGION=4M,