PK47506: FAST UNLOAD ORDER-BY NOT BEING RECOGNIZED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Fast Unload ORDER-BY (with a dash) receives SQLCODE -104. This
  only occurs in HPU 3.1.
  This can be circumvented by coding ORDER BY (without the dash)
  in the SELECT which is also supported by Fast Unload syntax.
  PB 5477
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 High Performance Unload.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: For 220 and 310:                        *
  *                                                              *
  *                      1. Second part of fix (first part is    *
  *                      provided by APAR PK46586)               *
  *                      A user abend U1009 occurs if HPU        *
  *                      process natively an ORDER BY with a     *
  *                      sort key longer than 4088 bytes.        *
  *                      Due to SORT limitation, cannot process  *
  *                      natively such an ORDER BY.              *
  *                      In this case, HPU will try to use DB2   *
  *                      access to process the corresponding     *
  *                      SELECT.                                 *
  *                      - if DB2 YES is specified, HPU will     *
  *                       issue message INZU063I UNSUPPORTED     *
  *                       SELECT and the select will be          *
  *                       processed by DB2,                      *
  *                      - if DB2 NO is specified, HPU will      *
  *                       issue an error message INZU040E with   *
  *                       a RC=8.                                *
  *                      PB 8866 additionally see 1a             *
  *                                                              *
  *                      For 310:                                *
  *                                                              *
  *                      2. FastUnload syntax: The ORDER-BY      *
  *                      keyword is not recognized as a synonym  *
  *                      of ORDER BY. HPU finishes with RC=8 and *
  *                      an SQLCODE -104                         *
  *                      PB 5477                                 *
  *                      3. An Abend U1113 occurs when unloading *
  *                      in DB2 V9 a table on wich a column with *
  *                      a FIELDPROC has been added by an ALTER. *
  *                      PB 8861                                 *
  *                      4. The files that are allocated using   *
  *                      a TEMPLATE are not unallocated when     *
  *                      they are no longer used. Therefore they *
  *                      are not available for allocation until  *
  *                      the HPU step is finished. An other      *
  *                      consequence is that the TIOT entries    *
  *                      can be exhausted leading to dynamic     *
  *                      allocation errors with reason code      *
  *                      0238 for new allocations.               *
  *                      PB 8893                                 *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  1. Modules inzctrl and inzmsgs were changed to determine the
  length of the sort key and decide if the sort can be processed
  internally or not.
  2. Module inzdyacc was changed to ass ORDER-BY as a synonym for
  ORDER BY.
  3. Modules udbspmfl, oddldb2 and osysibm were changed to
  process correctly this case.
  4. Modules otmplate, inzrload, inzctrlh and inzrunh were
  changed to free the datasets allocated by template as soon as
  they are no longer used.
  
  Additional Problem Description:
  1a. Additionally for 310
  - When DB2 FORCE and SORT(EXTERNAL)
  are specified, warning message
  INZU510W will be issued when the sort
  key is longer than 4088 bytes, and in
  this case the SORT(EXTERNAL) option
  will be ignored.
  PB 8866
  

Problem conclusion

• APPLY the PTF.
  

Temporary fix

Comments

•  Problem Description Amended.
  

APAR Information

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

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

  UK27209 UK27210

Modules/Macros

•    INZUTILB
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R220 PSY UK27209

     UP07/07/24 P F707

• R310 PSY UK27210

     UP07/07/24 P F707

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.