Handling SQL error codes in assembler applications

Assembler applications can request more information about SQL error codes by using the DSNTIAR subroutine or issuing a GET DIAGNOSTICS statement.

Procedure

To request information about SQL errors in assembler programs, use the following approaches:

  • You can use the subroutine DSNTIAR to convert an SQL return code into a text message.
    DSNTIAR takes data from the SQLCA, formats it into a message, and places the result in a message output area that you provide in your application program. For concepts and more information about the behavior of DSNTIAR, see Displaying SQLCA fields by calling DSNTIAR.
    DSNTIAR syntax
    DSNTIAR has the following syntax:
    CALL DSNTIAR,(sqlca, message, lrecl),MF=(E,PARM)
    DSNTIAR parameters
    The DSNTIAR parameters have the following meanings:
    sqlca
    An SQL communication area.
    message
    An output area, defined as a varying-length string, in which DSNTIAR places the message text. The first halfword contains the length of the remaining area; its minimum value is 240.
    The output lines of text, each line being the length specified in lrecl, are put into this area. For example, you could specify the format of the output area as: 
    LINES    EQU   10
LRECL    EQU   132
   ⋮
MSGLRECL DC     AL4(LRECL)
MESSAGE  DS    H,CL(LINES*LRECL)
         ORG   MESSAGE
MESSAGEL DC    AL2(LINES*LRECL)
MESSAGE1 DS    CL(LRECL)      text line 1
MESSAGE2 DS    CL(LRECL)      text line 2
   ⋮
MESSAGEn DS    CL(LRECL)      text line n
   ⋮
         CALL DSNTIAR,(SQLCA,MESSAGE,MSGLRECL),MF=(E,PARM)
    where MESSAGE is the name of the message output area, LINES is the number of lines in the message output area, and LRECL is the length of each line.
    lrecl
    A fullword containing the logical record length of output messages, in the range 72–240.

    The expression MF=(E,PARM) is an z/OS® macro parameter that indicates dynamic execution. PARM is the name of a data area that contains a list of pointers to the call parameters of DSNTIAR.

    See Sample applications supplied with Db2 for z/OS for instructions on how to access and print the source code for the sample program.

  • If your CICS application requires CICS storage handling, you must use the subroutine DSNTIAC instead of DSNTIAR.
    DSNTIAC syntax
    DSNTIAC has the following syntax: 
    CALL DSNTIAC,(eib,commarea,sqlca,msg,lrecl),MF=(E,PARM)
    DSNTIAC parameters

    DSNTIAC has extra parameters, which you must use for calls to routines that use CICS commands.

    eib
    EXEC interface block
    commarea
    communication area

    For more information on these parameters, see the appropriate application programming guide for CICS. The remaining parameter descriptions are the same as those for DSNTIAR. Both DSNTIAC and DSNTIAR format the SQLCA in the same way.

    You must define DSNTIA1 in the CSD. If you load DSNTIAR or DSNTIAC, you must also define them in the CSD. For an example of CSD entry generation statements for use with DSNTIAC, see member DSN8FRDO in the data set prefix.SDSNSAMP.

    The assembler source code for DSNTIAC and job DSNTEJ5A, which assembles and link-edits DSNTIAC, are also in the data set prefix.SDSNSAMP.

  • You can also use the MESSAGE_TEXT condition item field of the GET DIAGNOSTICS statement to convert an SQL return code into a text message.

    Programs that require long token message support should code the GET DIAGNOSTICS statement instead of DSNTIAR. For more information about GET DIAGNOSTICS, see Checking the execution of SQL statements by using the GET DIAGNOSTICS statement.