Db2

Mapping

The following tables explain how Db2 source datatypes are mapped to each target database. Wirekite extracts data from Db2, converts it to an internal type representation, then maps to the appropriate target type.

Wirekite reads column metadata from the SYSCAT.COLUMNS catalog view. Unquoted Db2 identifiers are folded to uppercase in the catalog; Wirekite lowercases table and column names on output.

CDC (Replicate) type coverage is narrower than the initial data load. The types below marked † extract correctly during a one-time Data load but are not decodable from the Db2 recovery log during Replicate (CDC). A tracked table with one of these columns will lose that column — and every column after it — in the change stream. The affected types are DECFLOAT, GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, DBCLOB, and LONG VARCHAR. Avoid them on tables you replicate, or keep them last in column order.

Numeric Types

Db2	PostgreSQL	MySQL	SQL Server	Oracle	Firebolt	Snowflake	Spanner	BigQuery	SingleStore	Databricks
`SMALLINT`	`smallint`	`SMALLINT`	`SMALLINT`	`NUMBER(38)`	`INTEGER`	`number`	`INT64`	`INT64`	`SMALLINT`	`SMALLINT`
`INTEGER`	`integer`	`INT`	`INT`	`NUMBER(38)`	`INTEGER`	`int`	`INT64`	`INT64`	`INT`	`INT`
`BIGINT`	`bigint`	`BIGINT`	`BIGINT`	`NUMBER(38)`	`BIGINT`	`bigint`	`INT64`	`INT64`	`BIGINT`	`BIGINT`
`DECIMAL`	`numeric`	`NUMERIC`	`NUMERIC`	`NUMBER`	`NUMERIC`	`number`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`DECIMAL`
`NUMERIC`	`numeric`	`NUMERIC`	`NUMERIC`	`NUMBER`	`NUMERIC`	`number`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`DECIMAL`
`REAL`	`real`	`FLOAT`	`REAL`	`BINARY_FLOAT`	`DOUBLE`	`float`	`FLOAT32`	`FLOAT64`	`FLOAT`	`FLOAT`
`DOUBLE`	`double precision`	`DOUBLE`	`FLOAT`	`BINARY_DOUBLE`	`DOUBLE`	`float`	`FLOAT64`	`FLOAT64`	`DOUBLE`	`DOUBLE`
`DECFLOAT` †	`double precision`	`DOUBLE`	`FLOAT`	`BINARY_DOUBLE`	`DOUBLE`	`float`	`FLOAT64`	`FLOAT64`	`DOUBLE`	`DOUBLE`

DECIMAL/NUMERIC carry their precision and scale through to the target. Db2’s maximum DECIMAL precision is 31; a source column with higher precision is capped at 31 when Db2 is the target. DECFLOAT is decimal floating-point with no fixed scale, so it maps to a 64-bit float rather than a fixed NUMERIC(p,s).

Boolean Type

Db2	PostgreSQL	MySQL	SQL Server	Oracle	Firebolt	Snowflake	Spanner	BigQuery	SingleStore	Databricks
`BOOLEAN`	`boolean`	`TINYINT(1)`	`BIT`	`CHAR(1)`	`BOOLEAN`	`boolean`	`BOOL`	`BOOL`	`TINYINT(1)`	`BOOLEAN`

Character Types

Db2	PostgreSQL	MySQL	SQL Server	Oracle	Firebolt	Snowflake	Spanner	BigQuery	SingleStore	Databricks
`CHAR` / `CHARACTER`	`character`	`CHAR`	`CHAR`	`CHAR`	`TEXT`	`varchar`	`STRING`	`STRING`	`CHAR`	`STRING`
`VARCHAR`	`character varying`	`VARCHAR`	`VARCHAR`	`VARCHAR2`	`TEXT`	`varchar`	`STRING`	`STRING`	`VARCHAR`	`STRING`
`LONG VARCHAR` †	`character varying`	`VARCHAR`	`VARCHAR`	`VARCHAR2`	`TEXT`	`varchar`	`STRING`	`STRING`	`VARCHAR`	`STRING`
`CLOB`	`text`	`LONGTEXT`	`VARCHAR(MAX)`	`CLOB`	`TEXT`	`varchar`	`STRING`	`STRING`	`LONGTEXT`	`STRING`

Graphic (Double-Byte) Types

