adbc-driver-db2

A pure-Go Apache Arrow ADBC driver for IBM Db2 that speaks Db2's native wire protocol, DRDA, directly. No IBM CLI / ODBC driver, no db2jcc, no cgo dependency on IBM libraries — one statically-linked shared library that plugs into every ADBC language binding (Python, Go, R, C/C++, C#, Rust, JavaScript) and returns Arrow record batches.

pip install adbc-driver-db2

import adbc_driver_db2.dbapi as db2

with db2.connect(
    uri="db2://db2host:50000/SAMPLE",
    username="db2inst1",
    password="********",
) as conn, conn.cursor() as cur:
    cur.execute("SELECT * FROM SALES.ORDERS WHERE ORDER_DATE >= ?", parameters=("2024-01-01",))
    table = cur.fetch_arrow_table()        # or cur.fetch_record_batch() to stream

Status: alpha. Tested against Db2 LUW 12.1 (Community Edition). The DRDA implementation parses the server's type definition, so Db2 for z/OS and Db2 for i should work in principle but have not been exercised yet — reports welcome.

Why

• Arrow-native, streaming. Result sets are pulled one DRDA query block at a time (1 MiB by default) and surfaced as Arrow record batches, so a 100 M-row SELECT needs about one batch of client memory. Perfect for Db2 → Arrow → somewhere else pipelines.
• Zero-install. IBM's clients are large, click-wrap licensed downloads. This is a pip install / go get.
• The full ADBC feature set: queries, parameter binding, bulk ingest (adbc_ingest with create/append/replace/create_append and declared temporary tables), transactions and isolation levels, GetObjects/GetTableSchema/GetInfo catalog metadata for tools like DBeaver, connection profiles, and the ADBC driver manifest. The driver passes the apache/arrow-adbc Go conformance suite.

Connection URI and options

db2://[user[:password]@]host[:port]/DATABASE[?param=value&...]

Standard ADBC options also apply: username, password, adbc.connection.autocommit, adbc.connection.transaction.isolation_level (mapped to SET CURRENT ISOLATION UR/CS/RS/RR), adbc.connection.catalog, adbc.connection.db_schema, and the adbc.ingest.* statement options.

Python

Streaming large result sets

with db2.connect(uri=uri, username=user, password=pw) as conn, conn.cursor() as cur:
    cur.execute("SELECT * FROM BIG.TABLE")
    reader = cur.fetch_record_batch()          # pyarrow.RecordBatchReader
    for batch in reader:                       # one query block at a time
        process(batch)

Db2 → GizmoSQL (or any ADBC target), ADBC to ADBC

The reader above can be handed straight to another driver's bulk ingest, so a table moves from Db2 into GizmoSQL without ever being materialised on the client:

import adbc_driver_db2.dbapi as db2
import adbc_driver_gizmosql.dbapi as gizmosql

with db2.connect(uri=db2_uri, username=db2_user, password=db2_pw) as src, \
     gizmosql.connect(gizmosql_uri, username="token", password=token) as dst:
    with src.cursor() as s, dst.cursor() as d:
        s.execute("SELECT * FROM PFWF6076.CGIBASE")
        rows = d.adbc_ingest(table_name="cgibase", data=s.fetch_record_batch(), mode="replace")
        dst.commit()
        print(f"Loaded {rows:,} rows")

Bulk ingest (Arrow → Db2)

import pyarrow as pa

table = pa.table({"id": [1, 2, 3], "name": ["alpha", "beta", None]})
with db2.connect(uri=uri, username=user, password=pw, autocommit=True) as conn, conn.cursor() as cur:
    cur.adbc_ingest("new_table", table, mode="create")   # create | append | replace | create_append

Rows are pipelined many per DRDA round trip (1000 by default; adbc.db2.ingest.batch_rows). VARCHAR/VARBINARY columns of a created table are sized from the first batch (adbc.db2.ingest.varchar_length overrides) because Db2's row-size limit depends on the tablespace page size. Values over 32 KiB are sent as out-of-line BLOB/CLOB data.

