Microsoft KB Archive/816374

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

Article ID: 816374

Article Last Modified on 12/30/2006

-

APPLIES TO


 * Microsoft Host Integration Server 2000 Standard Edition
 * Microsoft Host Integration Server 2000 Service Pack 1

-





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.



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.



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.



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.



MORE INFORMATION
This problem was experienced against a DB2 RDBM on the mainframe. According to the IBM DB2 manual &quot;DB2 for OS/390 Version 5: SQL Reference&quot;, 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

Keywords: kbqfe kbfix kbbug KB816374

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.