IBM Support

PI08138: RTRIM / LTRIM SCALAR FUNCTION ON ACCELERATED QUERY IS ADDING .0 TO THE RESULT IF COLUMN IS DEFINED AS DECIMAL

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • RTRIM / LTRIM scalar function on accelerated query is adding .0
    to the result if column is defined as decimal
    
    sample:
    
    CREATE TABLE TB21783
     ( col01 decimal(20)
     )
    ;
    INSERT INTO TB21783 values (123)
    ;
    
    ==> add table to accelerator and load it <==
    
    --run query on accelerator / IDAA
    SET CURRENT QUERY ACCELERATION = ALL
    ;
    SELECT RTRIM(COL01) FROM TB21783
     FOR READ ONLY WITH UR
    ;
    => 123.0
    
    SELECT LTRIM(COL01) FROM TB21783
     FOR READ ONLY WITH UR
    ;
    => 123.0
    
    --run query on DB2
    SET CURRENT QUERY ACCELERATION = NONE
    ;
    SELECT RTRIM(COL01) FROM TB21783
     FOR READ ONLY WITH UR
    ;
    => 123
    

Local fix

  • do not offload the query to the accelerator
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 10 for z/OS or DB2 11 for   *
    *                 z/OS who use IBM DB2 Analytics Accelerator.  *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect results or SQLCODE -904 are   *
    *                      returned when a query is executed on    *
    *                      an accelerator and contains an          *
    *                      implicit cast of a DECIMAL literal or   *
    *                      column as VARCHAR.                      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Incorrect results are returned when a query is executed on an
    accelerator and contains an implicit cast of a DECIMAL datatype
    as VARCHAR.
    For example:
    CREATE TABLE TB21783
     ( COL01 DECIMAL(20)
     );
    INSERT INTO TB21783 VALUES (123);
    
    SELECT RTRIM(COL01) FROM TB21783
     FOR READ ONLY WITH UR;
    
    When the above query executes on an accelerator, the incorrect
    value 123.0 is returned.  On DB2 the correct value 123 is
    returned. An implicit cast is added by DB2 to COL01.
    
    For static SQL queries that have been bound for acceleration
    and encounter any of the issues above, after applying this
    PTF, REBIND the package for the failing query.
    
    Additional Keywords:
    IDAAV3R1/K IDAAV4R1/K SQLINCORR SQLINCORROUT INCORROUT
    DB2INCORR/K SQLDECIMAL SQLCAST SQLVARCHAR SQLCODE904
    

Problem conclusion

  • Code is fixed so that the correct results are returned from
    the accelerator.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PI08138

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-12-17

  • Closed date

    2014-01-15

  • Last modified date

    2014-06-20

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

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

    UI14244 UI14245

Modules/Macros

  • DSNXONZQ DSNXONZS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI14244

       UP14/01/30 P F401 ½

  • RB10 PSY UI14245

       UP14/01/30 P F401 ½

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: A10

Reference #: PI08138

Modified date: 20 June 2014


Translate this page: