KR edition 37

KR- 37 (DSNTIAR)

If you're using DB2-COBOL, you're bound to have obtained something similar to the following in the spool: 

DSNT408I SQLCODE = -501, ERROR: THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN 

followed by more details about why the DB2 operation failed. It could be anything: like timeout, deadlock message etc. 

Ever wondered how all that gets produced? 

It's not possible to decode every SQL code manually in DB2 and so we have a routine called DSNTIAR for help. It helps us in getting a text message instead of a bunch of numbers. You might have heard of SQLCA (SQL communication area)- after every DB2 command, this SQLCA structure (which is just a group variable) gets filled up with return codes etc. This will also contain many error decoding values (but all are numbers and not text messages). So either you have to manually look up into books to decode these fields or DSNTIAR can help you with them. 

To make use of this routine, you need to have a group variable in your code where DSNTIAR can store the text messages. And we need to call this routine when we want to decode an error message (this would be done in case SQLCODE is not 0). The following will be the call: 

CALL 'DSNTIAR' USING SQLCA 
WT-DSNTIAR-ERROR-TABLE 
WT-DSNTIAR-ERROR-TEXT-LENGTH. 

Hmm....you may wonder, "i never code such a line in my COBOL code!" Well we usually just copy paste the error routine. You might use a common ABEND-para. And this abend-para might be coded in another copylib. If you locate this copylib you'll find the above statement.

So, whenever we have a SQL problem, we call this abend para, which in turn calls the DSNTIAR routine which inturn uses the SQLCA structure to decode the sqlcode+other error numbers to produce a text message in our spool. The message might be something like below: 

DSNT408I SQLCODE = -501, ERROR: THE CURSOR IDENTIFIED IN A FETCH OR
CLOSE STATEMENT IS NOT OPEN
DSNT418I SQLSTATE = 24501 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXERT SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -315 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFEC5' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION 

Notice all the messages begin with a "DSNT"! 

Go back to the main contents page