Data Type Mapping for Db2 for Linux, Unix and Windows
This section lists the mapping of data types for Db2 for Linux, Unix and Windows (LUW).
Db2 for LUW as Source
When Db2 for LUW is used as a source location, following is the mapping of data types in Db2 for LUW to the corresponding HVR repository data type.
| Db2 for LUW | HVR Capture Support | HVR Repository Data Type |
|---|---|---|
| bigint | Native | bigint |
| decfloat | Native | decfloat |
| decimal | Native | decimal |
| double | Native | double |
| float | Native | double |
| integer | Native | integer |
| numeric | Native | decimal |
| real | Native | real |
| smallint | Native | smallint |
| date | Native | ansidate |
| time | Native | time |
| timestamp | Native | timestamp |
| char | Native | char |
| char for bit data | Native | binary |
| clob | Native | clob |
| long varchar | Native | long varchar (db2) |
| long varchar for bit data | Native | long varbinary |
| varchar | Native | varchar |
| varchar for bit data | Native | varbinary |
| binary | Native | binary |
| blob | Native | blob |
| varbinary | Native | varbinary |
| dbclob | Native | dbclob |
| graphic | Native | graphic |
| long vargraphic | Native | long nvarchar (db2) |
| nclob | Native | dbclob |
| vargraphic | Native | vargraphic |
| xml | Native | db2 xml |
| rowid | Not supported | |
| user defined | Not supported |
Db2 for LUW as Target
When Db2 for LUW is used as a target location, following is the mapping of HVR repository data types to the corresponding data type in Db2 for LUW.
Text in green cell indicates the native data type of the DBMS
| HVR Repository Data Types | Attributes | DB2 for Linux, UNIX and Windows (UTF-8) |
|---|---|---|
| ansidate (ingres) | null=1 | date |
| ansidate | null=1 | date |
| bfile | null=1 | blob(2147483647) |
| bigint unsigned |
bytelen=8
null=1 | decimal(20) |
| bigint |
bytelen=8
null=1 | bigint |
| binary |
bytelen=10
null=1 | char(10) for bit data |
| binary_double |
bytelen=8
null=1 | double |
| binary_float |
bytelen=4
null=1 | real |
| bit (mysql) |
bitlen=32
null=1 | varchar(32) |
| bit |
bytelen=1
null=1 | smallint |
| blob | null=1 | blob(2147483647) |
| bool |
bytelen=1
null=1 | smallint |
| boolean |
bytelen=1
null=1 | smallint |
| byte varying |
bytelen=10
null=1 | varchar(10) for bit data |
| byte |
bytelen=10
null=1 | char(10) for bit data |
| byteint |
bytelen=1
null=1 | smallint |
| c |
bytelen=10
encoding=UTF-8 null=1 | char(10) |
| char |
bytelen=10
encoding=UTF-8 null=1 | char(10) |
| char |
bytelen=4000
encoding=UTF-8 null=1 | varchar(4000) |
| char |
bytelen=8000
encoding=UTF-8 null=1 | varchar(8000) |
| char |
bytelen=40
charlen=10 encoding=UTF-8 null=1 | char(40) |
| char |
bytelen=10
encoding=WINDOWS-1252 null=1 | char(20) |
| char (oracle) |
bytelen=10
encoding=UTF-8 null=1 | char(10) |
| char (oracle) |
bytelen=2000
encoding=UTF-8 null=1 | varchar(2000) |
| char (oracle) |
bytelen=40
charlen=10 encoding=UTF-8 null=1 | char(40) |
| char (oracle) |
bytelen=10
encoding=WINDOWS-1252 null=1 | char(20) |
| clob |
encoding=UTF-8
null=1 | clob(2147483647) |
| date | null=1 | timestamp(0) |
| date (hana) | null=1 | date |
| date (mysql) | null=1 | date |
| date (sybase) | null=1 | date |
| datetime (bigquery) |
prec_sec=0
null=1 | timestamp(0) |
| datetime (mysql) |
prec_sec=0
null=1 | timestamp(0) |
| datetime (sybase) | null=1 | timestamp(3) |
| datetime | null=1 | timestamp(3) |
| datetime2 |
prec_sec=0
null=1 | timestamp(0) |
| datetimeoffset |
prec_sec=0
null=1 | timestamp(0) |
| db2 timestamp with time zone |
prec_sec=0
null=1 | timestamp(0) |
| db2 xml |
encoding=UTF-8
null=1 | xml |
| dbclob |
charlen=0
null=1 | dbclob(1073741823) |
| decfloat |
prec=16
null=1 | decfloat(16) |
| decfloat |
prec=34
null=1 | decfloat(34) |
| decimal |
prec=10
scale=3 null=1 | decimal(10,3) |
| decimal |
prec=6
null=1 | decimal(6) |
| double |
bytelen=8
null=1 | double |
| epoch |
prec_sec=0
null=1 | timestamp(0) |
| float |
bytelen=8
null=1 | double |
| float4 |
bytelen=4
null=1 | real |
| float8 |
bytelen=8
null=1 | double |
| float64 |
bytelen=8
null=1 | double |
| graphic |
charlen=10
null=1 | graphic(10) |
| image | null=1 | blob(2147483647) |
| image (sybase) | null=1 | blob(2147483647) |
| ingresdate | null=1 | timestamp(0) |
| int unsigned |
bytelen=4
null=1 | bigint |
| int |
bytelen=4
null=1 | integer |
| integer |
bytelen=4
null=1 | integer |
| integer1 |
bytelen=1
null=1 | smallint |
| integer2 |
bytelen=2
null=1 | smallint |
| integer4 |
bytelen=4
null=1 | integer |
| integer8 |
bytelen=8
null=1 | bigint |
| int64 |
bytelen=8
null=1 | bigint |
| interval day to second (ingres) |
prec_sec=0
null=1 | timestamp(0) |
| interval day to second |
prec_sec=0
prec_day=0 null=1 | timestamp(0) |
| interval month to second |
prec_sec=0
null=1 | timestamp(0) |
| interval year to month (ingres) | null=1 | timestamp(0) |
| interval year to month |
prec_year=0
null=1 | timestamp(0) |
| json | null=1 | clob(2147483647) |
| jsonb | null=1 | clob(2147483647) |
| long byte | null=1 | blob(2147483647) |
| long char |
encoding=UTF-8
null=1 | clob(2147483647) |
| long nvarchar (db2) |
charlen=0
null=1 | long vargraphic |
| long nvarchar |
charlen=0
null=1 | dbclob(1073741823) |
| long raw | null=1 | blob(2147483647) |
| long varbinary | null=1 | long varchar for bit data |
| long varchar (db2) |
encoding=UTF-8
null=1 | long varchar |
| long varchar |
encoding=UTF-8
null=1 | clob(2147483647) |
| long |
encoding=UTF-8
null=1 | clob(2147483647) |
| mediumint unsigned |
bytelen=3
null=1 | integer |
| mediumint |
bytelen=3
null=1 | integer |
| money (ingres) | null=1 | decimal(14,2) |
| money | null=1 | decimal(19,4) |
| nchar |
charlen=10
null=1 | graphic(10) |
| nchar (oracle) |
bytelen=20
charlen=10 null=1 | graphic(10) |
| nclob |
charlen=0
null=1 | dbclob(1073741823) |
| ntext |
charlen=0
null=1 | dbclob(1073741823) |
| number |
scale=0
null=1 | decimal(31,4) |
| number |
prec=10
scale=-127 null=1 | double |
| number |
prec=10
scale=3 null=1 | decimal(10,3) |
| number |
prec=26
scale=0 null=1 | decimal(26) |
| number |
prec=6
scale=0 null=1 | integer |
| numeric (db2i) |
prec=10
scale=3 null=1 | decimal(10,3) |
| numeric (db2i) |
prec=6
null=1 | decimal(6) |
| numeric |
prec=10
scale=3 null=1 | decimal(10,3) |
| numeric |
prec=26
null=1 | decimal(26) |
| numeric |
prec=6
null=1 | decimal(6) |
| nvarchar |
charlen=10
null=1 | vargraphic(10) |
| nvarchar(max) |
charlen=0
null=1 | dbclob(1073741823) |
| nvarchar2 |
charlen=10
null=1 | vargraphic(10) |
| postgres date | null=1 | date |
| postgres timestamp with time zone |
prec_sec=0
null=1 | timestamp(0) |
| postgres timestamp |
prec_sec=0
null=1 | timestamp(0) |
| raw |
bytelen=10
null=1 | varchar(10) for bit data |
| real |
bytelen=4
null=1 | real |
| rowid |
bytelen=18
charlen=18 encoding=US-ASCII null=1 | char(18) |
| rowversion |
bytelen=10
null=1 | char(10) for bit data |
| smalldatetime | null=1 | timestamp(0) |
| smallint unsigned |
bytelen=2
null=1 | integer |
| smallint |
bytelen=2
null=1 | smallint |
| smallmoney | null=1 | decimal(10,4) |
| text (ingres) |
bytelen=10
encoding=UTF-8 null=1 | varchar(10) |
| text (sqlserver) |
encoding=WINDOWS-1252
null=1 | clob(2147483647) |
| text(sybase) |
encoding=UTF-8
null=1 | clob(2147483647) |
| time (mysql) |
prec_sec=0
null=1 | time |
| time (hana) | null=1 | time |
| time (sybase) | null=1 | time |
| time with local time zone |
prec_sec=0
null=1 | time |
| time with time zone |
prec_sec=0
null=1 | time |
| time |
prec_sec=0
null=1 | time |
| time |
prec_sec=3
null=1 | time |
| time2 | null=1 | time |
| timestamp (bigquery) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp (db2) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp (hana) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp (ingres) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp (mysql) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp (oracle) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp (sqlserver) |
bytelen=10
null=1 | char(10) for bit data |
| timestamp (sybase) |
bytelen=10
null=1 | varchar(10) for bit data |
| timestamp with local time zone |
prec_sec=0
null=1 | timestamp(0) |
| timestamp with local tz (oracle) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp with time zone |
prec_sec=0
null=1 | timestamp(0) |
| timestamp with tz (oracle) |
prec_sec=0
null=1 | timestamp(0) |
| timestamp |
prec_sec=0
null=1 | timestamp(0) |
| tinyint signed |
bytelen=1
null=1 | smallint |
| tinyint unsigned |
bytelen=1
null=1 | smallint |
| tinyint |
bytelen=1
null=1 | smallint |
| uniqueidentifier |
bytelen=16
null=1 | char(16) for bit data |
| unitext |
charlen=0
null=1 | dbclob(1073741823) |
| univarchar |
charlen=10
null=1 | vargraphic(10) |
| unsigned bigint |
bytelen=8
null=1 | decimal(20) |
| unsigned int |
bytelen=4
null=1 | bigint |
| unsigned smallint |
bytelen=2
null=1 | integer |
| urowid |
bytelen=100
charlen=100 encoding=US-ASCII null=1 | varchar(100) |
| varbinary |
bytelen=10
null=1 | varchar(10) for bit data |
| varbinary (sybase) |
bytelen=10
null=1 | varchar(10) for bit data |
| varbinary(max) | null=1 | blob(2147483647) |
| varbyte |
bytelen=10
null=1 | varchar(10) for bit data |
| varchar |
bytelen=10
encoding=UTF-8 null=1 | varchar(10) |
| varchar |
bytelen=8000
encoding=UTF-8 null=1 | varchar(8000) |
| varchar |
bytelen=40
charlen=10 encoding=UTF-8 null=1 | varchar(40) |
| varchar |
bytelen=10
encoding=WINDOWS-1252 null=1 | varchar(20) |
| varchar (sybase) |
bytelen=10
encoding=UTF-8 null=1 | varchar(10) |
| varchar (sybase) |
bytelen=8000
encoding=UTF-8 null=1 | varchar(8000) |
| varchar (sybase) |
bytelen=40
charlen=10 encoding=UTF-8 null=1 | varchar(40) |
| varchar (sybase) |
bytelen=10
encoding=WINDOWS-1252 null=1 | varchar(20) |
| varchar(max) |
encoding=WINDOWS-1252
null=1 | clob(2147483647) |
| varchar2 |
bytelen=10
encoding=UTF-8 null=1 | varchar(10) |
| varchar2 |
bytelen=40
charlen=10 encoding=UTF-8 null=1 | varchar(40) |
| varchar2 |
bytelen=10
encoding=WINDOWS-1252 null=1 | varchar(20) |
| vargraphic |
charlen=10
null=1 | vargraphic(10) |
| xml |
charlen=0
null=1 | xml |
| year (mysql) | null=1 | smallint |
Comments
0 comments
Please sign in to leave a comment.