Skip to main content

ArpeDb2 — Examples & recipes

Copy-paste snippets for the common tasks. All assume the driver is loadable by name (driver="arpedb2"), which the installer sets up through an arpedb2.toml manifest. Otherwise, use Loading by explicit path. For the full option reference behind each db_kwargs, see Connection.


Connect and query to Arrow​

The same recipe in every ADBC client. The driver is loaded by name (arpedb2) and configured with discrete options, so only the language changes:

import adbc_driver_manager.dbapi as dbapi

with dbapi.connect(
driver="arpedb2",
db_kwargs={
"adbc.arpedb2.server": "db2.example.com",
"adbc.arpedb2.port": "50000",
"adbc.arpedb2.database": "sample",
"adbc.arpedb2.username": "db2inst1",
"adbc.arpedb2.password": "<pw>",
},
autocommit=True,
) as conn, conn.cursor() as cur:
cur.execute("SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY")
table = cur.fetch_arrow_table() # pyarrow.Table
print(table.schema)
Autocommit in Python

dbapi.connect() turns autocommit off by default. The recipes on this page pass autocommit=True; when you write with autocommit off, call conn.commit() before closing, or the work is rolled back.

The Python snippets below reuse this dictionary:

Python
db_kwargs = {
"adbc.arpedb2.server": "db2.example.com",
"adbc.arpedb2.port": "50000",
"adbc.arpedb2.database": "sample",
"adbc.arpedb2.username": "db2inst1",
"adbc.arpedb2.password": "<pw>",
}

Connect with a URI or a connection string​

Python
# URI: the path segment is the database name
conn = dbapi.connect(driver="arpedb2", db_kwargs={
"uri": "db2://db2inst1:<pw>@db2.example.com:50000/sample",
}, autocommit=True)

# ADO.NET connection string
conn = dbapi.connect(driver="arpedb2", db_kwargs={
"adbc.arpedb2.connection_string":
"Server=db2.example.com;Port=50000;Database=sample;User ID=db2inst1;Password=<pw>",
}, autocommit=True)

A discrete option wins over the same field in the URI or the connection string, so a password can stay out of them:

Python
conn = dbapi.connect(driver="arpedb2", db_kwargs={
"uri": "db2://db2inst1@db2.example.com:50000/sample",
"adbc.arpedb2.password": "<pw>",
}, autocommit=True)

Straight to pandas / Polars / DuckDB​

Results are already Arrow, so there is no row-by-row conversion:

Python
with conn.cursor() as cur:
cur.execute("SELECT * FROM EMPLOYEE")
tbl = cur.fetch_arrow_table()

df = tbl.to_pandas() # pandas

import polars as pl
pf = pl.from_arrow(tbl) # Polars

import duckdb
duckdb.sql("SELECT WORKDEPT, count(*) FROM tbl GROUP BY 1").show() # DuckDB

Query with parameters​

? markers take their values from the DB-API parameters, for queries as well as for DML:

Python
with conn.cursor() as cur:
cur.execute(
"SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ? AND TABNAME LIKE ?",
["SYSCAT", "COL%"])
print(cur.fetchall())

cur.execute("INSERT INTO T (ID, NAME) VALUES (?, ?)", [1, "x"])
print(cur.rowcount) # 1

cur.executemany("INSERT INTO T (ID, NAME) VALUES (?, ?)", [[2, "y"], [3, "z"]])

A query takes one parameter row per execute. executemany is for statements without a result set: on a query it is refused with INVALID_ARGUMENT. To look up several values, bind them into one query (WHERE ID IN (?, ?, ?)) or join against a temporary table: see Troubleshooting.

An untyped marker in a select list (SELECT ? FROM ...) is a Db2 error (SQLCODE -418): write CAST(? AS INTEGER).

Bulk ingest an Arrow table​

adbc_ingest loads an Arrow table (or a stream of record batches) through parameterised INSERT requests of up to 1000 rows each:

Python
import pyarrow as pa

