PI79053: SUPPORT TOLWARN(QUIET) OPTION IN DSNTIAUL TO SUPPRESS MESSAGES WHEN SQLCODE IS ZERO BUT SQLSTATE HAS WARNINGS. MR0929081216

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Support a new TOLWARN suboption (QUIET) in DSNTIAUL to suppress
  printing SQLCA messages to SYSPRINT file when SQLCODE=0 but
  the SQLSTATE has warning flags set for multirow fetch. The
  message is issued once per row. With multirow fetch, there can
  be millions of messages issued and fill up the SYSPRINT file.
    This suboption would be treated like TOLWARN(YES) where the
  execution is continued on an SQL warning for an OPEN or a FETCH
  but the message and SQLCA information would be suppressed.
  This apar is for V11 and V12.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DSNTIAUL in Db2 Version 11 and  *
  *                 Db2 Version 12 for z/OS are affected by this *
  *                 change.                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR provides a new option for     *
  *                      DSNTIAUL that can be used to suppress   *
  *                      output of SQL warning messages from     *
  *                      OPEN and FETCH.                         *
  ****************************************************************
  * RECOMMENDATION: Apply the fixing PTF for this APAR           *
  ****************************************************************
  DSNTIAUL is a sample dynamic SQL processing application that is
  used primarily to unload data from Db2 tables to an externalized
  format.  DSNTIAUL also generates a LOAD statement that can be
  used in conjunction with the Db2 LOAD utility to load the
  externalized data back into Db2.
  
  DSNTIAUL has a parameter called TOLWARN that specifies whether
  to continue processing an unload request that encounters an
  SQL warning from an OPEN or FETCH operation.  For example,
    RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL,TOLWARN(NO)')
  means that processing should not continue; whereas
    RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL,TOLWARN(YES)')
  means that processing should continue and that DSNTIAUL
  should print diagnostic messages for each SQL warning from
  OPEN or FETCH.
  
  Some queries, when processed with TOLWARN(YES) in effect, can
  cause DSNTIAUL to print SQL warning messages after every FETCH.
  The SYSPRINT output can be flooded with messages like these:
  
    DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION
    DSNT418I SQLSTATE   = 01004 SQLSTATE RETURN CODE
    DSNT415I SQLERRP    = DSN SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD    = 0 0  0  -1  0  0 SQL DIAGNOSTIC
                          INFORMATION
    DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'
           X'FFFFFFFF'
           X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
    DSNT417I SQLWARN0-5 = W,W,,,, SQL WARNINGS
    DSNT417I SQLWARN6-A = ,,,,   SQL WARNINGS
  

Problem conclusion

• In response, this APAR provides a new option for TOLWARN called
  QUIET that is like the YES option except that DSNTIAUL will
  suppress all SQL warning messages from OPEN or FETCH if the
  SQLCODE is greater than or equal to 0.  Example:
    RUN  PROGRAM(DSNTIAUL) PARMS('SQL,TOLWARN(QUIET)')
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  PI79057 UI56309 UI56310

Modules/Macros

•    DSNTIAUL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI56310

     UP18/06/21 P F806

• RC10 PSY UI56309

     UP18/06/21 P F806

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.