DB2 on IBM System I aka IBM AS400 connector

[gabviv73]

Hy, I need to deal with this DB on this system. We cannot use the Win/Linux connector, it's different.
Using JDBC, there is another class driver (jtopen) but I think the usage is the same.
Reference sites are:
https://ibm.github.io/JTOpen
https://github.com/IBM/JTOpen

I can help you testing the connector.
I'm referring to #31918

[AnthonyLebleu]

I would be really interested in this connector. I also need to deal with IBM AS400 database.

[AnthonyLebleu]

Hi @gabviv73,

I worked on my own for a solution to query the AS400 db from Airbyte.
Ended up creating an app that works with APIs to get JSON formated data. Then you can use these APIs within Airbyte.

I am still learning how it works but I can share my work if you want to.

Regards

[TailorSoftHungary]

Could you please share your solution ? TY!

[allamsudha]

Could you please share your work as a repo with MIT license? Would it not work just like how Apache Hop does wherein you have to drop the jdbc driver in a particular folder and provide connection details.

[AnthonyLebleu]

Hey there. Unfortunately I won't be able to share the code as if it's the property of one our IT partner but the solution looks like this :

• Use FastAPI to set up the connection to your IBMi DB2 database.
• Configure the driver depending your OS (I used jt400.jar on Windows)
• When the connection is OK, you can configure any API route you need
• All you have to do now is to set up the URL of the API in Airbyte and use all the API routes

[maxigarn]

The easiest way to get started is by using the [isebbenmon/source-as400:0.0.7](https://hub.docker.com/r/isebbenmon/source-as400) connector. While I haven't been able to test it thoroughly, it was able to successfully detect my tables, which makes it a good starting point.

Connector Configuration

Here are the main configuration parameters to keep in mind:

host

The IP address or DNS name of your AS400 server.

port

This depends on how your AS400 is configured:

• 449: Default port for the database server (used by JDBC via JT400).
• 8470: Used for encrypted connections (SSL/TLS).
• 8471: Alternative for unencrypted connections, depending on your setup.
• 446: Legacy port for DDM (Distributed Data Management) — rarely used today.

database

The schema name you want to synchronize.

tables (optional)

List of tables to synchronize, separated by a pipe character |.
If this field is not filled, no tables will be detected.

---

Creating a Custom Connector

For more flexibility or to enable automatic table discovery, you may want to build your own custom connector.

We did this by copying the source-db2 connector and adapting it using this repository as a reference:

🔗 https://github.com/bomzai/custom-airbyte-jdbc

This repo contains very helpful code for building custom JDBC connectors.

By default, the connector uses the following method to discover columns via the JT400 library:

connection.getMetaData().getColumns(...)

---

Optimization for Large Schemas

In our case, the schema includes around 10,000 tables, many of which have a large number of columns. This is too heavy for Airbyte to handle in a standard discovery.

We adjusted the discovery logic to query SYSIBM.SQLCOLUMNS manually instead, and also filter the results based on a table that lists the tables allowed for synchronization.
This drastically reduces the load during schema discovery.

---

Building the Custom Connector with Docker

To build the custom connector easily, we recommend using a dedicated Docker container. This ensures a clean environment and avoids version conflicts with your local Python, Java, etc.

Steps:

1. Build the connector:

./gradlew :airbyte-integrations:connectors:source-ibmi:build

2. Tag the image with a version:

docker tag airbyte/source-ibmi:dev airbyte/source-ibmi:0.0.1

3. Export the image:

docker save airbyte/source-ibmi:0.0.1 -i source-ibmi.tar

4. Import it on the Airbyte server:

docker load -i source-ibmi.tar

5. Deploy the image to the Kubernetes cluster with kind:

kind load docker-image airbyte/source-ibmi:0.0.1 --name airbyte-abctl

6. You can then use it with airbyte:

Settings > Sources > + New connector > Add a new Docker connector

[ttarom]

@maxigarn we are trying to implement the same but it keeps failing, can you share your connector please?