PH20818: IGNORE ORDER CLUSTER WHEN SWITCHING TO SQL MODE

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• The ORDER CLUSTER feature cannot be honored when a logical
  unload is processed in SQL mode. This leads Db2 HPU to reject a
  logical unload which processing requires the SQL mode (either
  because the involved SELECT statement is not supported or
  because DB2 FORCE is specified) by not unloading the data and to
  end with completion code of 8.
  For automatic generation of UNLOAD statements, as determining
  what SELECT statement Db2 HPU supports in native mode by program
  is complex and might change along with the maintenance, the use
  of the DB2 YES option allows to request Db2 HPU to choose the
  relevant processing mode. Specifying the ORDER CLUSTER option is
  also a convenient way to get data in the preferred sequence when
  the data is to be reloaded into the tables with the same
  clustering index.
  When both DB2 YES and ORDER CLUSTER option are specified, if the
  SELECT statement is not supported by Db2 HPU for native
  processing, the unload fails. If the ORDER CLUSTER is only a
  preference for performance but not an actual requirement, it
  would be useful to allow Db2 HPU to ignore the ORDER CLUSER
  option whenever not applicable.
  ------------------
  The planned implementation is as follows:
  ------------------
  An additional keyword may be specified after the ORDER CLUSTER
  clause to specify whether the feature is mandatory or only a
  preference. Something like:
                                     +- value of ORDER_CLUSTER_BHV
  in VUU030/ULOPTNS -+
  >-------------ORDER
  CLUSTER--------+------------------------------------------------
  +------->
                                     +-MANDATORY
  -------------------------------------+
  
  +--OPTIONAL--------------------------------------+
  The ORDER_CLUSTER_BHV subparameter of VUU030/ULOPTNS parmlib
  parameter allows to set the default behavior desired for the
  ORDER CLUSTER feature. If ORDER_CLUSTER_BHV is not specified,
  the MANDATORY value is assumed.
  When DB2 YES applies:
  Db2 HPU removes any occurrence of ORDER CLUSTER, OPTIONAL from
  the SELECT statement whenever it is passed to Db2.
  When ORDER CLUSTER or ORDER CLUSTER, MANDATORY is specified for
  an unsupported SELECT statement, Db2 HPU stops with completion
  code 8 (this is the current behavior for ORDER CLUSTER).
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Db2 High Performance Unload         *
  *                 for z/OS                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: The ORDER CLUSTER feature cannot be     *
  *                      honored when a logical unload is        *
  *                      processed in SQL mode. This leads Db2   *
  *                      HPU to reject a logical unload which    *
  *                      processing requires the SQL mode        *
  *                      (either because the involved SELECT     *
  *                      statement is not supported or because   *
  *                      DB2 FORCE is specified) by not          *
  *                      unloading the data and to end with      *
  *                      completion code of 8.                   *
  *                                                              *
  *                      For automatic generation of UNLOAD      *
  *                      statements, as determining what SELECT  *
  *                      statement Db2 HPU supports in native    *
  *                      mode by program is complex and might    *
  *                      change along with the maintenance, the  *
  *                      use of the DB2 YES option allows to     *
  *                      request Db2 HPU to choose the relevant  *
  *                      processing mode. Specifying the ORDER   *
  *                      CLUSTER option is also a convenient way *
  *                      to get data in the preferred sequence   *
  *                      when the data is to be reloaded into    *
  *                      the tables with the same clustering     *
  *                      index.                                  *
  *                                                              *
  *                      When both DB2 YES and ORDER CLUSTER     *
  *                      option are specified, if the SELECT     *
  *                      statement is not supported by Db2 HPU   *
  *                      for native processing, the unload       *
  *                      fails. If the ORDER CLUSTER is only a   *
  *                      preference for performance but not an   *
  *                      actual requirement, it would be useful  *
  *                      to allow Db2 HPU to ignore the ORDER    *
  *                      CLUSER option whenever not applicable.  *
  ****************************************************************
  * RECOMMENDATION: Apply the PTF.                               *
  ****************************************************************
  ignore ORDER CLUSTER when switching to SQL mode
  

Problem conclusion

• Code has been changed to fix this problem.
  

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:

Modules/Macros

• INZ$$PRM INZTDSC  INZTVAR  INZUTIB1 INZUTIB2 INZUTILC INZUTILK
  INZUTILL
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R510 PSY UI68215

     UP20/03/18 P F003

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.