IBM Db2 to Snowflake: How to Move Your Data

Replicating Db2 to Snowflake moves data out of a mainframe or enterprise database that your analysts cannot easily query and into a cloud warehouse where they can. It is a common step in modernisation work, where Db2 remains the system of record and Snowflake becomes the analytical layer. Airbyte builds the pipeline with no code.

One decision comes before everything else, because Airbyte offers two different Db2 connectors with fundamentally different replication mechanisms. Which one you can use determines whether you get change data capture at all.

Which Db2 connector do you have?

The standard IBM Db2 source supports full refresh and incremental syncs. Incremental here means cursor-based: you nominate a column that only increases, and each sync fetches rows above the last value seen. There is no change data capture.

The practical consequence is that deletes are invisible, and so is any update that does not touch your cursor column. On a system of record where rows are corrected and occasionally removed, that gap matters. Plan a periodic full refresh to bring Snowflake back in line, and size that refresh honestly, because a full read of a large Db2 table is not a trivial operation on a production system.

The connector is built on the IBM Data Server Driver for JDBC and SQLJ, a pure-Java Type 4 driver, so there is no native client to install alongside Airbyte. It does not alter the schema in your source, though the destination may reshape things on arrival.

How does the Enterprise connector do CDC?

The Db2 Enterprise connector does support change data capture, and it is worth understanding how, because it is not the mechanism most people assume.

Unlike log-based systems such as IBM InfoSphere Change Data Capture, this connector implements CDC using database triggers and tracking tables. For each source table you replicate with CDC, a tracking table is created in an _ab_cdc schema, and triggers capture INSERT, UPDATE, and DELETE operations into it. During syncs the connector reads those tracking tables and deletes the processed change rows, removing only records older than the current checkpoint so the tables do not grow indefinitely.

Three implications follow, and each is a conversation with your DBA rather than a setting you flip. Triggers add write overhead to the source tables, so this is a change to production behaviour and not a passive read. Provisioning the CDC infrastructure requires a privileged user, typically a DBA, as a one-time setup step. And if you drop and recreate a source table, you must recreate the triggers and the tracking table for it, which makes ordinary schema maintenance a two-part job.

Note also that this implementation is not compatible with IBM InfoSphere Change Data Capture. If your organisation already runs InfoSphere, this connector does not read from it and will build its own parallel mechanism. Raise that before anyone assumes the two will cooperate.

The first sync performs a full snapshot before switching to incremental CDC, governed by an Initial Load Time Limit in Hours setting that defaults to 8. On a large table, that ceiling is the setting to review before your first run.

Setting up access

Create a dedicated read-only user and role with access to the schemas you intend to replicate. Airbyte's documentation describes creating a role, granting it database connect access, and assigning it to the Airbyte user. This step is optional in that an existing account would work, but a dedicated user gives you permission control and an audit trail, which on a Db2 system is usually a requirement rather than a nicety.

Db2 instances are rarely reachable from the public internet, so expect to arrange network access, whether through an SSH tunnel, a private link, or an allow list. The most reliable way to confirm connectivity is to test the connection in the Airbyte UI rather than reasoning about the network diagram.

Landing the data in Snowflake

The Snowflake destination is Airbyte-supported with a high reported sync success rate, and has used direct load since version 4.0.0.

The Decimal Data Type setting deserves particular attention on this pipeline. It defaults to FLOAT, which is approximate, and the alternative is the exact NUMBER(38,9) that Airbyte recommends. Db2 systems of record hold financial and transactional data with DECIMAL columns whose precision exists for a reason, and losing it to a floating point conversion is exactly the kind of error that surfaces in a reconciliation months later. Set it before the first sync, since changing it afterwards converts columns in place and cannot restore precision that was never stored.

Create dedicated Snowflake entities with a setup script, and use an X-Small warehouse with a sixty-second auto-suspend, since Snowflake bills per second and resumes on every load.

Frequently asked questions

Does the standard Db2 connector support CDC?

No. It supports full refresh and cursor-based incremental sync. CDC is available on the Db2 Enterprise connector, implemented with triggers and tracking tables.

Will this work alongside IBM InfoSphere CDC?

No. The Enterprise connector's CDC is explicitly not compatible with InfoSphere and uses its own trigger-based implementation instead.

What happens if I recreate a source table?

You must recreate the CDC triggers and the tracking table for that table. Dropping and recreating a table does not restore the CDC infrastructure automatically.

Why do my decimal values look wrong in Snowflake?

The Decimal Data Type setting defaults to FLOAT, which is approximate. Switch to NUMBER(38,9) and run a full refresh, because in-place conversion cannot recover lost precision.

Get your Db2 data into Snowflake

Establish which connector you have, plan a periodic full refresh if you are on the standard one, involve your DBA early if you are not, and set the decimal type before the first load. For another enterprise database source, see our guide to Oracle to PostgreSQL. For another route into the same warehouse, see ClickHouse to Snowflake.

Start syncing now →