IBM Support

PI60480: OFFLOADING QUERY SUBSTRACTING DATES FAIL WITH SQLCODE181 AND GETIDAA ERROR: BAD DATE EXTERNAL REPRESENTATION '120308'

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Offloading query substracting dates fail with sqlcode181 and get
    IDAA ERROR:  Bad date external representation '120308'
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 11 for z/OS users that use datetime      *
    *                 arithmetic on the analytics accelerator      *
    ****************************************************************
    * PROBLEM DESCRIPTION: A SQL statement with datetime           *
    *                      arithmetic operations inside the        *
    *                      scalar functions DAYOFWEEK(),           *
    *                      DAYOFWEEK_ISO(), DAYOFYEAR(),           *
    *                      YEAR(), WEEK_ISO(), MONTH(),            *
    *                      QUARTER() fails with                    *
    *                      SQLCODE = -181                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    A SQL statement with the scalar functions
    DAYOFWEEK(), DAYOFWEEK_ISO(), DAYOFYEAR(),
    YEAR(), WEEK_ISO(), MONTH(), QUARTER(), and datetime
    subtraction is converted into a combination which is not
    supported by the accelerator.
    
    Additional Keywords:
    IDAAV4R1/K IDAAV5R1/K SQLYEAR SQLCODE181
    

Problem conclusion

  • A fix has been implemented to detect the combination of
    the affected functions and datetime subtraction to correct
    the converted query in this case.
    

Temporary fix

Comments

  • ×**** PE17/09/21 FIX IN ERROR. SEE APAR PI87661  FOR DESCRIPTION
    

APAR Information

  • APAR number

    PI60480

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-04-08

  • Closed date

    2016-07-18

  • Last modified date

    2017-10-19

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

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

    UI39472

Modules/Macros

  •    DSNXONZS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI39472

       UP16/08/03 P F608

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.



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PI60480

Modified date: 19 October 2017


Translate this page: