OLEDB for DB2 driver returns DBTYPE_DECIMAL even if precision is greater than 29

View products that this article applies to.

Symptoms

When you insert a DECIMAL Data Type value with Microsoft SQL Server 2000 by means of a linked Server using the Microsoft OLEDB Provider for DB2 (which is included with Microsoft Host Integration Server 2000 or Microsoft Host Integration Server 2000 with Service Pack 1), the data field might show incorrect values.

↑ Back to the top

Cause

The Microsoft OLEDB Provider for DB2 returns a Data Type of DBTYPE_DECIMAL even if the precision of the field is specified greater than 28 on the target database.

↑ Back to the top

Resolution

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft Host Integration Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

328152� How to obtain the latest service pack for Host Integration Server 2000

Hotfix information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

   Date         Time   Version      Size     File name
   ------------------------------------------------------
   07-Mar-2003  12:30  5.0.0.900    672,016  Db2oledb.dll

Note Because of file dependencies, the most recent fix that contains the preceding files may also contain additional files.

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in Host Integration Server 2000 and Host Integration Server 2000 Service Pack 1.

This problem was corrected in Microsoft Host Integration Server 2000 Service Pack 2.

↑ Back to the top

More information

This problem was experienced against a DB2 RDBM on the mainframe. According to the IBM DB2 manual "DB2 for OS/390 Version 5: SQL Reference", chapter 3.7.3.5, the maximum precision of a DECIMAL can be up to 31 digits.

This causes the OLE DB Provider for DB2 to return a data type DBTYPE_DECIMAL with a precision of 31 because it is defined that way on the host. However, this value is larger than a DECIMAL data type can be according to our OLEDB specification where the maximum precision is supposed to be 29. The OLEDB specification states the following:

DBTYPE_DECIMAL 0 to 28 
DBTYPE_NUMERIC 0 to 38 
DBTYPE_VARNUMERIC -128 to 127 

Type indicator Maximum precision 

DBTYPE_DECIMAL 29 
DBTYPE_NUMERIC 38 
DBTYPE_VARNUMERIC 255

See also the documentation at the following MSDN Web site:

Precision of Numeric Data Types
http://msdn2.microsoft.com/en-us/library/ms715867.aspx

The hotfix that is described earlier in this article changed the DBTYPE for all the decimal fields with a precision of greater than 28 from DBTYPE_DECIMAL to DBTYPE_NUMERIC.

IMPORTANT: Starting with IBM DB2 UDB version 8, fields that are described as NUMERIC and DECIMAL are both being returned from DB2 UDB as type DECIMAL. With the hotfix described in this article applied, this means that the maximum precision of both NUMERIC and DECIMAL data types when using DB2 UDB version 8 or later is 28. This is true only when using ADO Client side cursors. With the hotfix applied, if you are using ADO Client side cursors and DB2 UDB version 8 and you try to access a NUMERIC or DECIMAL field with a precision larger than 28, you will receive the following error:

Data descriptor mismatch. SQLSTATE: HY000, SQLCODE: -292

↑ Back to the top