← All databases

Db2 for i into Apache Arrow with ADBC

Db2 for i 7.5 (IBM i, PUB400.COM public server), read and written through adbcBridge — the ADBC driver that loads the database's ODBC driver — verified on Linux and macOS arm64. Everything below is the compatibility matrix's record for this row.

Status

Driver and connection string

ODBC driver

IBM i Access ODBC 1.1.0.29 (libcwbodbc.so 07.01.029, IBM i host servers)

Wire

native ODBC

adbcBridge

0.1.1 (release)

The connection string the matrix used, as the connection strings reference records it (Databases with their own native protocol and ODBC driver); {drv} is the driver library or its name from odbcinst.ini, and the host, port and credentials are the test server's.

Driver={drv};System=<host>;UID=<user>;PWD=<password>;SSL=1;Naming=0;CommitMode=0;DefaultLibraries=<library>;

Python

import adbcbridge

# {drv}: the ODBC driver library (path), or its name from odbcinst.ini
conn = adbcbridge.connect(uri="Driver={drv};System=<host>;UID=<user>;PWD=<password>;SSL=1;Naming=0;CommitMode=0;DefaultLibraries=<library>;")
with conn.cursor() as cur:
    cur.execute("SELECT * FROM my_table")
    table = cur.fetch_arrow_table()      # pyarrow.Table

Polars and pandas

import polars as pl, pandas as pd

df = pl.read_database("SELECT * FROM my_table", connection=conn)   # Polars, Arrow-native
pdf = pd.read_sql("SELECT * FROM my_table", conn)                   # pandas 2.2+, ADBC connection

pip install adbcbridge brings the driver library; the Db2 for i ODBC driver is installed the way its vendor documents, then named in Driver=. Rust, Go, C# and Java use the same connection string through their ADBC driver managers — see the docs.

What the matrix recorded

a different engine and a different wire from the Db2 row above, not DRDA: IBM's own IBM i Access driver on the host-server ports, SQL_DBMS_NAME "DB2/400 SQL" 07.05.0015. Hosted — IBM i runs on Power hardware, there is no container and no emulator, and the entry points at PUB400.COM, the free public IBM i (PUB400_HOST/PUB400_USER/PUB400_PASSWORD; big_rows 3,000, one connection at a time).

What this stack needed

• Driver quirks: the Driver= value may be at most 35 characters (Key value in connection string too long. (30119) at 36 — the driver's own installed path is 36, so a DSN-less connection needs the registered name or a short symlink); the libraries resolve their message catalogues and conversion tables under a compiled-in /opt/ibm/iaccess, and without that directory every diagnostic degrades to CWBNL0202 - cwbodmsg.dll and the real error is lost (root-free route: a bwrap mount namespace).
• Generated ingest DDL spells an Arrow string VARCHAR(8000) CCSID 1208, not the CLOB the driver's SQLGetTypeInfo(SQL_LONGVARCHAR) names (ddl_string_type_name, keyed on SQL_DBMS_NAME "DB2/400"): a CLOB column can be neither array-bound for writing nor bound at all for reading, so every row costs a round trip — 3,000 rows went in at 8 rows/s and came back at 8 rows/s against 926-1,070 and 1,636-8,052 as VARCHAR(8000).
• The width is measured, not chosen: the widest VARCHAR the server reports (32,739) is refused in a four-column table (SQL0101, Db2 for i's row is at most 32,766 bytes) and describes too wide to bind, which puts the read back on SQLGetData — 120 rows/s at VARCHAR(16000), 62 at VARCHAR(32700).
• Server side: CommitMode=0 (*NONE) is required, because a table created with plain CREATE TABLE in a user library is not journaled and cannot be changed under commitment control (SQL7008 … not valid for operation); Naming=0 with DefaultLibraries=<user>1; SSL=1 (host-server ports 9470-9479) verified as well as plain.
• The entry's s column is VARCHAR(50) CCSID 1208 because a plain VARCHAR takes the job CCSID — 273, single-byte EBCDIC, on this host — and loses héllo 🚀 to substitution characters; CCSID 1208 is UTF-8 and round-trips it.
• Unquoted identifiers fold to upper case, SQL_MAX_IDENTIFIER_LEN is 18, and INTEGER, DOUBLE, VARBINARY(n), DATE, TIMESTAMP(6), DECIMAL(10,3) and BOOLEAN are all native 7.5 types needing no tolerance flag.
• First connect needs a password change PUB400 only offers on a 5250 screen: ssh -p 2222 refuses an expired profile outright and the client's own cwbCO_ChangePassword() is refused by this host (CWBSY1008 … rc=400); ingest 926 rows/s, fetch 8.1k rows/s, ~110 ms network round trip

Read next

• A 35-character Driver=, a compiled-in path, and a CLOB at eight rows a second