IBM Db2 to BigQuery: How to Move Your Data

Getting IBM Db2 data into BigQuery is usually done first with a nightly extract: a flat file export dropped on Cloud Storage and loaded by a scheduled job. It works, it runs whether anything changed or not, and it has no idea which rows were deleted.

This guide covers the managed path: replicating Db2 tables into BigQuery datasets with Airbyte. Two decisions carry the project, and neither is really about Airbyte. On the Db2 side you choose between two connectors with genuinely different trade-offs, and on the BigQuery side you make a couple of choices that cannot be changed later without rebuilding.

IBM Db2 to BigQuery at a glance:

Why move data from IBM Db2 to BigQuery?

Two situations account for most of these pipelines.

The first is reach. Db2 often holds the oldest and most authoritative data an organisation has, and the people who want to analyse it are using SQL notebooks and BI tools that do not speak to Db2 directly. Landing it in BigQuery puts that data where the tools already are, without giving dozens of analysts credentials to a mainframe-adjacent system.

The second is joining. Db2 is one system among several, and the value comes from combining it with data that lives elsewhere. That framing matters for the dataset location decision below, because BigQuery can only join datasets that share a physical location.

What do you need before you start?

More of this list involves other people than is usual for an Airbyte pipeline:

A decision on encryption versus CDC. The Enterprise Db2 connector lists SSL and SSH tunnel support as unavailable in its feature table, while the community connector supports SSL but replicates with a cursor and cannot capture deletes. Db2 usually holds data with a compliance regime attached, so settle this with your security team first.

A DBA, if you want CDC. The Enterprise connector implements change data capture with triggers and tracking tables that must be provisioned in advance. The connector does not create them, and this is usually the longest lead time in the project.

A Google service account. With the BigQuery User and BigQuery Data Editor roles. If you choose Cloud Storage staging you will also need a bucket, an HMAC key, and Storage Object Admin granted to the same service account.

A decided dataset location. BigQuery queries can only reference datasets in the same physical location, and the location cannot be changed after creation. Match it to the datasets you intend to join against.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on the Db2 side before you begin.

How do you build a Db2 to BigQuery pipeline in Airbyte?

Step 1: Prepare Db2 and the dataset

If you are using CDC, have your DBA provision the triggers and tracking tables for a small set of tables to begin with, so you can observe what they do to write latency before extending. Create the read-only Db2 user. Then create the BigQuery dataset in the location you settled on, because that choice is permanent.

Step 2: Configure the Db2 source

Click Sources in the left navigation, then New Source, and select Db2, following adding a source. Supply the host, port, database and credentials. On the Enterprise connector, select change data capture as the cursor method and set the initial load timeout, which defaults to eight hours and bounds the snapshot phase before the connector switches to incremental mode.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key, and pick a loading method. Batched standard inserts is the default and the simplest, converting inserts into managed file uploads that BigQuery cleans up automatically. Cloud Storage staging gives you control over where staging files live, at the cost of a bucket, an HMAC key and an extra role. Note that buckets using customer-managed encryption keys are not supported.

Step 4: Create the connection and choose deletion semantics

Click Connections, then New connection, pick your source and destination, and select tables and a sync mode per stream. With CDC, deleted rows arrive flagged rather than vanishing, so append and deduped gives a table mirroring current Db2 state while plain append preserves every version including the deletion event. Db2 systems frequently sit under audit requirements, and there plain append with a current-state view on top is the safer default, because you can derive the present from the history and never the reverse.

How should you query the resulting tables?

Output tables are partitioned daily on the extraction timestamp and clustered on that column plus the primary keys. That is not decoration, it is the main lever you have on query cost, because BigQuery bills for the bytes a query scans.

Filtering on the partitioning column prunes partitions and reduces what a query costs. On a Db2 table that has been accumulating for years and now grows indefinitely in BigQuery, the difference between a query that filters on it and one that does not is the difference between a few pounds a month and a conversation with finance.

Tell your analysts this once, at the start. It is much harder to retrofit onto a set of dashboards that already work than to establish as a convention while there is one query to change.

Do you actually need change data capture?

Worth asking honestly, because CDC on Db2 costs more than it does elsewhere. The Enterprise connector implements it with triggers rather than by reading the transaction log, so the overhead lands on every write to your production tables rather than on a background log reader. Tracking tables also grow between syncs, and keep growing if a connection is paused.

For analytics specifically, the question is whether the tables you need are ones where rows are ever deleted. A great deal of what sits in Db2 is append-heavy: transactions, ledger entries, events. If nothing is deleted, a cursor sees everything a change stream would, and the community connector with SSL and no DBA involvement becomes the better engineering decision rather than the compromise.

Where rows are updated or deleted and the warehouse must reflect that, CDC earns its cost. Just price it as a change to a production database rather than as a connector setting.

Frequently asked questions

Which BigQuery loading method should I use?

Batched standard inserts is the default and the simplest. Choose Cloud Storage staging when you need control over where staging files live, and note that buckets with customer-managed encryption keys are not supported.

Can I change the dataset location later?

No. It is fixed at creation, and BigQuery queries can only reference datasets in the same physical location, so match it to whatever you intend to join against.

Does Airbyte read the Db2 transaction log for CDC?

No. The Enterprise Db2 connector uses triggers and tracking tables, which is why the overhead lands on writes to your source tables rather than on a log reader.

How do I keep BigQuery query costs down?

Filter on the extraction timestamp, which is the partitioning column. Doing so prunes partitions and reduces the bytes scanned, which is what BigQuery bills for.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. The exception on this pipeline is the Db2 side, where CDC triggers and tracking tables have to be provisioned by a DBA before you begin.

Get your Db2 data into BigQuery

Settle the encryption and CDC question first, since it decides which connector you can use. Then make the two BigQuery choices deliberately, because the dataset location is permanent and the partitioning convention is much easier to establish now than to retrofit onto dashboards later.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same Db2 source can feed a lakehouse alongside the warehouse without rebuilding anything. For that pairing, see IBM Db2 to Databricks, and for another source landing in the same warehouse, Shopify to BigQuery.

Start syncing now →