PH48523: INCORRECT UNLOADED DATA IF SQL EXPRESSION EVALUATION INVOLVES DATETIME VALUE WITH NON COMPLIANT YEAR PART

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A logical unload produces incorrect data (invalid or wrong
  value) instead of stopping to report that an invalid datetime
  value has been handled when evaluating an SQL expression.
  This occurs if all the following conditions are fulfilled:
  - the logical unload is processed in native mode
  - the logical unload specifies an SQL statement with expressions
  which evaluation requires handling a datetime value with an
  invalid year part of 0000 which occurs if:
     - case 1) the SQL expression specifies an invalid datetime
  constant such as in the following examples:
  TIMESTAMP('0000-01-01-00.00.00.000000')
     - case 2) the intermediary or final result of the SQL
  expression would produce a datetime value with a year part of
  0000 such as in the following examples:
  TIMESTAMP('0001-01-01-00.00.00.000000') - 1 DAY or
  TIMESTAMP('0001-01-01-00.00.00.000000') - 5 YEAR
  while DB2 returns an SQL error when processing the same SQL
  expression:
     - case 1)
  DSNT408I SQLCODE = -181, ERROR: THE STRING REPRESENTATION OF A
  DATETIME VALUE
           IS NOT A VALID DATETIME VALUE
     - case 2)
  SQLCODE = -183, ERROR: AN ARITHMETIC OPERATION ON A DATE OR
  TIMESTAMP HAS A RESULT THAT IS NOT WITHIN THE VALID RANGE OF
  DATES
  

Local fix

• Run the unload in SQ 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 produces incorrect     *
  *                      data (invalid or wrong value) instead   *
  *                      of stopping to report that an invalid   *
  *                      datetime value has been handled when    *
  *                      evaluating an SQL expression.           *
  *                                                              *
  *                      This occurs if all the following        *
  *                      conditions are fulfilled:               *
  *                      - the logical unload is processed in    *
  *                        native mode                           *
  *                      - the logical unload specifies an SQL   *
  *                        statement with expressions which      *
  *                        evaluation requires handling a        *
  *                        datetime value with an invalid year   *
  *                        part of 0000 which occurs if:         *
  *                          - case 1) the SQL expression        *
  *                            specifies an invalid datetime     *
  *                            constant such as in the following *
  *                            examples:                         *
  *                      TIMESTAMP('0000-01-01-00.00.00.000000') *
  *                          - case 2) the intermediary or final *
  *                            result of the SQL expression      *
  *                            would produce a datetime value    *
  *                            with a year part of 0000 such as  *
  *                            in the following examples:        *
  *                      TIMESTAMP('0001-01-01-00.00.00.000000') *
  *                       - 1 DAY or                             *
  *                      TIMESTAMP('0001-01-01-00.00.00.000000') *
  *                       - 5 YEAR                               *
  *                            while DB2 returns an SQL error    *
  *                            when processing the same SQL      *
  *                            expression:                       *
  *                            - case 1)                         *
  *                      DSNT408I SQLCODE = -181, ERROR: THE     *
  *                      STRING REPRESENTATION OF A DATETIME     *
  *                      VALUE IS NOT A VALID DATETIME VALUE     *
  *                            - case 2)                         *
  *                      SQLCODE = -183, ERROR: AN ARITHMETIC    *
  *                      OPERATION ON A DATE OR TIMESTAMP HAS A  *
  *                      RESULT THAT IS NOT WITHIN THE VALID     *
  *                      RANGE OF DATES                          *
  ****************************************************************
  * RECOMMENDATION: Apply the PTF                                *
  ****************************************************************
  Incorrect unloaded data if SQL expression evaluation involves
  datetime value with non compliant year part.
  

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

• INZCHECK INZCRX10 INZCRXSM INZCRXSQ INZTPMEM INZUTILK
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R510 PSY UI83089

     UP22/11/05 P F211

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.