table = pa.table({
"ID": pa.array([1, 2, 3], pa.int32()),
"NAME": pa.array(["a", "b", "c"], pa.string()),
})

with dbapi.connect(driver="arpedb2", db_kwargs=db_kwargs, autocommit=True) as conn:
with conn.cursor() as cur:
cur.adbc_ingest("MY_TABLE", table, mode="create") # create | append | replace | create_append
with conn.cursor() as cur:
cur.adbc_ingest("MY_TABLE", table, mode="append", db_schema_name="STAGING")
with conn.cursor() as cur:
cur.adbc_ingest("MY_TEMP", table, mode="create", temporary=True) # SESSION.MY_TEMP

The ingest options stay set on a cursor, so use a new cursor when the target changes: a temporary ingest on a cursor that still names a schema is refused.

The column types create makes are in Data types → Ingest.

Large and parallel loads. Commit every N rows so that a load does not hold millions of row locks, and so that several connections can load the same table at once (see Parallel loads):

Python
kw = dict(db_kwargs)
kw["adbc.arpedb2.ingest.commit_interval_rows"] = "50000"

with dbapi.connect(driver="arpedb2", db_kwargs=kw, autocommit=True) as conn:
with conn.cursor() as cur:
cur.adbc_ingest("LINEITEM", reader, mode="append") # reader: a RecordBatchReader

Table options. Place the created table or set its storage with a statement option:

Python
with conn.cursor() as cur:
cur.adbc_statement.set_options(**{
"adbc.arpedb2.ingest.create_table_suffix": "IN TS1 COMPRESS YES",
})
cur.adbc_ingest("MY_TABLE", table, mode="create")

Copying a Db2 table. A result read with ArpeDb2 carries each column's Db2 type in its metadata, and create uses it, so the copy keeps CHAR(3), DECIMAL(9,2) or TIMESTAMP(9):

Python
with conn.cursor() as cur:
cur.execute("SELECT * FROM SALES.ORDERS")
data = cur.fetch_arrow_table()
cur.adbc_ingest("ORDERS_COPY", data, mode="create", db_schema_name="SALES")

Export a query to Parquet​

Python
import pyarrow.parquet as pq

with conn.cursor() as cur:
cur.execute("SELECT * FROM SALES.ORDERS")
reader = cur.fetch_record_batch() # streaming RecordBatchReader
with pq.ParquetWriter("orders.parquet", reader.schema) as w:
for batch in reader:
w.write_batch(batch)

Streaming keeps memory flat whatever the result size. Tune the batch size with adbc.arpedb2.buffer_size and the fetch block with adbc.arpedb2.qryblksz: see Performance.

Fetch just the schema​

Get a query's Arrow schema without running it for data:

Python
with conn.cursor() as cur:
schema = cur.adbc_execute_schema("SELECT EMPNO, FIRSTNME, SALARY FROM EMPLOYEE")
print(schema) # pyarrow.Schema

Stored procedures​

CALL runs through the ordinary cursor. A procedure's result set comes back like a query's:

Python
with conn.cursor() as cur:
cur.execute("CALL SALES.TOP_ORDERS(?)", [10])
table = cur.fetch_arrow_table()

OUT and INOUT parameters​

By default the OUT and INOUT values of a CALL are not returned. Set the statement option adbc.arpedb2.call.out_params to the 1-based positions you want back. For a procedure without a result set of its own, they come back as a one-row result:

Python
import pyarrow as pa

# PROCEDURE DEMO.PROC_OUT(IN a INTEGER, OUT b INTEGER, INOUT c INTEGER)
# SET b = a + 1; SET c = c * 10;
with conn.cursor() as cur:
cur.adbc_statement.set_options(**{"adbc.arpedb2.call.out_params": "2,3"})
# NULL for the OUT position (b); a value for IN and INOUT (a, c)
params = pa.record_batch(
[pa.array([41], pa.int32()), pa.array([None], pa.int32()),
pa.array([5], pa.int32())],
names=["a", "b", "c"],
)
cur.execute("CALL DEMO.PROC_OUT(?, ?, ?)", params)
print(cur.fetchall()) # [(42, 50)]

