Data Type Mapping

Article
11/26/2012

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.