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 Fivetran HVR repository data type.

Db2 for LUW	Capture Support	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
boolean	Native	boolean
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.

* For Db2 LUW versions before 11.1.1.1, the repository data types bool and boolean are mapped to smallint. For Db2 LUW versions since 11.1.1.1, they are mapped to boolean.

Text in green cell indicates the native data type of the DBMS

Repository Data Type	Attributes	DB2 for Linux, UNIX and Windows (UTF-8)
ansidate (ingres)	nullable=0	date
ansidate	nullable=0	date
bfile	nullable=0	blob(2147483647)
bigdatetime	timeprec=6 nullable=0	timestamp(6)
bigint unsigned	bytelen=8 nullable=0	decimal(20)
bigint	bytelen=8 nullable=0	bigint
bigtime	timeprec=6 nullable=0	time
binary	bytelen=10 nullable=0	char(10) for bit data
binary_double	bytelen=8 nullable=0	double
binary_float	bytelen=4 nullable=0	real
binary decimal	prec=9 scale=3 nullable=0	decimal(9,3)
binary decimal	prec=6 scale=2 nullable=0	decimal(6,2)
bit (mysql)	bitlen=32 nullable=0	varchar(32)
bit	bytelen=1 nullable=0	boolean
blob	nullable=0	blob(2147483647)
bool	bytelen=1 nullable=0	boolean
boolean	bytelen=1 nullable=0	boolean
byte varying	bytelen=10 nullable=0	varchar(10) for bit data
byte	bytelen=10 nullable=0	char(10) for bit data
byteint	bytelen=1 nullable=0	smallint
c	bytelen=10 encoding=UTF-8 nullable=0	char(10)
char	bytelen=10 encoding=UTF-8 nullable=0	char(10)
char	bytelen=4000 encoding=UTF-8 nullable=0	varchar(4000)
char	bytelen=8000 encoding=UTF-8 nullable=0	varchar(8000)
char	bytelen=40 charlen=10 encoding=UTF-8 nullable=0	char(40)
char	bytelen=10 encoding=WINDOWS-1252 nullable=0	char(20)
char (oracle)	bytelen=10 encoding=UTF-8 nullable=0	char(10)
char (oracle)	bytelen=2000 encoding=UTF-8 nullable=0	varchar(2000)
char (oracle)	bytelen=40 charlen=10 encoding=UTF-8 nullable=0	char(40)
char (oracle)	bytelen=10 encoding=WINDOWS-1252 nullable=0	char(20)
clob	encoding=UTF-8 nullable=0	clob(2147483647)
datalink	bytelen=800 charlen=200 encoding=UTF-8 nullable=0	varchar(800)
date	nullable=0	timestamp(0)
date (hana)	nullable=0	date
date (mysql)	nullable=0	date
date (sybase)	nullable=0	date
datetime (bigquery)	timeprec=0 nullable=0	timestamp(0)
datetime (mysql)	timeprec=0 nullable=0	timestamp(0)
datetime (sybase)	nullable=0	timestamp(3)
datetime	nullable=0	timestamp(3)
datetime2	timeprec=0 nullable=0	timestamp(0)
datetimeoffset	timeprec=0 nullable=0	timestamp(0)
db2 rowid	bytelen=40 nullable=0	varchar(80)
db2 timestamp with time zone	timeprec=0 nullable=0	timestamp(0)
db2 xml	encoding=UTF-8 nullable=0	xml
dbclob	nullable=0	dbclob(1073741823)
decfloat	prec=16 nullable=0	decfloat(16)
decfloat	prec=34 nullable=0	decfloat(34)
decimal	prec=10 scale=3 nullable=0	decimal(10,3)
decimal	prec=6 nullable=0	decimal(6)
double	bytelen=8 nullable=0	double
epoch	timeprec=0 nullable=0	timestamp(0)
float	bytelen=8 nullable=0	double
float4	bytelen=4 nullable=0	real
float8	bytelen=8 nullable=0	double
float64	bytelen=8 nullable=0	double
graphic	charlen=10 nullable=0	graphic(10)
hierarchyid	bytelen=892 nullable=0	varchar(4000)
image	nullable=0	blob(2147483647)
image (sybase)	nullable=0	blob(2147483647)
ingresdate	nullable=0	timestamp(0)
int unsigned	bytelen=4 nullable=0	bigint
int	bytelen=4 nullable=0	integer
integer	bytelen=4 nullable=0	integer
integer1	bytelen=1 nullable=0	smallint
integer2	bytelen=2 nullable=0	smallint
integer4	bytelen=4 nullable=0	integer
integer8	bytelen=8 nullable=0	bigint
int64	bytelen=8 nullable=0	bigint
interval day to second (ingres)	timeprec=0 nullable=0	timestamp(0)
interval day to second	timeprec=0 dayprec=0 nullable=0	timestamp(0)
interval month to second	timeprec=0 nullable=0	timestamp(0)
interval year to month (ingres)	nullable=0	timestamp(0)
interval year to month	yearprec=0 nullable=0	timestamp(0)
json	encoding=UTF-8 nullable=0	clob(2147483647)
jsonb	encoding=UTF-8 nullable=0	clob(2147483647)
long byte	nullable=0	blob(2147483647)
long char	encoding=UTF-8 nullable=0	clob(2147483647)
long nvarchar (db2)	nullable=0	long vargraphic
long nvarchar	nullable=0	dbclob(1073741823)
long raw	nullable=0	blob(2147483647)
long varbinary	nullable=0	long varchar for bit data
long varchar (db2)	encoding=UTF-8 nullable=0	long varchar
long varchar	encoding=UTF-8 nullable=0	clob(2147483647)
long	encoding=UTF-8 nullable=0	clob(2147483647)
mediumint unsigned	bytelen=3 nullable=0	integer
mediumint	bytelen=3 nullable=0	integer
money (ingres)	nullable=0	decimal(14,2)
money	nullable=0	decimal(19,4)
nchar	charlen=10 nullable=0	graphic(10)
nchar (oracle)	bytelen=20 charlen=10 nullable=0	graphic(10)
nclob	nullable=0	dbclob(1073741823)
ntext	nullable=0	dbclob(1073741823)
number	nullable=0	decimal(31,4)
number	prec=10 scale=-127 nullable=0	double
number	prec=10 scale=3 nullable=0	decimal(10,3)
number	prec=26 nullable=0	decimal(26)
number	prec=6 nullable=0	integer
numeric (db2i)	prec=10 scale=3 nullable=0	decimal(10,3)
numeric (db2i)	prec=6 nullable=0	decimal(6)
numeric	prec=10 scale=3 nullable=0	decimal(10,3)
numeric	prec=26 nullable=0	decimal(26)
numeric	prec=6 nullable=0	decimal(6)
nvarchar	charlen=10 nullable=0	vargraphic(10)
nvarchar(max)	nullable=0	dbclob(1073741823)
nvarchar2	charlen=10 nullable=0	vargraphic(10)
postgres date	nullable=0	date
postgres time	timeprec=6 nullable=0	time
postgres timestamp with time zone	timeprec=0 nullable=0	timestamp(0)
postgres timestamp	timeprec=0 nullable=0	timestamp(0)
raw	bytelen=10 nullable=0	varchar(10) for bit data
real	bytelen=4 nullable=0	real
rowid	bytelen=18 charlen=18 encoding=US-ASCII nullable=0	char(18)
rowversion	bytelen=10 nullable=0	char(10) for bit data
smalldatetime	nullable=0	timestamp(0)
smallint unsigned	bytelen=2 nullable=0	integer
smallint	bytelen=2 nullable=0	smallint
smallmoney	nullable=0	decimal(10,4)
text (ingres)	bytelen=10 encoding=UTF-8 nullable=0	varchar(10)
text (sqlserver)	encoding=WINDOWS-1252 nullable=0	clob(2147483647)
text(sybase)	encoding=UTF-8 nullable=0	clob(2147483647)
time (mysql)	timeprec=0 nullable=0	time
time (hana)	nullable=0	time
time (sybase)	nullable=0	time
time with local time zone	timeprec=0 nullable=0	time
time with time zone	timeprec=0 nullable=0	time
time	timeprec=0 nullable=0	time
time	timeprec=3 nullable=0	time
time2	nullable=0	time
timestamp (bigquery)	timeprec=0 nullable=0	timestamp(0)
timestamp (databricks)	timeprec=0 nullable=0	timestamp(0)
timestamp (db2)	timeprec=0 nullable=0	timestamp(0)
timestamp (hana)	timeprec=0 nullable=0	timestamp(0)
timestamp (ingres)	timeprec=0 nullable=0	timestamp(0)
timestamp (mysql)	timeprec=0 nullable=0	timestamp(0)
timestamp (oracle)	timeprec=0 nullable=0	timestamp(0)
timestamp (sqlserver)	bytelen=10 nullable=0	char(10) for bit data
timestamp (sybase)	bytelen=10 nullable=0	varchar(10) for bit data
timestamp with local time zone	timeprec=0 nullable=0	timestamp(0)
timestamp with local tz (oracle)	timeprec=0 nullable=0	timestamp(0)
timestamp with time zone	timeprec=0 nullable=0	timestamp(0)
timestamp with tz (oracle)	timeprec=0 nullable=0	timestamp(0)
timestamp	timeprec=0 nullable=0	timestamp(0)
tinyint signed	bytelen=1 nullable=0	smallint
tinyint unsigned	bytelen=1 nullable=0	smallint
tinyint	bytelen=1 nullable=0	smallint
uniqueidentifier	bytelen=16 nullable=0	char(16) for bit data
unitext	nullable=0	dbclob(1073741823)
univarchar	charlen=10 nullable=0	vargraphic(10)
unsigned bigint	bytelen=8 nullable=0	decimal(20)
unsigned int	bytelen=4 nullable=0	bigint
unsigned smallint	bytelen=2 nullable=0	integer
urowid	bytelen=100 charlen=100 encoding=US-ASCII nullable=0	varchar(100)
varbinary	bytelen=10 nullable=0	varchar(10) for bit data
varbinary (sybase)	bytelen=10 nullable=0	varchar(10) for bit data
varbinary(max)	nullable=0	blob(2147483647)
varbyte	bytelen=10 nullable=0	varchar(10) for bit data
varchar	bytelen=10 encoding=UTF-8 nullable=0	varchar(10)
varchar	bytelen=8000 encoding=UTF-8 nullable=0	varchar(8000)
varchar	bytelen=40 charlen=10 encoding=UTF-8 nullable=0	varchar(40)
varchar	bytelen=10 encoding=WINDOWS-1252 nullable=0	varchar(20)
varchar (sybase)	bytelen=10 encoding=UTF-8 nullable=0	varchar(10)
varchar (sybase)	bytelen=8000 encoding=UTF-8 nullable=0	varchar(8000)
varchar (sybase)	bytelen=40 charlen=10 encoding=UTF-8 nullable=0	varchar(40)
varchar (sybase)	bytelen=10 encoding=WINDOWS-1252 nullable=0	varchar(20)
varchar(max)	encoding=WINDOWS-1252 nullable=0	clob(2147483647)
varchar2	bytelen=10 encoding=UTF-8 nullable=0	varchar(10)
varchar2	bytelen=40 charlen=10 encoding=UTF-8 nullable=0	varchar(40)
varchar2	bytelen=10 encoding=WINDOWS-1252 nullable=0	varchar(20)
vargraphic	charlen=10 nullable=0	vargraphic(10)
variant	encoding=UTF-8 nullable=0	clob(2147483647)
xml	nullable=0	xml
year (mysql)	nullable=0	smallint