When the procedure also returns a result set, fetchall() gives that result set, and the OUT and INOUT values are in the read-only statement option adbc.arpedb2.call.out_values, a JSON object keyed by position:

Python
import json

with conn.cursor() as cur:
cur.adbc_statement.set_options(**{"adbc.arpedb2.call.out_params": "2,3"})
cur.execute("CALL DEMO.PROC_RS_OUT(?, ?, ?)", params)
rows = cur.fetchall() # the result set
out = json.loads(cur.adbc_statement.get_option("adbc.arpedb2.call.out_values"))
print(out) # {"2": 42, "3": 50}

In that JSON, DECIMAL values are exact decimal strings ("123.45": parse them with decimal.Decimal), DATE, TIME and TIMESTAMP values are ISO 8601 strings, CLOB and XML values are text, and BLOB values are lowercase hexadecimal.

Several result sets​

A procedure declared DYNAMIC RESULT SETS N returns its first result set through execute(). To move to the next one, set the statement option adbc.arpedb2.call.next_result_set and execute the underlying statement again. The procedure runs once; this only moves to the result set it already opened. When there is none left, the driver answers NOT_FOUND.

Python
import adbc_driver_manager
import pyarrow as pa

def next_result_set(cur):
"""The next result set of the last CALL, or None when there are no more."""
cur.adbc_statement.set_options(**{"adbc.arpedb2.call.next_result_set": "1"})
try:
handle, _ = cur.adbc_statement.execute_query()
except adbc_driver_manager.Error as e:
if e.status_code == adbc_driver_manager.AdbcStatusCode.NOT_FOUND:
return None
raise
return pa.RecordBatchReader._import_from_c(handle.address)

with conn.cursor() as cur:
cur.execute("CALL DEMO.PROC_TWO_RS(?)", [2])
first = cur.fetch_arrow_table() # result set 1
while (reader := next_result_set(cur)) is not None:
print(reader.read_all()) # result set 2, 3, ...

Under autocommit, the work of the CALL is committed once its last result set is finished.

TLS and Kerberos​

Python
# TLS with full verification, on the server's SSL port
conn = dbapi.connect(driver="arpedb2", db_kwargs={
**db_kwargs,
"adbc.arpedb2.port": "50001",
"adbc.arpedb2.sslmode": "verify-full",
"adbc.arpedb2.sslrootcert": "/path/to/ca.pem", # not needed when the CA is in the system store
}, autocommit=True)

# What the handshake negotiated
print(conn.adbc_connection.get_option("adbc.arpedb2.tls.version")) # TLSv1.3

# Kerberos, from the credential cache (run kinit first)
conn = dbapi.connect(driver="arpedb2", db_kwargs={
"adbc.arpedb2.server": "db2.example.com",
"adbc.arpedb2.database": "sample",
"adbc.arpedb2.auth.mechanism": "kerberos",
"adbc.arpedb2.kerberos.spn": "db2inst1/db2.example.com@EXAMPLE.COM",
}, autocommit=True)

See Authentication for the modes and the other logon mechanisms.

Loading by explicit path​

Load-by-name uses an arpedb2.toml ADBC driver manifest on the driver manager's search path (point ADBC_DRIVER_PATH at its directory if needed). Without one, pass the shared library's path as the driver:

Python
conn = dbapi.connect(
driver="/opt/arpeio/libarpedb2_adbc_driver-linux-x64.so", # Windows: r"C:\arpeio\arpedb2_adbc_driver-win-x64.dll"
db_kwargs=db_kwargs,
)

In C# use CAdbcDriverImporter.Load("libarpedb2_adbc_driver-linux-x64.so", "AdbcDriverInit"). Everything after loading is identical. When loading by path, put the licence file arpeio_adbc.lic next to the library, or supply the licence another way (see Licensing).

See also​