IBM Db2 to Elasticsearch: How to Move Your Data

Moving IBM Db2 into Elasticsearch gives an application search over records that have sat in a mainframe-era system for decades. Db2 retrieves by key faultlessly and offers nothing resembling relevance ranking, so anything involving partial matches ends up as a LIKE clause scanning a very large table.

This guide covers the managed path with Airbyte. Two things shape the build, and the first is a blocker: this destination exists on Airbyte Core and PyAirbyte only. The second is that capturing deletes from Db2 means installing machinery inside the database itself.

IBM Db2 to Elasticsearch at a glance:

Why move data from IBM Db2 to Elasticsearch?

Two situations account for most of these pipelines.

The first is putting search in front of a system of record. Customer service staff searching for a policy, a claim or an account by half-remembered details is a search problem, and building it against Db2 directly means either expensive scans or a great deal of application code approximating what a search engine does natively.

The second is reducing load on a system that is expensive to query. If what you need is analysis rather than search, aggregating across years of records, a warehouse suits that far better and IBM Db2 to Snowflake avoids both the availability constraint and the index design work described below.

What do you need before you start?

Four things, and the first decides whether you can proceed at all:

An Airbyte Core or PyAirbyte deployment. The Elasticsearch destination is not offered on Standard, Plus, Pro or Enterprise Flex, and no configuration works around it. The Elasticsearch destination documentation sets out the position.

A dedicated read-only Db2 user. With access to the schemas you intend to replicate. The connector is built on IBM's JDBC driver and does not alter your schema. The Db2 source documentation covers the connection details and the SSL option.

A DBA, if you need deletes. Change capture here is trigger based rather than log based, so triggers and tracking tables have to be provisioned in the database before the pipeline can use them.

An index mapping designed for legacy data. Db2 schemas tend to be full of codes, reference values and fixed-width identifiers, and how you map those decides whether search behaves sensibly.

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 Elasticsearch pipeline in Airbyte?

Step 1: Decide whether you need deletes, because that decides everything

For a search index this question matters more than it would for a warehouse. A cursor-based sync never sees deletions, so records removed from Db2 stay findable in your index indefinitely, and a service agent searching for a closed account will find it. If that is unacceptable, you need change capture, which means a DBA installing triggers and tracking tables. Establish which world you are in before designing anything else.

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 adds it to a keystore for you.

Step 3: Configure the Elasticsearch destination

Click Destinations, then New Destination, and select Elasticsearch, following adding a destination. Supply the endpoint and authentication, and point it at indexes whose mapping you created deliberately rather than letting Elasticsearch infer types from whichever documents arrive first.

Step 4: Create the connection and test a realistic search

Click Connections, then New connection, select your streams and a sync mode. Then search the way your users will, with a partial reference number or a misspelled name, rather than with a value you copied from the database. That is the case this index exists for and the one most likely to disappoint.

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

What does trigger-based change capture put in your database?

Rather more than most people expect. This is not log reading: for every table you replicate with change capture, a tracking table is created in a dedicated schema and three triggers are added to the source table, one each for inserts, updates and deletes. Changes are captured into the tracking tables as they happen, and the connector reads from there.

That has consequences a DBA will want to discuss. Triggers add work to every write on a replicated table, tracking tables consume space, and the arrangement is distinct from IBM's own InfoSphere Change Data Capture rather than integrating with it. The connector does prune old change records after processing them, so the tracking tables do not grow without limit, but the write overhead is permanent.

Two practical notes. The runtime user needs different grants from the setup user, specifically SELECT on your source tables plus SELECT and DELETE on the tracking tables, and they can legitimately be different accounts. And if you use the provided setup script, be aware it prints the database connection string including credentials to standard output, which is worth suppressing anywhere logs are retained.

How should a legacy schema be mapped for search?

Mostly as keywords, which is the opposite of what a text-heavy source would want. Db2 schemas accumulated over decades are full of status codes, product references, branch identifiers and fixed-width account numbers, and every one of those is a value somebody filters or matches exactly rather than a phrase anybody types partially.

Mapping those as analysed text produces results that look plausible and are wrong, because a reference split into tokens matches other references sharing a fragment. The fields that genuinely want analysing are the free-text ones, typically names, addresses and description columns, and those are usually a small minority of a legacy table's columns.

Multi-field mapping handles the cases that genuinely need both, which here usually means a customer name searchable in part and matchable exactly, or a reference number people sometimes paste whole and sometimes type the tail of. Decide all of this before the first sync, because changing a mapping means reindexing, and reindexing a table from a system of record is not a quick afternoon.

Frequently asked questions

Can I use this destination on any Airbyte plan?

No. Elasticsearch is available on Airbyte Core and PyAirbyte only, so confirm your deployment before planning around it.

Will deleted records disappear from the index?

Only with change capture. Cursor-based reading sees inserts and updates only, which leaves removed records findable indefinitely, and for a search index that is a visible fault.

Does this use IBM InfoSphere Change Data Capture?

No. It is a separate trigger-based implementation using its own tracking tables, and it is not compatible with InfoSphere CDC.

Why does searching a reference number return the wrong records?

It is probably mapped as analysed text and being split into tokens. Codes and identifiers belong as keywords, with multi-field mapping where partial matching is also wanted.

Can I do this without writing code?

The pipeline, yes. Provisioning change capture is database work for a DBA, and the index mapping is configuration you write in Elasticsearch.

Get your IBM Db2 data into Elasticsearch

Confirm your deployment supports this destination first, since Core and PyAirbyte are the only options. Then settle the deletes question, because a search index holding records the source has removed is a visible fault rather than an inconvenience, and capturing them means triggers and tracking tables your DBA has to agree to. Map codes and identifiers as keywords rather than analysed text, and test with the searches your users will actually type.

Airbyte's connector catalog includes 600+ pre-built connectors, so a system of record can power search without a bespoke indexer. For the same source into a lakehouse, see IBM Db2 to Databricks, and for another enterprise database into the same destination, Oracle Database to Elasticsearch.

Start syncing now →