pandas and Polars

with db2.connect(uri=uri, username=user, password=pw) as conn, conn.cursor() as cur:
    cur.execute("SELECT * FROM SYSCAT.TABLES")
    df = cur.fetch_df()                                   # pandas
    # or, zero-copy into Polars:
    import polars as pl
    cur.execute("SELECT * FROM SYSCAT.COLUMNS")
    pl_df = pl.from_arrow(cur.fetch_arrow_table())

Parameters and executemany

import datetime

with db2.connect(uri=uri, username=user, password=pw, autocommit=True) as conn, conn.cursor() as cur:
    cur.execute("CREATE TABLE EVENTS (ID INTEGER NOT NULL, NAME VARCHAR(40), AT TIMESTAMP)")
    cur.executemany(
        "INSERT INTO EVENTS VALUES (?, ?, ?)",
        [(1, "start", datetime.datetime(2024, 1, 1, 9, 0)), (2, "stop", None)],
    )   # rows are pipelined many-per-round-trip, not sent one at a time
    cur.execute("SELECT NAME FROM EVENTS WHERE ID = ?", parameters=(2,))
    print(cur.fetchone())

Query Db2 live from DuckDB or GizmoSQL (adbc_scanner)

The c-shared driver plugs straight into DuckDB's adbc_scanner community extension (see the GizmoSQL guide) — and therefore into GizmoSQL, which embeds DuckDB. Store the credentials in a DuckDB secret once, then ATTACH Db2 like any other database and query it with plain SQL (projection and filter pushdown included):

INSTALL adbc_scanner FROM community;
LOAD adbc_scanner;

CREATE SECRET db2_secret (
    TYPE adbc,
    SCOPE 'db2://db2host:50000/SAMPLE',
    driver 'db2',                        -- by name after `python -m adbc_driver_db2 install-manifest`,
                                         -- or a path: '/path/to/libadbc_driver_db2.so'
    uri 'db2://db2host:50000/SAMPLE',
    username 'db2inst1',
    password '********'
);

ATTACH 'db2://db2host:50000/SAMPLE' AS db2 (TYPE adbc);

SELECT * FROM db2.SALES.ORDERS WHERE ORDER_DATE >= DATE '2024-01-01';

-- join Db2 with local data without copying it first
SELECT o.ORDER_ID, c.name
FROM db2.SALES.ORDERS o
JOIN customers c ON c.id = o.CUST_ID;

-- materialise a copy
CREATE TABLE orders AS SELECT * FROM db2.SALES.ORDERS;

For arbitrary Db2 SQL (or to push data the other way) the secret also drives the function API:

SET VARIABLE db2 = adbc_connect({'secret': 'db2_secret'});
SELECT * FROM adbc_scan(getvariable('db2')::BIGINT, 'SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY');

