Using DSNTIAR

DSNTIAR formats the SQLCA and returns a readable text message based on the SQLCODE. Here's the syntax:

CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LENGTH

ERROR-MESSAGE should contain an array where the actual formatted message should be stored. In addition to that, the first elementary item in ERROR-MESSAGE should be an integer indicating the length of the array. For example, if you declared a 5-element array and each element is 72 characters long, the length of the array is 360 and this should be indicated by the integer in ERROR-MESSAGE.

How many elements should your array have and how long should the text be? That will depend on your requirements. The number of elements in the array determines how many lines of text you will be able to display. If the message for the SQLCODE that you are decoding exceeds the number of elements in your array, DSNTIAR will have a return-code of 4. Your requirements will also dictate how long each element should be. The minimum length is 72 and the maximum is 240. If you want each line to fit in the screen, you'll probably go for 72 to 80.

ERROR-TEXT-LENGTH indicates the size of each element in the array inside ERROR-MESSAGE.

Here's an example:
01  WS-ERROR-MESSAGE.
    05 WS-ERROR-MSG-LENGTH PIC S9(4) COMP VALUE +288.
    05 WS-ERROR-MSG-TEXT PIC X(72
       OCCURS 4 TIMES
       INDEXED BY ERROR-IX.

01 WORK-AREA.
    05 WS-ERROR-TEXT-LENGTH PIC S9(9) COMP VALUE +72.
.
.
.
CALL 'DSNTIAR' USING SQLCA
               WS-ERROR-MESSAGE
               WS-ERROR-TEXT-LENGTH

PERFORM VARYING ERROR-IX
   FROM 1 BY 1
  UNTIL ERROR-IX > 4

        DISPLAY WS-ERROR-MESSAGE-TEXT(WS-ERROR-IX)

END-PERFORM

If your SQLCODE = -803, the code above will display the following:

DSNT408I SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID
         BECAUSE INDEX IN INDEX SPACE XPERSON CONSTRAINS COLUMNS OF THE
         TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE
         COLUMNS.

Diagnosing Multi-Row Insert Errors

Errors encountered on an ATOMIC multi-row insert will be reflected on the SQLCODE. For example, if the ATOMIC multi-row insert failed due to duplicate rows, SQLCODE will be -803.

A NON-ATOMIC multi-row insert can return the following SQLCODE:
   

• 0 - Successful
  
• +252 - Successful but with Warnings
  
• -253 - At least one row (but not all) failed
  
• -254 - All inserts failed. No rows were inserted.
  

In order to determine how many rows were successfully inserted, which row failed, and to find out what errors were encountered, you need to execute the GET DIAGNOSTICS statement.

To get the number of rows and the number of error conditions encountered, you can execute GET DIAGNOSTICS this way:

EXEC SQL GET DIAGNOSTICS
    :ROWS-INSERTED = ROW_COUNT,
    :ERROR-CONDITIONS = NUMBER
END-EXEC

Now that you know the number of error conditions encountered you can perform a loop to check out those errors:

PERFORM VARYING I FROM 1 BY 1
  UNTIL I > ERROR-CONDITIONS

        EXEC SQL GET DIAGNOSTICS
            :WS-SQLCODE = DB2_RETURNED_SQLCODE,
            :WS-ROWNUM = DB2_ROW_NUMBER
        END-EXEC

        PERFORM Z900-DB2-ERRORS

END-PERFORM