ArpeDb2 — Connection guide
Everything you can set to point ArpeDb2 at an IBM Db2 server, in one place: the three ways to supply connection details, every option the driver accepts, its default and effect, how transactions, isolation levels and cancel behave, and the environment variables the driver reads.
For the logon mechanisms and TLS, see Authentication. For the Db2 → Arrow type mapping, see Data types. For the licence, see Licensing.
The same options work against both families. Where the two differ (packages, isolation levels, the no-commit level), the section says so. Db2 for z/OS is not supported yet: see Compatibility.
Three ways to connect
Like every driver of the family, ArpeDb2 accepts three connection forms. They can be mixed: a discrete option always wins over the same field taken from a connection string or a URI, whatever the order they are set.
| Form | Option key | Grammar | Best for |
|---|---|---|---|
| Discrete options | adbc.arpedb2.<field> | one option per field | programmatic clients, secrets kept out of a single string |
| Connection string | adbc.arpedb2.connection_string | ADO.NET Key=Value;… | pasting a .NET-style connection string |
| Connection URI | uri | db2://… URL | configuration files, portable tooling |
import adbc_driver_manager.dbapi as dbapi
# Discrete options
conn = 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)
# Connection string (ADO.NET keywords)
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)
# Connection URI
conn = dbapi.connect(driver="arpedb2", db_kwargs={
"uri": "db2://db2inst1:<pw>@db2.example.com:50000/sample",
}, autocommit=True)
with conn, conn.cursor() as cur:
cur.execute("SELECT * FROM SYSCAT.TABLES FETCH FIRST 10 ROWS ONLY")
print(cur.fetch_arrow_table())
server, database, username and password are required for a
user/password logon. A connection made without one of them fails before the
socket is opened, with Cannot connect: adbc.arpedb2.<field> is required.
With Kerberos, username and password are only needed for
kerberos.cred=password.
Connection string
adbc.arpedb2.connection_string accepts the ADO.NET Key=Value;… grammar:
case-insensitive keywords, quoted values, doubled-quote escapes. An unknown or
repeated keyword is refused.
| Keyword(s) | Option |
|---|---|
Server, Hostname, Host, Data Source, Address, Addr, Network Address | server |
Port | port |
Database, Initial Catalog | database |
User ID, UID, User | username |
Password, PWD | password |
sslmode | sslmode |
sslrootcert | sslrootcert |
sslhostname, HostNameInCertificate | sslhostname |
The Server value also accepts an embedded port, as tcp:host,port or
host,port.
Server=db2.example.com;Port=50001;Database=sample;User ID=db2inst1;Password=<pw>;sslmode=verify-full
Every other option (timeouts, packages, Kerberos, the licence, and so on) is a discrete option.
Connection URI
The standard ADBC uri option takes a Db2-style URL:
<scheme>://[user[:password]@]host[:port][/database][?key=value&…]
- Schemes (case-insensitive, equivalent):
db2://andarpedb2://. The scheme only selects the URL grammar. It never picks the driver, sodb2://does not collide with another Db2 ADBC driver installed on the same machine. - The path segment is the database name, not an instance:
db2://host:50000/sampletargets the databasesample. - The user, password, host and query values are percent-decoded, and query
values read
+as a space. Percent-encode a password that holds@,:,/,?,#,&,%or+, or leave it out of the URI and passadbc.arpedb2.passwordseparately. - An IPv6 host is written in brackets:
db2://[::1]:50000/sample.
| Query parameter(s) | Option |
|---|---|
user | username |
password | password |
database, dbname | database |
sslmode | sslmode |
sslrootcert | sslrootcert |
sslhostname | sslhostname |
An unknown parameter is refused, and so is a value given twice (a database in
both the path and dbname=, for example).
db2://db2inst1:<pw>@db2.example.com:50000/sample
db2://db2inst1:<pw>@db2.example.com:50001/sample?sslmode=verify-full
arpedb2://db2inst1:<pw>@[::1]:50000/sample
An ADO.NET Key=Value; string goes in connection_string, not in uri: the
URI parser requires a scheme://.
Option reference
All options are string-typed and live under the adbc.arpedb2.*
namespace. Set them as ADBC database options before the connection is opened,
unless a section says an option can also be set on the connection or on the
statement.
Values are read the same way at every level:
- Integers: decimal, with an optional sign. A value outside the option's
range is refused. The integer options can also be set and read through the
typed ADBC calls (
SetOptionInt,GetOptionInt). - Booleans:
true/false,1/0,yes/nooron/off, in any case. The standardadbc.connection.autocommittakestrue/false/1/0only. - Names (
decfloat_as,timestamp_precision, and so on): exactly as listed.
A refused value fails with INVALID_ARGUMENT and
Invalid <key> '<value>' (expected ...), and the stored value is unchanged. A
value longer than its option allows (255 bytes for server, username and
password, 18 for database, 1023 for file paths) is refused too, never cut
short.
Target
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.server | — | Host name or IP address of the Db2 server. Also accepts tcp:host,port, tcp:host and host,port (the embedded port is applied). Required. |
adbc.arpedb2.port | 50000 | TCP port, 1..65535. Db2 for LUW listens on 50000 by default, Db2 for i on 446 (DRDA) and 448 (DRDA over TLS). |
adbc.arpedb2.database | — | Database (RDB) name, at most 18 bytes. Required. |
Authentication
| Option | Values | Default | Meaning |
|---|---|---|---|
adbc.arpedb2.username | — | Db2 user name. The standard ADBC option username is the same option. | |
adbc.arpedb2.password | — | Password, or the token with auth.mechanism=token; at most 255 bytes. The standard ADBC option password is the same option. | |
adbc.arpedb2.auth.mechanism | password, encrypted_password, kerberos, token | unset (negotiate) | The logon mechanism. Unset: user/password, with the encrypted logon negotiated with the server. An explicit value is never switched: a server that refuses it fails the connection. See Authentication. |
adbc.arpedb2.auth.encryption | auto, aes, des | auto | Algorithm of the encrypted password logon. auto takes AES when the server lists it; aes never uses DES. |
adbc.arpedb2.auth.require_encryption (alias adbc.arpedb2.require_password_encryption) | true, false | false | true: never send the password in clear. Without TLS, a server that does not offer the encrypted logon fails the connection. Inside TLS it changes nothing. |
adbc.arpedb2.kerberos.spn | <DB2INSTANCE>/<fqdn>@<REALM> | — | The Db2 server's Kerberos service principal name. Required with auth.mechanism=kerberos; never derived from the host name. |
adbc.arpedb2.kerberos.cred | default (alias ccache), keytab, password | default | Where the Kerberos credential comes from: the credential cache, a keytab, or username and password. |
adbc.arpedb2.kerberos.keytab | path | — | Keytab file, for kerberos.cred=keytab (Linux). |
Two read-only connection options report what the logon used:
| Option | Value |
|---|---|
adbc.arpedb2.auth.negotiated_mechanism | password, encrypted_password or kerberos; "" before the connection opens. |
adbc.arpedb2.auth.negotiated_encryption | aes or des for an encrypted password logon, "" otherwise. |
TLS
| Option | Values | Default | Meaning |
|---|---|---|---|
adbc.arpedb2.sslmode | disable, require, verify-ca, verify-full | disable | TLS mode. See TLS for what each mode checks. |
adbc.arpedb2.sslrootcert | path | — | PEM file of the CA that signed the server's certificate. Required for verify-ca. For verify-full, needed only when that CA is not in the system trust store. With require, the chain is verified against it. |
adbc.arpedb2.sslhostname | host name or IP address | the server value | The name verify-full checks the certificate against, when it differs from the name or address you connect to (an IP address, an alias, a load balancer, an SSH tunnel). The other modes ignore it. |
Db2 uses implicit TLS: there is no upgrade inside the connection, so
port must be the server's SSL port (for example 50001 next to a plain
50000), not the plain one.
Once the connection is open, three read-only connection options report
what the TLS handshake negotiated. They are "" on a connection without TLS.
| Option | Example |
|---|---|
adbc.arpedb2.tls.version | TLSv1.3 |
adbc.arpedb2.tls.group | X25519MLKEM768, x25519 |
adbc.arpedb2.tls.cipher | TLS_AES_256_GCM_SHA384 |
The read-only database option adbc.arpedb2.openssl_version gives the
OpenSSL version linked into the driver (for example OpenSSL 3.5.9 ...),
without a licence or a connection. Compare it with an OpenSSL advisory to know
whether an installed driver is affected.
Dialect
The driver picks the Db2 family from the product identifier the server sends
at connect: Db2 for LUW or Db2 for i. The two differ in catalog queries
(SYSCAT or QSYS2) and in some wire details, not in the options you set.
The option adbc.arpedb2.dialect (luw) is accepted but not used: the family
always comes from the server.
Packages
Every statement runs in a DRDA package, SYSSH200, the dynamic SQL package
IBM's own CLI and JDBC drivers use. Db2 for LUW ships it in the NULLID
collection, so there is nothing to do there. Db2 for i has none until a client
binds it, so the driver binds it for you on first use.
| Option | Values | Default | Meaning |
|---|---|---|---|
adbc.arpedb2.package_collection | an unquoted identifier, at most 18 characters | Db2 for i: your current library; Db2 for LUW: NULLID | Collection (on Db2 for i, the library) of the package. Folded to upper case. |
adbc.arpedb2.package_auto_bind | true, false | true | When the server reports the package missing (SQLCODE -805), bind it once and retry the statement. |
These are database options; they cannot be set in a connection string or URI.
On Db2 for i. With package_collection unset, the driver uses your job's
current library (CURLIB). The first connection of a process finds it
through a temporary package in QTEMP, then binds the real package there
once. That first connection takes about half a second longer; later ones find
the package and skip the bind. Binding needs the authority to create objects
in the library. If your profile has no current library, or it is an IBM Q…
library such as QGPL, the driver stays on QTEMP, which works but binds
again on every connection: set package_collection to a library you own.
With package_auto_bind=false there is no lookup and the default collection
is NULLID. Each isolation level other than the default
uses its own package (SYSSH000, SYSSH100, SYSSH300, SYSSH400), bound
the same way on first use.
Sections. A package has 64 cursor sections, and each statement the driver
prepares on a connection gets one of its own. A query can therefore run while
another result on the same connection is still being read. A section stays
reserved while a result stream is open on it; with 64 streams open at once on
one connection, the next query fails with INVALID_STATE until one is
released. Past 64 prepared statements, the least recently used one is prepared
again the next time it runs, without any action on your side.
Timeouts
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.login_timeout | 30 | Seconds for the whole logon: the TCP connect, the TLS handshake and the DRDA logon together. 0 = no limit. |
adbc.arpedb2.query_timeout | 0 | Longest the driver waits for the server during a call after login, in seconds. 0 = no limit. It applies to each wait, not to the whole query, so a long result that keeps arriving never times out. Also a connection option, which can be changed on an open connection. |
When query_timeout expires, the call fails with TIMEOUT and the driver
closes the connection, because the reply it was waiting for cannot be
skipped. Later calls on that connection fail: open a new one. Db2 rolls back
the open transaction of the closed connection.
TCP keepalive
On by default. When the connection has been silent for keepalive_idle
seconds (a long sort on the server before the first row, an idle pooled
connection), the operating system sends a small probe that the server's TCP
stack answers. This keeps firewalls and NAT gateways from dropping the
connection as idle, and if keepalive_count probes go unanswered the call
fails instead of waiting forever (with the defaults, about two minutes after
the server stopped answering). A server that is only busy answers the probes,
so long queries are not affected.
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.keepalive | true | false turns keepalive off. |
adbc.arpedb2.keepalive_idle | 60 | Seconds of silence before the first probe, 1..32767. |
adbc.arpedb2.keepalive_interval | 10 | Seconds between probes, 1..32767. |
adbc.arpedb2.keepalive_count | 6 | Unanswered probes before the connection is declared dead, 1..127. |
On Windows the probe count needs Windows 10 version 1709 or Windows Server 2019 or later; on older versions the idle time and interval still apply.
Connect retry and alternate servers
For jobs that must survive an HADR takeover, a pureScale member change or a server restart. This applies at connect time only: a connection that breaks later is not reconnected, and a query is never sent again.
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.alternate_servers | none | Servers to try, in order, after server: host[:port],host[:port],... (an IPv6 address as [addr]:port; no port means the value of port). At most 16. |
adbc.arpedb2.connect_retry_count | 0 | Extra rounds after the first, 0..100. A round tries server, then each alternate. |
adbc.arpedb2.connect_retry_delay | 3 | Seconds between rounds, 0..3600. |
The next server, or the next round, is tried when the network or the server
process fails (refused, timed out, TLS or protocol error) or when the database
refuses access for now (an HADR standby, a database not activated or
quiesced). A wrong password, a missing authorization or an unknown database
name stops at once: every server would answer the same, and retrying a bad
password can lock the account. login_timeout applies to each attempt. When
every attempt fails, the error lists each server and its error. The read-only
connection option adbc.arpedb2.connected_server gives the host:port the
connection reached.
Client information
Lets DBAs see and classify the driver's sessions. The values are set as the
CURRENT CLIENT_* special registers right after logon, where
MON_GET_CONNECTION, db2pd -applications, WLM workload definitions and the
Db2 for i job information read them.
| Option | Register | Meaning |
|---|---|---|
adbc.arpedb2.client.application_name | CLIENT_APPLNAME | Application name. Db2 for LUW also lists it as the application name of the connection (LIST APPLICATIONS); unset, that name is ArpeDb2. |
adbc.arpedb2.client.workstation | CLIENT_WRKSTNNAME | Client host name. |
adbc.arpedb2.client.user | CLIENT_USERID | End user the work is done for (not the login user). |
adbc.arpedb2.client.accounting | CLIENT_ACCTNG | Accounting string. |
Each takes up to 255 bytes. They are database options and connection options: set on an open connection, the register changes at once, which is useful for pooled connections.
Type rendering
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.decfloat_as | double | double reads DECFLOAT as float64 (about 16 significant digits); string reads it as utf8 holding the exact value. See Data types. |
adbc.arpedb2.timestamp_precision | micro | micro reads every TIMESTAMP(p) as timestamp[us]. exact keeps the digits of TIMESTAMP(7) to (12). See Data types. |
adbc.arpedb2.bit_data_ccsid | 0 | 0 reads FOR BIT DATA columns as binary. A CCSID (for example 37, 273, 819, 1208) reads them as utf8 converted from it. Also a connection option. See Data types. |
Performance
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.buffer_size | 32000 | Rows per Arrow batch, 1..10,000,000. Also a statement option. |
adbc.arpedb2.batch_max_bytes | 0 (no byte cap) | Bytes of Arrow buffers per batch. A batch ends at whichever comes first, buffer_size rows or this many bytes, and always holds at least one row. Useful for consumers with a message size limit (Flight SQL's 16 MiB gRPC cap) when rows are wide. Also a statement option. |
adbc.arpedb2.qryblksz | 0 (default block size) | DRDA query block size in bytes, 512..10,485,760. Larger blocks carry more rows per network round trip. |
adbc.arpedb2.max_lob_bytes | 67108864 (64 MiB) | Largest BLOB, CLOB, DBCLOB or XML value the driver reads, 1..2,147,483,648. A larger value fails the read with an error naming the column; it is never cut short. |
adbc.arpedb2.defer_prepare | true | true: Prepare costs no round trip, and the prepare travels with the statement's first execute. A SQL error in the text is then reported by that execute. false: Prepare prepares at once and reports the error itself. Also a statement option. |
adbc.arpedb2.concurrent_access_resolution | default | How a read treats rows another transaction has changed but not committed. See below. Also a statement option. |
One round trip per query. The driver sends several DRDA requests in one
transmission, so a new SELECT is one network round trip (prepare, describe
and cursor open together) and a SELECT with parameters is two. A statement
without a result set (INSERT, UPDATE, DELETE, MERGE, DDL) runs at
once, without a prepare.
Read-ahead. While one block of a result is decoded, the driver has
already asked the server for the next one, so neither side waits for the
other. It holds at most one more block per open query. Results with BLOB,
CLOB, DBCLOB or XML columns are read one row per request instead.
Concurrent access resolution. The same setting as IBM CLI's
ConcurrentAccessResolution:
| Value | Effect |
|---|---|
default | The server decides (on Db2 for LUW, the CUR_COMMIT database setting). |
currently_committed | Read the last committed version of a locked row instead of waiting for the writer. |
wait_for_outcome | Wait for the writer to commit or roll back, up to CURRENT LOCK TIMEOUT. |
skip_locked_data | Skip locked rows. Db2 for i only; on Db2 for LUW, write SKIP LOCKED DATA at the end of the SELECT instead. |
Allocator. Arrow column buffers are allocated with mimalloc. See Environment variables to switch to the C library allocator.
Ingest
Database options that set the default for every statement, which a statement can override:
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.ingest.commit_interval_rows | 0 | With autocommit on, a bulk ingest or a bound-data ExecuteUpdate commits each time this many rows have been sent. 0 commits once, at the end. See Parallel loads. |
adbc.arpedb2.ingest.max_rejected_rows | 0 | 0: one bad row fails the load. -1 or a row count N: bad rows are set aside and the load goes on while at most N rows were rejected. See Rejected rows. |
Protocol trace
For support: writes the DRDA conversation to a file, one block per message exchanged with the server, with the SQL text of each statement.
| Option | Environment variable | Default | Meaning |
|---|---|---|---|
adbc.arpedb2.trace | ARPEDB2_TRACE | off | off, summary, or hex (summary plus a hex dump of each message). |
adbc.arpedb2.trace_file | ARPEDB2_TRACE_FILE | standard error | File the trace is appended to, created readable by its owner only. |
adbc.arpedb2.trace_hex_max | ARPEDB2_TRACE_HEX_MAX | 4096 | Bytes dumped per message at hex level. |
adbc.arpedb2.trace_sql | ARPEDB2_TRACE_SQL | true | false leaves the SQL text, the parameter values and the result rows out of the trace. |
The environment variables apply when the option is not set, so a trace can be turned on without changing the application:
export ARPEDB2_TRACE=hex ARPEDB2_TRACE_FILE=/tmp/arpedb2-trace.log
Passwords, encrypted logon values and Kerberos tickets are never written: they
show as <redacted>. SQL text is written by default, and at hex level so
are the data rows and the parameter values. Check the file before you send
it, or set trace_sql=false (ARPEDB2_TRACE_SQL=off).
Licence
| Option | Meaning |
|---|---|
arpeio.adbc.license | Licence blob, inline. |
arpeio.adbc.license_file | Path to a .lic file. |
arpeio.adbc.license.status | Read-only; reports <state>;code=<ARROW_LIC_*>;tier=<tier>;expires=<epoch>. |
The driver also reads the shared ARPEIO_ADBC_LICENCE[_FILE] environment
variables and an arpeio_adbc.lic file next to the library. The full
resolution order is on the Licensing page.
Standard ADBC connection options
| Option | Values | Meaning |
|---|---|---|
adbc.connection.autocommit | true (default), false | Autocommit mode, on the database or the connection, before or after connect. See Transactions. |
adbc.connection.transaction.isolation_level | see Isolation level | Isolation level of the following statements. |
adbc.connection.db_schema | a schema name | SET CURRENT SCHEMA (case kept). Reading it returns the current schema. It is not a metadata filter. |
Statement options
Set on the statement handle (Python: cursor.adbc_statement.set_options(...)):
| Option | Default | Meaning |
|---|---|---|
adbc.arpedb2.buffer_size | the database value | Rows per Arrow batch for this statement. |
adbc.arpedb2.batch_max_bytes | the database value | Byte cap per Arrow batch for this statement. |
adbc.arpedb2.defer_prepare | the database value | Whether Prepare on this statement is deferred to its first execute. |
adbc.arpedb2.concurrent_access_resolution | the database value | See Performance. |
adbc.arpedb2.bulk.chunk_rows | 1000 | Most rows a bulk ingest or a bound-data ExecuteUpdate sends in one request. A chunk also stops before its encoded rows pass 1 MiB; a single larger row is sent alone. |
adbc.arpedb2.ingest.commit_interval_rows | the database value | Rows between commits during a load under autocommit. |
adbc.arpedb2.ingest.max_rejected_rows | the database value | 0, -1 or the number of rejected rows to tolerate. |
adbc.arpedb2.ingest.rejected_rows | read-only | JSON array of the rows the last load on this statement rejected. See Rejected rows. |
adbc.arpedb2.ingest.varchar_length | 4000 | Length of the VARCHAR that ingest create makes for a string column, 1..32672. |
adbc.arpedb2.ingest.create_table_suffix | none | Text appended to the CREATE TABLE of ingest create, replace and create_append, after the column list: IN TS1, ORGANIZE BY ROW, DISTRIBUTE BY HASH("ID"), COMPRESS YES, and so on. Sent to the server as written. Not used for a temporary table. |
adbc.ingest.temporary (standard ADBC) | false | true ingests into a declared global temporary table in SESSION. Needs a user temporary table space; cannot be combined with adbc.ingest.target_catalog or target_db_schema. |
adbc.arpedb2.call.out_params | unset | Comma-separated 1-based positions of the OUT and INOUT parameters of a CALL whose values you want back. See Examples. |
adbc.arpedb2.call.out_values | read-only | JSON object of the last CALL's OUT/INOUT values, keyed by position, for example {"2":3,"3":50}. |
adbc.arpedb2.call.next_result_set | unset | Set it, then execute the statement again, to move to the next result set of a CALL without running the procedure again. See Examples. |
Transactions
Db2 has no autocommit mode on the server: the client ends each unit of work itself. The driver does it as IBM's own drivers do.
- Autocommit on (the default). Each statement commits when it ends: DDL
and DML at once, a load at its end (or every
ingest.commit_interval_rowsrows), and a query when its result reaches the end of data or the result stream is released. A reader therefore holds its locks only while it reads.CommitandRollbackare refused withINVALID_STATE. ACALLis committed once the last of its result sets is finished. - Autocommit off. Nothing is committed until
Commit;Rollbackundoes everything since the last one, DDL and theCREATEorDROPof a bulk ingest included. Closing the connection rolls back what was not committed. - A failed load (a bulk ingest, or an
ExecuteUpdateon bound data such as DB-APIexecutemany) leaves nothing behind. With autocommit on, the driver rolls it back; rows already committed byingest.commit_interval_rowsstay. With autocommit off, the load runs under a savepoint and a failure rolls back to it: the statements you ran before it in the transaction stay pending for yourCommitorRollback. - Turning autocommit on commits the open transaction first.
- After a deadlock or a lock timeout (
SQLCODE -911), Db2 has already rolled back the whole unit of work.
The DB-API layer of adbc_driver_manager turns autocommit off unless you
pass autocommit=True to dbapi.connect(). Pass it for read-only work and
for loads that use ingest.commit_interval_rows; otherwise call
conn.commit() yourself.
Isolation level
Set adbc.connection.transaction.isolation_level on an open connection (it
applies from the next statement) or at connect.
| ADBC value | Db2 level | Db2 for LUW | Db2 for i |
|---|---|---|---|
adbc.connection.transaction.isolation.read_uncommitted | UR (*CHG) | ✅ | ✅ |
adbc.connection.transaction.isolation.read_committed | CS (*CS) | ✅ | ✅ |
adbc.connection.transaction.isolation.repeatable_read | RS (*ALL) | ✅ | ✅ |
adbc.connection.transaction.isolation.serializable | RR (*RR) | ✅ | ✅ |
adbc.connection.transaction.isolation.default | the server's default | ✅ | ✅ (CS) |
adbc.arpedb2.transaction.isolation.no_commit | NC (*NONE) | refused | ✅ |
...isolation.snapshot, ...isolation.linearizable | none | refused | refused |
On Db2 for LUW the driver sets the CURRENT ISOLATION special register. Db2
for i has no such register: a statement runs at the isolation level of its
package, so the driver uses one package per level, bound the
first time it is used. A refused value fails with NOT_IMPLEMENTED.
No commit (Db2 for i). adbc.arpedb2.transaction.isolation.no_commit is
IBM i's *NONE. Statements run outside commitment control, which is what lets
you write to a table that is not journaled (otherwise SQLCODE -7008), bulk
ingest included. There is no rollback: each change is permanent as soon as
its statement ends, whatever autocommit says, and a bulk ingest that fails
half way keeps the rows it had written. Since v0.3.9.
Cancelling a request
AdbcStatementCancel and AdbcConnectionCancel can be called from another
thread to interrupt what is in progress on the connection: a request waiting
for its reply or a result stream being read. The interrupted call fails with
CANCELLED. The cancel shuts the socket down, so the connection cannot be
used afterwards: every later call fails with status IO, SQLSTATE 08S01 and
an earlier operation was cancelled; reconnect. Close it and open a new one.
With nothing in progress, a cancel does nothing and the connection stays
usable.
Parallel loads
By default a bulk ingest is one unit of work: every row stays locked until the
commit at the end. When several connections load the same table at once, each
of them soon holds enough row locks for Db2 to escalate them to a table lock,
the escalations collide, and loads fail with SQL0911N reason code 2
(deadlock). The whole load must also fit in the transaction log.
Set adbc.arpedb2.ingest.commit_interval_rows (for example 50000) on every
connection of a parallel load. Each load then commits every 50,000 rows, its
lock count stays under the escalation threshold, the loads run at the same
time, and the log only holds the rows since the last commit. A single load
gains too.
Loads into one table (Db2 12.1, TPC-H LINEITEM, 6M rows) | No interval | commit_interval_rows=50000 |
|---|---|---|
| 1 | 67 s | 43 s |
| 4 | 3 of 4 fail with SQL0911N | 28 s |
| 8 | — | 22 s |
The interval applies only with autocommit on: with autocommit off the application owns the transaction and the driver never commits for it. A load that fails keeps the rows already committed, and its error message says how many. Truncate or delete the target before you retry, as with any bulk loader that commits in batches.
Rejected rows
By default a load sends each chunk of rows atomically: one row the server
refuses fails the chunk, and the load is rolled back. Set
adbc.arpedb2.ingest.max_rejected_rows to load the good rows and set the bad
ones aside. Since v0.3.8.
-1: no limit.N: tolerate up toNrejected rows.0(default): one bad row fails the load.- Within the limit the call succeeds, and
rows_affectedcounts the rows that went in. Over the limit the call fails with an error saying how many rows were rejected, and the load is rolled back like any failed load. - The rejected rows are in the read-only statement option
adbc.arpedb2.ingest.rejected_rowsuntil the next execute on the statement: a JSON array of objects such as{"row": 2, "sqlcode": -803, "sqlstate": "23505", "message": "..."}, whererowis the 0-based index in the bound data. A row the driver itself refused before sending (a value longer than its column, for example) has anullsqlcodeandsqlstate. The first 10,000 rows are listed;GetOptionInton the same key gives the total. - With autocommit off, the good rows stay in the open transaction for you to commit or roll back.
import json
with conn.cursor() as cur:
cur.adbc_statement.set_options(**{"adbc.arpedb2.ingest.max_rejected_rows": "-1"})
cur.executemany("INSERT INTO T VALUES (?, ?)", rows)
rejected = json.loads(cur.adbc_statement.get_option("adbc.arpedb2.ingest.rejected_rows"))
Environment variables
Connections are configured through options, not environment variables. The driver reads only these:
| Variable | Meaning |
|---|---|
ARPEIO_ADBC_LICENCE | The licence blob, inline. See Licensing. |
ARPEIO_ADBC_LICENCE_FILE | Path to a .lic licence file. |
ARPEIO_ADBC_ALLOCATOR | mimalloc (default) or system: the allocator of the Arrow column buffers. Read once, when the driver library is loaded. |
ARPEIO_ADBC_LOG_TLS | 1: print one line on standard error after each TLS handshake, with the TLS version, the key-exchange group, the cipher and the peer. Never key material. |
ARPEDB2_TRACE, ARPEDB2_TRACE_FILE, ARPEDB2_TRACE_HEX_MAX, ARPEDB2_TRACE_SQL | The protocol trace, when the matching option is not set. |
The ARPEIO_ADBC_* variables are shared by every driver of the family. With
Kerberos, MIT Kerberos reads its own variables (KRB5CCNAME, KRB5_CONFIG).
Not supported
- Partitioned execution (
ExecutePartitions,ReadPartition) and Substrait plans returnNOT_IMPLEMENTED. - Db2 for z/OS.
See also
- Authentication: logon mechanisms and TLS
- Data types: the Db2 → Arrow type mapping
- Compatibility: supported Db2 families, versions and platforms
- Examples: copy-paste recipes
- Troubleshooting: common connection failures
- Licensing: supplying your Arpeio licence