Query Db2 from DuckDB via connection profiles (Columnar's adbc extension)

Columnar's adbc community extension (see the GizmoSQL guide) resolves databases through ADBC connection profiles, and additionally supports writing (INSERT, CREATE TABLE AS) into the attached database through ADBC bulk ingest. Install this driver's manifest once, write a profile, and Db2 is a catalog:

python -m adbc_driver_db2 install-manifest        # registers driver "db2"
cat > ~/.config/adbc/profiles/warehouse.toml <<EOF   # macOS: ~/Library/Application Support/ADBC/Profiles/
profile_version = 1
driver = "db2"

[Options]
uri = "db2://db2host:50000/SAMPLE"
username = "db2inst1"
password = "********"
EOF

INSTALL adbc FROM community;
LOAD adbc;

SELECT * FROM read_adbc('profile://warehouse', 'SELECT * FROM SALES.ORDERS FETCH FIRST 10 ROWS ONLY');

ATTACH 'profile://warehouse' AS db2 (TYPE adbc);
USE db2.SALES;
SELECT COUNT(*) FROM ORDERS;
CREATE TABLE ORDERS_2024 AS SELECT * FROM memory.staged_orders;   -- bulk ingest into Db2
INSERT INTO ORDERS_2024 SELECT * FROM memory.late_orders;

Both DuckDB extensions are exercised in this repo's test suite (python/tests/test_adbc_scanner.py, python/tests/test_duckdb_adbc_client.py).

Alternative: drive adbc_driver_manager directly

from adbc_driver_manager import dbapi
import adbc_driver_db2

conn = dbapi.connect(
    driver=adbc_driver_db2._driver_path(),
    entrypoint="Db2DriverInit",
    db_kwargs={"uri": "db2://host:50000/SAMPLE", "username": "u", "password": "p"},
)

Connection profiles and the driver manifest

python -m adbc_driver_db2 install-manifest

writes a db2.toml ADBC driver manifest so the driver resolves by name from any ADBC consumer — adbc_driver_manager.dbapi.connect(uri="db2://..."), DuckDB's adbc_connect({'driver': 'db2', ...}), DBeaver's ADBC connection type — and from connection profiles:

# ~/.config/adbc/profiles/warehouse.toml
driver   = "db2"
uri      = "db2://db2host:50000/SAMPLE?schema=SALES"
username = "reporting"
password = "********"

from adbc_driver_manager import dbapi
conn = dbapi.connect(profile="warehouse")

Go

import (
    "github.com/apache/arrow-adbc/go/adbc"
    "github.com/apache/arrow-go/v18/arrow/memory"
    "github.com/gizmodata/adbc-driver-db2/driver/db2"
)

drv := db2.NewDriver(memory.DefaultAllocator)
database, _ := drv.NewDatabase(map[string]string{
    adbc.OptionKeyURI:      "db2://host:50000/SAMPLE",
    adbc.OptionKeyUsername: "db2inst1",
    adbc.OptionKeyPassword: "********",
})
conn, _ := database.Open(ctx)
stmt, _ := conn.NewStatement()
stmt.SetSqlQuery("SELECT * FROM SYSCAT.TABLES")
reader, _, _ := stmt.ExecuteQuery(ctx)
for reader.Next() { rec := reader.RecordBatch(); ... }

Bulk ingest from Go:

stmt, _ := conn.NewStatement()
stmt.SetOption(adbc.OptionKeyIngestTargetTable, "ORDERS_COPY")
stmt.SetOption(adbc.OptionKeyIngestMode, adbc.OptionValueIngestModeCreateAppend)
stmt.BindStream(ctx, reader)          // any array.RecordReader — e.g. from Parquet, Flight, or another ADBC driver
rows, _ := stmt.ExecuteUpdate(ctx)

The internal/drda package is a self-contained DRDA client (connect, describe, execute, streaming cursors, parameter binding, LOBs) that the ADBC layer sits on.

Type mapping

Every field carries db2:type, db2:length, db2:precision, db2:scale metadata; a schema produced by this driver round-trips through bulk ingest with the original Db2 types.

Development

go test ./...                                   # unit tests (no server needed)
DB2_HOST=localhost go test ./...                # integration + ADBC conformance suite
go build -buildmode=c-shared -tags driverlib -o pkg/db2/libadbc_driver_db2.dylib ./pkg/db2
ADBC_DB2_LIBRARY=$PWD/pkg/db2/libadbc_driver_db2.dylib pip install -e ".[test]"
DB2_HOST=localhost pytest

A Db2 for testing: docker run -d -p 50000:50000 --privileged -e LICENSE=accept -e DB2INST1_PASSWORD=password -e DBNAME=testdb icr.io/db2_community/db2 (first start takes ~10 minutes). go run ./cmd/drda-sniff is a transparent proxy that decodes DRDA traffic — handy when comparing this driver's messages with IBM's own clients.

License

MIT — see LICENSE. DRDA is an open standard published by The Open Group; this implementation was written from the specification and the open-source Apache Derby and pydrda clients.