Db2	PostgreSQL	MySQL	SQL Server	Oracle	Firebolt	Snowflake	Spanner	BigQuery	SingleStore	Databricks
`GRAPHIC` †	`character`	`CHAR`	`CHAR`	`CHAR`	`TEXT`	`varchar`	`STRING`	`STRING`	`CHAR`	`STRING`
`VARGRAPHIC` †	`character varying`	`VARCHAR`	`VARCHAR`	`VARCHAR2`	`TEXT`	`varchar`	`STRING`	`STRING`	`VARCHAR`	`STRING`
`LONG VARGRAPHIC` †	`character varying`	`VARCHAR`	`VARCHAR`	`VARCHAR2`	`TEXT`	`varchar`	`STRING`	`STRING`	`VARCHAR`	`STRING`
`DBCLOB` †	`text`	`LONGTEXT`	`VARCHAR(MAX)`	`CLOB`	`TEXT`	`varchar`	`STRING`	`STRING`	`LONGTEXT`	`STRING`

Db2 graphic (DBCS) types store Unicode text and fold to the same internal representation as their character counterparts (GRAPHIC→CHAR, VARGRAPHIC→VARCHAR, DBCLOB→CLOB). On a UTF-8 database the values are preserved through the initial load. They are not supported by CDC — see the warning above.

Binary Types

Db2	PostgreSQL	MySQL	SQL Server	Oracle	Firebolt	Snowflake	Spanner	BigQuery	SingleStore	Databricks
`BINARY`	`bytea`	`BINARY`	`BINARY`	`RAW`	`BYTEA`	`binary`	`BYTES`	`BYTES`	`BINARY`	`BINARY`
`VARBINARY`	`bytea`	`VARBINARY`	`VARBINARY`	`RAW`	`BYTEA`	`binary`	`BYTES`	`BYTES`	`VARBINARY`	`BINARY`
`BLOB`	`bytea`	`BLOB`	`VARBINARY(MAX)`	`BLOB`	`BYTEA`	`binary`	`BYTES`	`BYTES`	`BLOB`	`BINARY`

Date and Time Types

Db2	PostgreSQL	MySQL	SQL Server	Oracle	Firebolt	Snowflake	Spanner	BigQuery	SingleStore	Databricks
`DATE`	`date`	`DATE`	`DATE`	`DATE`	`DATE`	`date`	`DATE`	`DATE`	`DATE`	`DATE`
`TIME`	`time without time zone`	`TIME(6)`	`TIME`	`VARCHAR2(64)`	`TEXT`	`time`	`STRING`	`TIME`	`TIME(6)`	`STRING`
`TIMESTAMP`	`timestamp without time zone`	`DATETIME(6)`	`DATETIME2`	`TIMESTAMP`	`TIMESTAMP`	`timestamp_ntz`	`TIMESTAMP`	`DATETIME`	`DATETIME(6)`	`TIMESTAMP_NTZ`

Db2 TIME has no sub-second precision. TIMESTAMP supports fractional seconds up to 12 digits; Wirekite preserves microsecond precision (.FF6) on the wire.

Other Types

Db2	PostgreSQL	MySQL	SQL Server	Oracle	Firebolt	Snowflake	Spanner	BigQuery	SingleStore	Databricks
`XML`	`xml`	`TEXT`	`XML`	`CLOB`	`TEXT`	`varchar`	`STRING`	`STRING`	`TEXT`	`STRING`
`(Any other type)`	`character varying`	`VARCHAR`	`VARCHAR`	`VARCHAR2`	`TEXT`	`varchar`	`STRING`	`STRING`	`VARCHAR`	`STRING`

Any Db2 type not listed above (for example ROWID, TIMESTAMP WITH TIME ZONE, or CHAR FOR BIT DATA) falls back to a wide character type on the initial load, with a warning logged. These fallback types are not supported by CDC.

Introduction

Datatype Matrices

Source Guides

Data Movers

Target Guides

Queues

Running Wirekite

Tools

API/CLI

Mapping

Numeric Types

Boolean Type

Character Types

Graphic (Double-Byte) Types

Binary Types

Date and Time Types

Other Types

​Mapping

​Numeric Types

​Boolean Type

​Character Types

​Graphic (Double-Byte) Types

​Binary Types

​Date and Time Types

​Other Types

Mapping

Numeric Types

Boolean Type

Character Types

Graphic (Double-Byte) Types

Binary Types

Date and Time Types

Other Types