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 — and therefore into GizmoSQL, which embeds DuckDB — so Db2 tables can be joined, filtered and pushed into DuckDB/GizmoSQL tables with plain SQL:

INSTALL adbc_scanner FROM community;
LOAD adbc_scanner;

SET VARIABLE db2 = adbc_connect({
    'driver':   '/path/to/libadbc_driver_db2.so',   -- adbc_driver_db2._driver_path() in Python
    'uri':      'db2://db2host:50000/SAMPLE',
    'username': 'db2inst1',
    'password': '********'
});

-- pull
CREATE TABLE orders AS
  SELECT * FROM adbc_scan(getvariable('db2')::BIGINT, 'SELECT * FROM SALES.ORDERS');

-- or join Db2 with local data without copying it first
SELECT o.ORDER_ID, c.name
FROM adbc_scan(getvariable('db2')::BIGINT, 'SELECT ORDER_ID, CUST_ID FROM SALES.ORDERS') o
JOIN customers c ON c.id = o.CUST_ID;

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.