Data Type Mapping
This topic describes all data type mappings to OLE DB data types.
DB2 to OLE DB Data Type Mapping
The following table describes DB2 data type mappings to OLE DB data types.
OLE DB data type |
DB2 data type |
Description |
DBTYPE_I8 |
Bigint |
A big integer (BIGINT) is an 8-byte binary integer. |
DBTYPE_Bytes |
BLOB |
A binary large object (BLOB) is a varying-length string used to store non textual or binary data. |
DBTYPE_Bytes |
Char() for BIT Data |
A character () for bit data is a fixed-length binary string containing character data. |
DBTYPE_STR |
Char |
A character is a fixed-length SBCS or MBCS string. |
DBTYPE_WSTR |
Char |
A Unicode character is a fixed-length MBCS string. |
DBTYPE_STR |
CLOB |
A varying-length character large object (CLOB) is a varying-length string. The maximum length of the string depends on the DB2 platform and version. |
DBTYPE_Data |
Date |
A date is a 10-byte string. |
DBTYPE_Decimal |
Decimal |
A decimal is a packed decimal number. |
DBTYPE_R8 |
Double |
A double is an 8-byte double-precision floating point number. |
DBTYPE_R8 |
Float |
A float is an 8-byte double-precision floating point number. |
DBTYPE_WSTR |
Graphic |
A graphic is a fixed-length DBCS only string. |
DBTYPE_I4 |
Integer |
An integer is a 4-byte binary integer. |
DBTYPE_Numeric |
Numeric |
A numeric is a packed decimal number. |
DBTYPE_I2 |
Smallint |
A small integer (SMALLINT) is a two-byte binary integer. |
DBTYPE_R4 |
Real |
A real is a 4-byte single-precision floating point number. |
DBTYPE_DBTime |
Time |
A time is an 8-byte time string. |
DBTYPE_DBTimesStamp |
TimesStamp |
A timestamp is a 26-byte string representing the date, time, and microseconds. |
DBTYPE_Bytes |
Varchar() For BIT Data |
A varying character () for bit data is a varying-length binary string containing character data. |
DBTYPE_STR |
Varchar |
A varying character is a varying-length SBCS or MBCS character string. |
DBTYPE_WSTR |
Varchar |
A varying character Unicode varchar is a varying-length Unicode string. |
DBTYPE_WSTR |
VarGraphic |
A varying graphic is a varying-length DBCS only string. |
Data Types by DB2 Platform
Schema information in OLE DB is retrieved using predefined schema rowsets with IDBSchemaRowset::GetRowset. The Data Provider exposed the PROVIDER_TYPES Rowset to indicate the DB2 to OLE DB data type support (types, mappings, limits), based on the IBM DB2 platform and version.
DB2 for z/OS V9R1
The Data Provider supports accessing these data types when connected to DB2 for z/OS (based on V9R1).
DB2 Type_name |
OLE DB data_type |
Column_size |
Minimum_scale |
Maximum_scale |
Smallint |
DBType_12 |
5 |
||
Integer |
DBType_14 |
10 |
||
Bigint |
DBType_18 |
19 |
||
Real |
DBType_R4 |
7 |
||
Float |
DBType_R8 |
15 |
||
Double |
DBType_ R8 |
15 |
||
Decimal |
DBType_ Decimal |
31 |
0 |
31 |
Graphic |
DBType_ WSTR |
127 |
||
VarGraphic |
DBType_ WSTR |
16336 |
||
Char |
DBType_STR |
254 |
||
Varchar |
DBType_STR |
32672 |
||
Char |
DBType_WSTR |
254 |
||
Varchar |
DBType_ WSTR |
32672 |
||
Char() for BIT data |
DBType_ Bytes |
254 |
||
Varchar() for BIT data |
DBType_ Bytes |
32672 |
||
Numeric |
DBType_Numeric |
31 |
0 |
31 |
Date |
DBType_ DBDate |
10 |
||
Time |
DBType_DBTime |
8 |
||
TimeStamp |
DBType_ TimesStamp |
26 |
||
BLOB |
DBType_ Bytes |
2147483647 |
||
CLOB |
DBType_STR |
2147483647 |
DB2 for i5/OS V6R1
The Data Provider supports accessing these data types when connected to DB2 for i5/OS (based on V6R1).
DB2 Type_name |
OLE DB data_type |
Column_size |
Minimum_scale |
Maximum_scale |
Smallint |
DBType_12 |
5 |
||
Integer |
DBType_14 |
10 |
||
Bigint |
DBType_18 |
19 |
||
Real |
DBType_R4 |
7 |
||
Float |
DBType_R8 |
15 |
||
Double |
DBType_ R8 |
15 |
||
Decimal |
DBType_ Decimal |
31 |
0 |
31 |
Graphic |
DBType_ WSTR |
127 |
||
VarGraphic |
DBType_ WSTR |
16336 |
||
Char |
DBType_STR |
254 |
||
Varchar |
DBType_STR |
32672 |
||
Char |
DBType_WSTR |
254 |
||
Varchar |
DBType_ WSTR |
32672 |
||
Char() for BIT data |
DBType_ Bytes |
254 |
||
Varchar() for BIT data |
DBType_ Bytes |
32672 |
||
Numeric |
DBType_Numeric |
31 |
0 |
31 |
Date |
DBType_ DBDate |
10 |
||
Time |
DBType_DBTime |
8 |
||
TimeStamp |
DBType_ TimesStamp |
26 |
||
BLOB |
DBType_ Bytes |
2147483647 |
||
CLOB |
DBType_STR |
2147483647 |
DB2 for LUW V9.7
The Data Provider supports accessing these data types when connected to DB2 for LUW (based on V9.7).
DB2 Type_name |
OLE DB data_type |
Column_size |
Minimum_scale |
Maximum_scale |
Smallint |
DBType_12 |
5 |
||
Integer |
DBType_14 |
10 |
||
Bigint |
DBType_18 |
19 |
||
Real |
DBType_R4 |
7 |
||
Float |
DBType_R8 |
15 |
||
Double |
DBType_ R8 |
15 |
||
Decimal |
DBType_ Decimal |
31 |
0 |
31 |
Graphic |
DBType_ WSTR |
127 |
||
VarGraphic |
DBType_ WSTR |
16336 |
||
Char |
DBType_STR |
254 |
||
Varchar |
DBType_STR |
32672 |
||
Char |
DBType_WSTR |
254 |
||
Varchar |
DBType_ WSTR |
32672 |
||
Char() for BIT data |
DBType_ Bytes |
254 |
||
Varchar() for BIT data |
DBType_ Bytes |
32672 |
||
Numeric |
DBType_Numeric |
31 |
0 |
31 |
Date |
DBType_ DBDate |
10 |
||
Time |
DBType_DBTime |
8 |
||
TimeStamp |
DBType_ TimesStamp |
26 |
||
BLOB |
DBType_ Bytes |
2147483647 |
||
CLOB |
DBType_STR |
2147483647 |
SQL Server Integration Services
When using the SQL Server Integration Services Import and Export Wizards from the Microsoft SQL Server Management Studio, you can customize the default data conversions by editing the XML mapping files. The XML files are located at ate C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles.
SQL Server Replication Services
SQL Server Replication may convert data incorrectly, based on the default mappings from SQL Server to DB2 data types. We recommend that the administrator and developer review and revise the Replication data type mappings using the following SQL Server system stored procedures.
sp_helpdatatypemap
sp_getdefaultdatatypemapping
sp_setdefaultdatatypemapping
For more information, see the System Stored Procedures (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=241519) topic in SQL Server Books Online.