PH67432: SORT(EXTERNAL) UNDULY IGNORED OR IGNORED WITH NO EXPLANATION MESSAGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The outcome of the SORT(EXTERNAL) option is not correctly
  handled for a logical unload it applies to. The symptoms and
  related root causes are detailed below.
  
  #1) The SORT process is undesirably performed through DB2
  instead of the unload step (as visible from the lack of writing
  of the UTPRTnnn datasets) despite the SELECT statement being
  eligible to the SORT(EXTERNAL) feature. This happens when the
  ORDER BY clause is followed by additional clauses.
  
  #2) No message is issued to explain why the SORT(EXTERNAL)
  option is not honored. This can happen because either of the
  following conditions applies:
  - The SORT process is unduly performed through DB2 despite the
  SELECT statement being eligible for SORT(EXTERNAL). This symptom
  is an extension of the behavior observed in symptom #1
  - The logical unload is not eligible for the SORT(EXTERNAL)
  feature, as DB2 HPU could not determine the list of the columns
  involved in the ORDER BY clause (e.g. because the ORDER BY
  clause specifies qualified column names or SQL expressions)
  

Local fix

• #1) For SELECT statements that would be eligible to
  SORT(EXTERNAL), no local fix is possible.
  Please note: the output data is not affected by this behavior.
  The data will still be sorted according to the given criteria.
  
  #2) For ineligible SELECT statements, remove the unnecessary
  SORT(EXTERNAL) clause.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Db2 High Performance Unload for     *
  *                 z/OS                                         *
  ****************************************************************
  * PROBLEM DESCRIPTION: The outcome of the SORT(EXTERNAL)       *
  *                      option is not correctly handled for a   *
  *                      logical unload it applies to. The       *
  *                      symptoms and related root causes are    *
  *                      detailed below.                         *
  *                                                              *
  *                                                              *
  *                      #1) The SORT process is undesirably     *
  *                      performed through DB2 instead of the    *
  *                      unload step (as visible from the lack   *
  *                      of writing of the UTPRTnnn datasets)    *
  *                      despite the SELECT statement being      *
  *                      eligible to the SORT(EXTERNAL) feature. *
  *                      This happens when the ORDER BY clause   *
  *                      is followed by additional clauses.      *
  *                                                              *
  *                                                              *
  *                                                              *
  *                      #2) No message is issued to explain     *
  *                      why the SORT(EXTERNAL) option is not    *
  *                      honored. This can happen because either *
  *                      of the following conditions applies:    *
  *                                                              *
  *                        -  The SORT process is unduly         *
  *                           performed through DB2 despite the  *
  *                           SELECT statement being eligible    *
  *                           for SORT(EXTERNAL). This symptom   *
  *                           is an extension of the behavior    *
  *                           observed in symptom #1             *
  *                                                              *
  *                        -  The logical unload is not eligible *
  *                           for the SORT(EXTERNAL) feature, as *
  *                           DB2 HPU could not determine the    *
  *                           list of the columns involved in    *
  *                           the ORDER BY clause (e.g. because  *
  *                           the ORDER BY clause specifies      *
  *                           qualified column names or SQL      *
  *                           expressions)                       *
  *                                                              *
  *                      Local fix:                              *
  *                                                              *
  *                      #1) For SELECT statements that would    *
  *                      be eligible to SORT(EXTERNAL), no local *
  *                      fix is possible.                        *
  *                                                              *
  *                          Please note: the output data is not *
  *                          affected by this behavior. The      *
  *                          data will still be sorted according *
  *                           to the given criteria.             *
  *                                                              *
  *                                                              *
  *                      #2) For ineligible SELECT statements,   *
  *                      remove the unnecessary SORT(EXTERNAL)   *
  *                      clause.                                 *
  ****************************************************************
  * RECOMMENDATION: Apply the PTF                                *
  ****************************************************************
  SORT(EXTERNAL) unduly ignored or ignored with no explanation
  message
  

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

• INZUTILK
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R520 PSY UO04514

     UP25/08/23 P F508

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.