JR24777: SQL SERVER cannot handle both PACKED DECIMAL and ZONED DECIMAL when using the IBM OLE DB Provider

DB2 Version 9.1 Fix Pack 4a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 3   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 4 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 3a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• The CLI function - SQLColumns, and the IBM OLE DB Provider
  function - IDBSchemaRowset, report PACKED DECIMAL fields as
  DECIMAL and ZONED DECIMAL FIELDS as NUMERIC. This is as
  designed. However, IColumnsInfo reports both as Decimal. This is
  because it calls SQLDescribe internally, which returns
  SQL_DECIMAL for both data types.
  
  The problem comes when SQL Server - using the IBM OLE DB
  Provider - fetches data from another data source. It cross
  checks the data types from IColumnsInfo and IDBSchemaRowset. If
  they don't match, it reports an error like:
    OLE DB provider 'IBMDADB2' supplied inconsistent metadata for
    a column.
    Metadata information was changed at execution time.
    OLE DB error trace [Non-interface error: Column 'COL_A'
    (compile-time ordinal 1) of object '"DBNAME.SCHEMA.TABLE"' was
    reported to have a DBTYPE of 131 at compile time and 14 at run
    time].
  
  By default, the PACKED DECIMAL columns are fine, but the ZONED
  DECIMAL columns generate the above error.
  
  You can use the MapDecimalToNumeric=1 parameter on the
  connection string. This will stop ZONED DECIMAL columns from
  generating an error, but PACKED DECIMAL columns will now report
  a similar error - DBTYPE of 14 at compile time and 131 at run
  time.
  

Local fix

• Local Fix: None
  
  Work Around: Create a VIEW on DB2 for iSeries that CASTs the
  ZONED DECIMAL to PACKED DECIMAL and select from this VIEW
  instead.
  

Problem summary

• ERROR DESCRIPTION:
  The CLI function - SQLColumns, and the IBM OLE DB Provider
  function - IDBSchemaRowset, report PACKED DECIMAL fields as
  DECIMAL and ZONED DECIMAL FIELDS as NUMERIC. This is as
  designed. However, IColumnsInfo reports both as Decimal. This is
  because it calls SQLDescribe internally, which returns
  SQL_DECIMAL for both data types.
  
  The problem comes when SQL Server - using the IBM OLE DB
  Provider - fetches data from another data source. It cross
  checks the data types from IColumnsInfo and IDBSchemaRowset. If
  they don't match, it reports an error like:
    OLE DB provider 'IBMDADB2' supplied inconsistent metadata for
    a column.
    Metadata information was changed at execution time.
    OLE DB error trace [Non-interface error: Column 'COL_A'
    (compile-time ordinal 1) of object '"DBNAME.SCHEMA.TABLE"' was
    reported to have a DBTYPE of 131 at compile time and 14 at run
    time].
  
  By default, the PACKED DECIMAL columns are fine, but the ZONED
  DECIMAL columns generate the above error.
  
  You can use the MapDecimalToNumeric=1 parameter on the
  connection string. This will stop ZONED DECIMAL columns from
  generating an error, but PACKED DECIMAL columns will now report
  a similar error - DBTYPE of 14 at compile time and 131 at run
  time.
  

Problem conclusion

• Problem first fixed in DB2 v9 FP3 (s070719)
  

Temporary fix

• Local Fix:
  Local Fix: None
  

Comments

APAR Information

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

  JR24776

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

Fix information

• Fixed component name

  DB2 CUE WINDOWS

• Fixed component ID

  5724B6201

Applicable component levels

• R910 PSY

     UP