Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Data Type Mapping

SQL Server 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) (http://go.microsoft.com/fwlink/?LinkId=241519) topic in SQL Server Books Online.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.