PH65605: INCORRECT OR INCORRECTLY ORDERED DATA FOR AN UNLOAD WITH DAYOFWEEK(COMPLEX_EXPRESSION) RUN IN NATIVE MODE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A logical unload which SELECT statement specifies an SQL
  expression that involves the DAYOFWEEK scalar function unloads
  incorrect data.
  
  This occurs if all the following conditions are fulfilled:
  - the logical unload is run in native mode
  - the SELECT statement uses an SQL expression involving the
    DAYOFWEEK(complex_expression) where complex expression
    involving more than one operator or scalar function (e.g.
    DATE("2012-1" || "2-12") is a complex expression whereas
    DATE("2012-12-12") is not)
  
  Depending on where the affected expression is used, the
  evaluation issue can lead to one or more of the following
  symptoms:
  - an incorrect content of the field unloaded, if the affected
    expression is one of the target columns of the SELECT
    statement
  - the unloaded rows are not the expected ones if the affected
    expression is used in a WHERE clause
  - the unloaded data is not correctly order if the affected
    expression is used in an ORDER BY clause
  

Local fix

• run the unload in SQL mode by specifying the DB2 FORCE option.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Db2 High Performance Unload for     *
  *                 z/OS                                         *
  ****************************************************************
  * PROBLEM DESCRIPTION: A logical unload which SELECT           *
  *                      statement specifies an SQL expression   *
  *                      that involves the DAYOFWEEK scalar      *
  *                      function unloads incorrect data.        *
  *                                                              *
  *                      This occurs if all the following        *
  *                      conditions are fulfilled:               *
  *                      - the logical unload is run in native   *
  *                        mode                                  *
  *                      - the SELECT statement uses an SQL      *
  *                        expression involving the              *
  *                        DAYOFWEEK(complex_expression) where   *
  *                        complex expression involving more     *
  *                        than one operator or scalar function  *
  *                        (e.g. DATE("2012-1" || "2-12") is a   *
  *                        complex expression whereas            *
  *                        DATE("2012-12-12") is not)            *
  *                                                              *
  *                      Depending on where the affected         *
  *                      expression is used, the evaluation      *
  *                      issue can lead to one or more of the    *
  *                      following symptoms:                     *
  *                      - an incorrect content of the field     *
  *                        unloaded, if the affected expression  *
  *                        is one of the target columns of the   *
  *                        SELECT statement                      *
  *                      - the unloaded rows are not the         *
  *                        expected ones if the affected         *
  *                        expression is used in a WHERE clause  *
  *                      - the unloaded data is not correctly    *
  *                        order if the affected expression is   *
  *                        used in an ORDER BY clause            *
  *                                                              *
  *                      Local fix: run the unload in SQL mode   *
  *                      by specifying the DB2 FORCE option.     *
  ****************************************************************
  * RECOMMENDATION: Apply the PTF                                *
  ****************************************************************
  Incorrect or incorrectly ordered data for an unload with
  DAYOFWEEK(complex_expression) run in native 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

• INZUTILK
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R510 PSY UO02741

     UP25/04/09 P F504

• R520 PSY UO02742

     UP25/04/09 P F504

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.