IBM Support

PH12094: SQL STATEMENT WITH CAST TO INTEGER DOES NOT WORK ON IDAA V7, IT RETURNS INCORROUT. SAME STATEMENT WORKS IN DB2 AND IDAA V5.

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

APAR status

  • Closed as program error.

Error description

  • SQL statement with CAST to integer does not work on IDAA V7, it
    return incorrout. Same statement work in Db2 and IDAA V5.
    Additional keywords: IDAAV7R1/K
    
    Sample:
    
    create table tb1
     ( col01 decimal ( 8 , 0 ) )
    ;
    insert into tb1 values ( 12345678.0 )
    ;
    insert into tb1 values ( 87654321.0 )
    ;
    set current query acceleration all
    ;
    select cast(cast("TB1"."COL01" as integer) as varchar(8))
     from tb1
    ;
    
    IDAA V7 result:
    
     1
     --
     ##
     ##
    
    IDAA V5 and Db2 result as expected:
    
     1
     --------
     12345678
     87654321
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All Db2 11/12 for z/OS users who use IBM     *
    *                 Db2 Analytics Accelerator V7.                *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect output might be returned when *
    *                      offloading a query with mulitple CAST   *
    *                      functions to IBM Db2 Analytics          *
    *                      Accelerator V7.                         *
    ****************************************************************
    * RECOMMENDATION: Apply corrective PTF when available.         *
    ****************************************************************
    When offloading a query with multiple levels of CAST
    functions to IBM Db2 Analytics Accelerator V7, incorrect
    output might occur. The incorrect output occurs when the
    source of the inner CAST function is DECIMAL, and the target
    of the upper CAST function is CHAR/VARCHAR.
       For example:
           create table tb1
            ( col01 decimal ( 8 , 0 ) );
    
           set current query acceleration = all;
           select cast(cast("TB1"."COL01" as integer) as varchar(8))
           from tb1 for fetch only;
    
    Additional Keywords:
        IDAAV7R1/K  SQLCAST  SQLDECIMAL  SQLCHAR SQLVARCHAR
        SQLINCORR SQLINCORROUT INCORROUT DB2INCORR/K
    

Problem conclusion

  • Queries with multiple CAST functions on decimal data type
    are now able to be successfully offloaded to IBM Db2
    Analytics Accelerator V7.
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PH12094

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-05-15

  • Closed date

    2019-06-13

  • Last modified date

    2019-06-13

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

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

    UI63647 UI63648

Modules/Macros

  •    DSNXOBAQ
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels



Document information

More support for: DB2 for z/OS

Software version: 11.0

Reference #: PH12094

Modified date: 13 June 2019