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
- Connect with a URI or a connection string
- Straight to pandas / Polars / DuckDB
- Query with parameters
- Bulk ingest an Arrow table
- Export a query to Parquet
- Fetch just the schema
- Stored procedures
- TLS and Kerberos
- Loading by explicit path
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:
- Python
- C / C++
- C#
- Go
- Java
- R
- Rust
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)
#include <cstdio>
#include <cstdlib>
#include <arrow-adbc/adbc.h>
#include <arrow-adbc/adbc_driver_manager.h>
#define CHECK(EXPR) \
if (AdbcStatusCode s = (EXPR); s != ADBC_STATUS_OK) { \
std::fprintf(stderr, "%s failed: %s\n", #EXPR, \
error.message ? error.message : "(unknown)"); \
std::exit(1); \
}
int main() {
AdbcError error = {};
AdbcDatabase database = {};
CHECK(AdbcDatabaseNew(&database, &error));
// Load the driver by name from its ADBC manifest.
CHECK(AdbcDatabaseSetOption(&database, "driver", "arpedb2", &error));
CHECK(AdbcDatabaseSetOption(&database, "adbc.arpedb2.server", "db2.example.com", &error));
CHECK(AdbcDatabaseSetOption(&database, "adbc.arpedb2.port", "50000", &error));
CHECK(AdbcDatabaseSetOption(&database, "adbc.arpedb2.database", "sample", &error));
CHECK(AdbcDatabaseSetOption(&database, "adbc.arpedb2.username", "db2inst1", &error));
CHECK(AdbcDatabaseSetOption(&database, "adbc.arpedb2.password", "<pw>", &error));
CHECK(AdbcDatabaseInit(&database, &error));
AdbcConnection connection = {};
CHECK(AdbcConnectionNew(&connection, &error));
CHECK(AdbcConnectionInit(&connection, &database, &error)); // logs on
AdbcStatement statement = {};
CHECK(AdbcStatementNew(&connection, &statement, &error));
CHECK(AdbcStatementSetSqlQuery(&statement,
"SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY", &error));
ArrowArrayStream stream = {}; // Arrow C Data interface
int64_t rows_affected = -1;
CHECK(AdbcStatementExecuteQuery(&statement, &stream, &rows_affected, &error));
// consume `stream` with nanoarrow / Arrow C++, then release:
stream.release(&stream);
AdbcStatementRelease(&statement, &error);
AdbcConnectionRelease(&connection, &error);
AdbcDatabaseRelease(&database, &error);
return 0;
}
using Apache.Arrow.Adbc;
using Apache.Arrow.Adbc.DriverManager;
var parameters = new Dictionary<string, string>
{
["adbc.arpedb2.server"] = "db2.example.com",
["adbc.arpedb2.port"] = "50000",
["adbc.arpedb2.database"] = "sample",
["adbc.arpedb2.username"] = "db2inst1",
["adbc.arpedb2.password"] = "<pw>",
};
// Load the driver by name from its ADBC manifest.
using AdbcDriver driver = AdbcDriverManager.FindLoadDriver(
"arpedb2", loadOptions: AdbcLoadFlags.Default);
using AdbcDatabase database = driver.Open(parameters);
using AdbcConnection connection = database.Connect(null);
using AdbcStatement statement = connection.CreateStatement();
statement.SqlQuery = "SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY";
QueryResult result = statement.ExecuteQuery();
using var stream = result.Stream!; // IArrowArrayStream of RecordBatches
Console.WriteLine(stream.Schema);
package main
import (
"context"
"fmt"
"github.com/apache/arrow-adbc/go/adbc/drivermgr"
)
func main() {
ctx := context.Background()
var drv drivermgr.Driver
// Load the driver by name from its ADBC manifest.
db, err := drv.NewDatabase(map[string]string{
"driver": "arpedb2",
"adbc.arpedb2.server": "db2.example.com",
"adbc.arpedb2.port": "50000",
"adbc.arpedb2.database": "sample",
"adbc.arpedb2.username": "db2inst1",
"adbc.arpedb2.password": "<pw>",
})
if err != nil {
panic(err)
}
defer db.Close()
conn, err := db.Open(ctx)
if err != nil {
panic(err)
}
defer conn.Close()
stmt, err := conn.NewStatement()
if err != nil {
panic(err)
}
defer stmt.Close()
if err := stmt.SetSqlQuery("SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY"); err != nil {
panic(err)
}
reader, _, err := stmt.ExecuteQuery(ctx) // (array.RecordReader, int64, error)
if err != nil {
panic(err)
}
defer reader.Release()
fmt.Println(reader.Schema())
}
import java.util.HashMap;
import java.util.Map;
import org.apache.arrow.adbc.core.AdbcConnection;
import org.apache.arrow.adbc.core.AdbcDatabase;
import org.apache.arrow.adbc.core.AdbcStatement;
import org.apache.arrow.adbc.driver.jni.JniDriver;
import org.apache.arrow.memory.BufferAllocator;
import org.apache.arrow.memory.RootAllocator;
import org.apache.arrow.vector.ipc.ArrowReader;
public class ConnectAndQuery {
public static void main(String[] args) throws Exception {
Map<String, Object> parameters = new HashMap<>();
// Load the driver by name from its ADBC manifest.
JniDriver.PARAM_DRIVER.set(parameters, "arpedb2");
parameters.put("adbc.arpedb2.server", "db2.example.com");
parameters.put("adbc.arpedb2.port", "50000");
parameters.put("adbc.arpedb2.database", "sample");
parameters.put("adbc.arpedb2.username", "db2inst1");
parameters.put("adbc.arpedb2.password", "<pw>");
try (BufferAllocator allocator = new RootAllocator();
AdbcDatabase db = new JniDriver(allocator).open(parameters);
AdbcConnection connection = db.connect();
AdbcStatement statement = connection.createStatement()) {
statement.setSqlQuery("SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY");
try (AdbcStatement.QueryResult result = statement.executeQuery()) {
ArrowReader reader = result.getReader();
System.out.println(reader.getVectorSchemaRoot().getSchema());
}
}
}
}
library(adbcdrivermanager)
library(arrow)
# Load the driver by name from its ADBC manifest.
db <- adbc_database_init(
adbc_driver("arpedb2"),
adbc.arpedb2.server = "db2.example.com",
adbc.arpedb2.port = "50000",
adbc.arpedb2.database = "sample",
adbc.arpedb2.username = "db2inst1",
adbc.arpedb2.password = "<pw>"
)
con <- adbc_connection_init(db)
table <- read_adbc(con, "SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY") |>
as_arrow_table()
print(table$schema)
use adbc_core::options::{AdbcVersion, OptionDatabase, OptionValue};
use adbc_core::{Connection, Database, Driver, Statement, LOAD_FLAG_DEFAULT};
use adbc_driver_manager::ManagedDriver;
use arrow_array::RecordBatchReader;
fn main() -> Result<(), Box<dyn std::error::Error>> {
// Load the driver by name from its ADBC manifest.
let mut driver = ManagedDriver::load_from_name(
"arpedb2", None, AdbcVersion::default(), LOAD_FLAG_DEFAULT, None,
)?;
let opt = |k: &str, v: &str| (OptionDatabase::Other(k.into()), OptionValue::String(v.into()));
let mut database = driver.new_database_with_opts([
opt("adbc.arpedb2.server", "db2.example.com"),
opt("adbc.arpedb2.port", "50000"),
opt("adbc.arpedb2.database", "sample"),
opt("adbc.arpedb2.username", "db2inst1"),
opt("adbc.arpedb2.password", "<pw>"),
])?;
let mut connection = database.new_connection()?;
let mut statement = connection.new_statement()?;
statement.set_sql_query("SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY")?;
let reader = statement.execute()?; // impl RecordBatchReader
println!("{}", reader.schema());
Ok(())
}
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:
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
# 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:
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:
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:
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:
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):
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:
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):
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
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:
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:
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:
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:
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.
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
# 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:
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
- Connection: every connection option
- Data types: the Arrow types you get back
- Troubleshooting: when a snippet does not connect