PI51547: DEFECT IN BIF_COMPATIBILITY=DECIMAL_V9_VARCHAR FOR A CAST(<DECIMAL> AS VARCHAR(N)) FOR QUERY ACCELERATION IN IDAA

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Different results are observed when CAST funtion is used
  for Query acceleration in IDAA.
  .
  When running a CAST statement on a DECIMAL column, the results
  are not the same when compared in DB2 to the result from
  accelerator.
  .
  Example:
  Source column   DECIMAL(14,0)  has value in column 12345
  SELECT CAST(col as VARCHAR(10))
  returns 12345.0 from the appliance,
  whereas in DB2 it returns 12345.
  

Local fix

• A workaround is to use VARCHAR(col), which has proper IDAA
  support for BIF_COMPATIBILITY=DECIMAL_V9_VARCHAR.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 for z/OS who use            *
  *                 query acceleration.                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect results can be returned from  *
  *                      an accelerator when subsystem parameter *
  *                      BIF_COMPATIBILITY is set to             *
  *                      V9_DECIMAL_VARCHAR and the SQL          *
  *                      statement contains a CAST on a          *
  *                      decimal expression as a character or    *
  *                      varying character data type.            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Incorrect results can be returned from an accelerator when
  subsystem parameter BIF_COMPATIBILITY is set to
  V9_DECIMAL_VARCHAR and the SQL statement contains a CAST on a
  decimal expression as a character or varying character data
  type.
  
  For example, column C1 is a DECIMAL(14,0) and the value in the
  column is 12345
  
  The following query returns 12345. with the decimal point from
  DB2.
  
  The same query returns 12345.0 with both the decimal point
  and trailing 0 from the accelerator.
  
  SELECT CAST(C1 AS VARCHAR(10)) FROM T1;
  
  Keywords:
  IDAAV4R1/K IDAAV5R1/K
  SQLINCORROUT SQLINCORR INCORROUT DB2INCORR/K
  

Problem conclusion

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

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:

  UI33719 UI33720

Modules/Macros

• DSNXONZQ DSNXONZS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI33719

     UP15/12/29 P F512

• RB10 PSY UI33720

     UP15/12/29 P F512

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.