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

Obtain the fix for this APAR.

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

• RB10 PSY UI63648

     UP19/06/29 P F906 Ž

• RC10 PSY UI63647

     UP19/06/28 P F906 Ž

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.