PH07396: SET RC=8 WHEN THE DSNTIAUL GETS AN UNEXPECTED SQLCODE FROM PREPARE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DSNTIAUL returns a warning message without executing the query.
  DSNT404I SQLCODE = 20520, WARNING:  ATTEMPT TO USE A DEPRECATED
  FEATURE, REASON CODE: 2, DB2LPP.PLAN_TABLE
  and the resultant SYSREC00 contains no data even though the
  DSNTIAUL job step completed with RC=04.
  The parm TOLWARN(YES) is specified when running DSNTIAUL.
  

Local fix

• fix the condition that incurred the warning SQLCODE.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of DSNTIAUL in Db2 Version 12                      *
  * for z/OS are affected by this change.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * DSNTIAUL ends with return code of 4                          *
  * instead of 8 after processing of an                          *
  * SQL query is discontinued due to an                          *
  * SQL warning from PREPARE or FETCH.                           *
  * The output for the query is empty or                         *
  * incomplete.  This situation can occur                        *
  * for more than one query in the run.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  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.  By default, you
  need to specify a list of one or more tables to be unloaded.
  Alternatively, you can use DSNTIAUL's 'SQL' parameter option
  to specify full SQL queries.  These two options are referred
  to here as 'NOSQL mode' and 'SQL mode'.
  
  Currently, in NOSQL mode, when DSNTIAUL receives an SQL
  warning from the PREPARE request for unloading a table, it
  writes these messages:
    DSNT492I SQL WARNING DURING SQL STATEMENT PREPARE, TABLE xxx
    DSNT404I SQLCODE = sql-code, explanation
  discontinues processing of that SQL statement, and continues
  to the next SQL statement.  This situation can occur for one
  or more tables specified for unload in a single run.  At the
  end of processing, assuming no SQL errors have occurred,
  DSNTIAUL ends with return code 4.
  
  In SQL mode, when DSNTIAUL receives an SQL warning from
  PREPARE request for an SQL statement, it writes these messages:
    DSNT502I ERROR IN DSNTIAUL SQL INPUT: statement
    DSNT404I SQLCODE = sql-code, explanation
  discontinues processing of that SQL statement, and continues
  to the next SQL statement.  This situation can occur for one
  or more SQL statements in a single run.  At the end of
  processing, assuming no SQL errors have occurred, DSNTIAUL
  ends with return code 4.
  
  Likewise, in either NOSQL mode or SQL mode, when the DSNTIAUL
  TOLWARN option is NO (the default) and DSNTIAUL receives an
  SQL warning from a FETCH request for an SQL query, it writes
  these messages:
    DSNT492I SQL WARNING DURING SQL STATEMENT FETCH TABLE xxx
    DSNT404I SQLCODE = sql-code, explanation
  discontinues fetching rows for that query, and continues to
  the next SQL statement.  This situation can occur for one or
  more SQL statements in a single run.  At the end of processing,
  assuming no SQL errors have occurred, DSNTIAUL ends with return
  code 4.
  
  In all of the above cases, if no SQL errors have occurred
  during processing, DSNTIAUL ends with return code 4 even
  though incomplete results - or no results at all - were
  returned for any affected table requests or SQL statements.
  

Problem conclusion

• This APAR modifies DSNTIAUL to end with a return code of 8 when
  processing of at least one SQL statements is discontinued due
  to an SQL warning from PREPARE or FETCH.  If FETCH processing is
  continued, for example because the DSNTIAUL TOLWARN option is
  set to YES, then the return code remains 4 unless subsequent,
  more severe errors occur.
  

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:

  UI64087

Modules/Macros

• DSNTIAUL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI64087

     UP19/07/23 P F907

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.