PH00256: CUMULATIVE MAINTENANCE PB53302 PB53147 PB53394 PB53293 Z651

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• (HPU 5,1 only) The sequence of the unloaded records is incorrect
  for some output data sets if the following conditions are
  fulfilled :
  - an UNLOAD command specifies several SELECT statements run in
  native mode and involving the same not-partitioned table space
  that fulfills the following conditions
  * at least one of this set of SELECT statements has an ORDER
    clause
  * a column involved in this set of SELECT statements fulfills
    the following conditions :
     > its name is not a item (explcitly or implicitly via the
       SELECT * specification) of any SELECT clause
     > its name is used in a non trivial (i.e. not limited to the
       column name but an actual computation is requested) SQL
       expression of either the SELECT clause or the WHERE clause
     > in the table record, it is located before the column
       involved by the ORDER clause
  Local fix: specify the SELECT statements affected by the issue
  under separate UNLOAD commands or force the processing in SQL
  mode by specifying the DB2 FORCE option. Be aware that this
  might decrease the performance of the unoad.
  -          PB53302
  (HPU 5,1 only) The error messages reporting errors at the record
  level that should display the area of the record involved in the
  error sometimes unduly display an area that starts at the
  beginning of the concerned area but that is either shorter or
  longer than the concerned area.
  For example, with the following unload statement
   UNLOAD TABLESPACE
   SELECT DE2 INTO C2 CHAR(6)
   FROM MZLTEST.TBCTVREF
   OUTDDN OUT
   FORMAT EXTERNAL
   OPTIONS NUMERIC_LOSS WARNING ;
   the error message displays the following
   234 * DATA LOST * OUT * C2 * F0099999999900F09999999999999999
   instead of
   234 * DATA LOST * OUT * C2 * F00999999999
  
  Local fix: none
  -          PB53147
  (HPU 5.1 only) The designation, help text and allowed values
  changes for the VUU037/ULSIGZ parameter related to PI95262 have
  not been made. The new values allowed for VUU037/ULSIGZ can
  therefore not be set via Tools Customizer.
  Local fix: set the new value in the INZUTIL parmlib member
  (SINZSAMP library) by specifying ULSIGZ = ulsigz_value where
  ulsigz_value stands or the desired value.
  -          PB53394
  (HPU 5,1 only) The data unloaded for a logical unload involving
  an ORDER clause and run in native mode is incorrect for both
  following reasons:
  - the output data contains altered values.
  - the sequence of the records is not as expected.
  This can occur when a column fulfills the following conditions:
  - it is one is one of the item of the SELECT clause
  - it is one of the columns involved in the ORDER clause
  - it is not the first column of the table
  
  The issue occurs if the above set of conditions is satisfied and
  if the unload is not run in the optimized mode for at least one
  of the following reasons:
  - for technical and internal reason, the optimized mode is not
    applicable
  - the OPTIM_LEVEL 0 option from the GLOBAL option block is
    specified
  - the VUU030/ULOPTNS parameter specifies FIX(OPTCNV)
  Local fix: remove the OPTIM_LEVEL 0 option or the FIX(OPTCNV)
  PB53293
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of High Performance Unload                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * PH00256:                                                     *
  * Fix 1 -                                                      *
  * The data unloaded for a logical unload involving an ORDER    *
  * clause and run in native mode is incorrect for both          *
  * following reasons:                                           *
  * - the output data contains altered values,                   *
  * - the sequence of the records is not as expected.            *
  * This can occur when a column fulfills the following          *
  * conditions:                                                  *
  * - it is one is one of the item of the SELECT clause          *
  * - it is one of the columns involved in the ORDER clause      *
  * - it is not the first column of the table                    *
  * The issue occurs if the above set of conditions is satisfied *
  * and if the unload is not run in the optimized mode for at    *
  * least one of the following reasons:                          *
  * - for technical and internal reason, the optimized mode is   *
  * not applicable                                               *
  * - the OPTIM_LEVEL 0 option from the GLOBAL option block is   *
  * specified                                                    *
  * - the VUU030/ULOPTNS parameter specifies FIX(OPTCNV)         *
  * Fix 2 -                                                      *
  * The sequence of the unloaded records is incorrect for some   *
  * output data sets if the following conditions are fulfilled : *
  * - an UNLOAD command specifies several SELECT statements run  *
  * in native mode and involving the same not-partitioned table  *
  * space that fulfills the following conditions                 *
  *    * at least one of this set of SELECT statements has an    *
  * ORDER clause                                                 *
  *    * a column involved in this set of SELECT statements      *
  * fulfills the following conditions :                          *
  *       > its name is not an item (explicitly or implicitly    *
  * via the SELECT * specification) of any         SELECT clause *
  *       > its name is used in a non trivial (i.e. not limited  *
  * to the column name but an actual         computation is      *
  * requested) SQL expression of either the SELECT clause or the *
  * WHERE clause                                                 *
  *       > in the table record, it is located before the column *
  * involved by the ORDER clause                                 *
  * Fix 3 -                                                      *
  * The error messages reporting errors at the record level that *
  * should display the area of the record involved in the error  *
  * sometimes unduly display an area that starts at the          *
  * beginning of the concerned area but that is either shorter   *
  * or longer than the concerned area.                           *
  * For example, with the following unload statement             *
  *       UNLOAD TABLESPACE                                      *
  *       SELECT DE2 INTO C2 CHAR(6)                             *
  *       FROM MZLTEST.TBCTVREF                                  *
  *       OUTDDN OUT                                             *
  *       FORMAT EXTERNAL                                        *
  *       OPTIONS NUMERIC_LOSS WARNING ;                         *
  * the error message displays the following                     *
  *       234 * DATA LOST * OUT * C2 *                           *
  * F0099999999900F09999999999999999                             *
  *  instead of                                                  *
  *       234 * DATA LOST * OUT * C2 * F00999999999              *
  * Fix 4 -                                                      *
  * The designation, help text and allowed values changes for    *
  * the VUU037/ULSIGZ parameter related to PI95262 have not been *
  * made. The new values allowed for VUU037/ULSIGZ can therefore *
  * not be set via Tools Customizer.                             *
  *                                                              *
  * PH00493:                                                     *
  * Create a PARMLIB symbolic to be able to change RC of jobs    *
  * when message INZU213W is issued.                             *
  * When a select without defining a column name when using an   *
  * expression and a SYSPUNCH is created, HPU issues message     *
  * INZU213W to alert that PUNCH needs to be modified.           *
  * The same way it has options to change RC of SQL use (DB2     *
  * YES) or zero line unloaded, we need an option to change the  *
  * RC of that message.                                          *
  *                                                              *
  * PH00494:                                                     *
  * HPU displays ROWID value when it has error during conversion *
  * from EBCDIC to ASCII.                                        *
  *                                                              *
  * PH00495:                                                     *
  * When a LISTDEF statement includes a table with either a LOB  *
  * or XML column, the INTERNAL format cannot be requested as it *
  * is not applicable to LOB or XML data. In such a case, in     *
  * order to ease mass unload processes where FORMAT INTERNAL is *
  * preferred, DB2 HPU should take an alternate format for the   *
  * table with LOB or XML columns into account. The alternate    *
  * format would be explicitly defined by the unload statement   *
  * or via the DB2 HPU settings.                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply the PTF for problem resolution                         *
  ****************************************************************
  

Problem conclusion

• Code has been changed to fix this problem
  

Temporary fix

• PH00256:
  Fix 1 - Remove the OPTIM_LEVEL 0 option or the FIX(OPTCNV)
  parameter if it was not intentionnally specified. None,
  otherwise.
  Fix 2 - Specify the SELECT statements affected by the issue
  under separate UNLOAD commands or force the processing in SQL
  mode by specifying the DB2 FORCE option. Be aware that this
  might decrease the performance of the unload.
  Fix 4 - Set the new value in the INZUTIL parmlib member
  (SINZSAMP library) by specifying ULSIGZ = ulsigz_value where
  ulsigz_value stands or the desired value.
  

Comments

APAR Information

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

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

  UI57474

Modules/Macros

• INZ$$PRM INZUTILK INZTVAR
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R510 PSY UI57474

     UP18/07/31 P F807

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.