IBM Db2 to Amazon Redshift: How to Move Your Data

Moving IBM Db2 into Amazon Redshift takes analytical load off a system that is usually expensive to query and central to operations. Db2 retrieves by key faultlessly and is a costly place to run a report scanning a decade of transactions while the business depends on it.

This guide covers the managed path with Airbyte. Two things shape the build: which Db2 you are connecting to changes what you need before you start, and a schema designed for transactions gives you no obvious answer to Redshift's most important question.

IBM Db2 to Amazon Redshift at a glance:

Why move data from IBM Db2 to Amazon Redshift?

Two situations account for most of these pipelines.

The first is cost and contention. Analytical queries against a system of record compete with the transactions that pay for it, and on a platform licensed by capacity that competition has a price. Moving the reporting elsewhere is often the cheapest performance improvement available.

The second is joining decades of transactional history to data that never lived on the mainframe. If your work is heavy transformation over that history rather than SQL reporting, IBM Db2 to Databricks handles that shape with less table maintenance than a cluster asks for.

What do you need before you start?

Four things, and the first determines whether the others are even the right questions:

Certainty about which Db2 this is. The connector uses IBM's JDBC driver, and a Db2 for i database needs an additional IBM licence file that the standard driver does not include. The Db2 source documentation covers the connection details.

A dedicated read-only user. With access to the schemas you intend to replicate. The connector does not alter your schema, which is usually the first thing a Db2 administrator wants to hear.

An S3 bucket in your cluster's region. Loading into Redshift goes through S3 staging, and a bucket in a different region adds transfer cost and latency to every sync for no benefit.

A plan for sort and distribution keys. Redshift's performance depends on them, the pipeline will not choose them for you, and a legacy schema rarely suggests an obvious answer.

If your database or cluster restricts traffic by IP, add the Airbyte Cloud IP addresses to the allow lists before you begin.

How do you build an IBM Db2 to Amazon Redshift pipeline in Airbyte?

Step 1: Establish which Db2 you are connecting to

People say Db2 to mean several different products, and the answer changes your setup. A Db2 for i database, the descendant of the AS/400, requires an extra IBM licence file alongside the JDBC driver, which is not something you discover gracefully midway through configuration. Ask whoever runs the system exactly what it is before assuming the standard path applies.

Step 2: Configure the Db2 source

Click Sources in the left navigation, then New Source, and select IBM Db2, following adding a source. Supply the host, port, database and credentials. For an encrypted connection on the community connector you provide a client certificate in the SSL PEM field, and the connector builds a keystore from it.

Step 3: Configure the Redshift destination

Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster details, database, credentials and your S3 staging bucket. Check numeric precision on any column carrying money or quantities, since a legacy schema often defines these more generously than a default mapping preserves.

Step 4: Create the connection and schedule maintenance

Click Connections, then New connection, select your streams and a sync mode. Then schedule vacuum and analyze, because Redshift does not maintain itself and a cluster receiving regular loads degrades quietly rather than failing.

Watch the load your syncs place on Db2 as well, since this is frequently a system where capacity is both finite and expensive.

Why does the Db2 variant matter so much?

Because Db2 is a family rather than a product. The version running on Linux or Windows behaves differently from the one on IBM i, which descends from the AS/400 and is still quietly running a great deal of business logic, and both differ again from the mainframe edition.

The practical consequence is a licence file. Connecting to a Db2 for i database needs an additional IBM jar that the standard driver does not ship with, which is an acquisition question rather than a configuration one and therefore capable of adding days to a project. Asking early costs nothing and asking late costs a week.

The related question is deletes, which decides which connector you need. Cursor-based reading sees inserts and updates only, so rows removed in Db2 remain in Redshift indefinitely. Capturing them means trigger-based change capture, with tracking tables and three triggers per replicated table, which is a conversation with a DBA rather than a setting. Establish whether your analysis tolerates the drift before committing to either path.

How do you choose keys for a legacy schema?

From your queries, not from the source. Redshift wants a distribution key that spreads rows evenly and colocates joins, and a sort key matching how you filter. A Db2 schema was designed for transactional access twenty years ago, so its primary keys and indexes answer a different question entirely and copying them across is a common mistake.

The practical approach is to look at the reports people actually run. Something filtered by date almost always wants date early in the sort key. A large fact table joined repeatedly to the same dimension usually wants that join column as the distribution key, while a table small enough to replicate to every node is better distributed that way and stops being a consideration at all.

Two things worth doing alongside. Check numeric precision on money columns before anybody reconciles a total, since legacy schemas are often generous with decimal definitions in ways a default mapping may not preserve. And schedule vacuum and analyze from the first week, because a cluster loaded regularly and never maintained gets slower in a way that looks like growth rather than neglect.

Frequently asked questions

Does this work with Db2 for i?

It needs an additional IBM licence file alongside the JDBC driver, so establish which Db2 you are connecting to before assuming the standard setup applies.

Will deleted rows disappear from Redshift?

Only with change capture, which here is trigger based and requires a DBA to provision tracking tables. Cursor-based reading leaves removed rows in place.

Should I copy my Db2 indexes as sort keys?

No. Those were designed for transactional access. Choose sort and distribution keys from the reports people run against Redshift instead.

Why is my cluster getting slower?

Probably missing maintenance. Vacuum and analyze are jobs you schedule, and a cluster receiving regular loads without them degrades gradually rather than erroring.

Can I do this without writing code?

The pipeline, yes. Key design and maintenance jobs are not, and both are what separates a usable cluster from a populated one.

Get your IBM Db2 data into Amazon Redshift

Find out exactly which Db2 you are connecting to, because a Db2 for i database needs a licence file nobody wants to be sourcing midway through the work. Settle the deletes question, since capturing them means triggers and a DBA. Keep your staging bucket in the cluster's region. Then design sort and distribution keys from the reports people run rather than from the legacy schema, check numeric precision on money columns, and schedule vacuum and analyze from the start.

Airbyte's connector catalog includes 600+ pre-built connectors, so a system of record can feed analysis without carrying it. For the same source into a warehouse, see IBM Db2 to Snowflake, and for another enterprise database into the same destination, Oracle Database to Amazon Redshift.

Start syncing now →