Skip to main content

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.

Db2 for LUW and Db2 for i

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.

FormOption keyGrammarBest for
Discrete optionsadbc.arpedb2.<field>one option per fieldprogrammatic clients, secrets kept out of a single string
Connection stringadbc.arpedb2.connection_stringADO.NET Key=Value;…pasting a .NET-style connection string
Connection URIuridb2://… URLconfiguration files, portable tooling
Python
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 Addressserver
Portport
Database, Initial Catalogdatabase
User ID, UID, Userusername
Password, PWDpassword
sslmodesslmode
sslrootcertsslrootcert
sslhostname, HostNameInCertificatesslhostname

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:// and arpedb2://. The scheme only selects the URL grammar. It never picks the driver, so db2:// 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/sample targets the database sample.
  • 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 pass adbc.arpedb2.password separately.
  • An IPv6 host is written in brackets: db2://[::1]:50000/sample.
Query parameter(s)Option
userusername
passwordpassword
database, dbnamedatabase
sslmodesslmode
sslrootcertsslrootcert
sslhostnamesslhostname

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/no or on/off, in any case. The standard adbc.connection.autocommit takes true/false/1/0 only.
  • 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​

OptionDefaultMeaning
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.port50000TCP 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​

OptionValuesDefaultMeaning
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.mechanismpassword, encrypted_password, kerberos, tokenunset (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.encryptionauto, aes, desautoAlgorithm 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, falsefalsetrue: 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.creddefault (alias ccache), keytab, passworddefaultWhere the Kerberos credential comes from: the credential cache, a keytab, or username and password.
adbc.arpedb2.kerberos.keytabpath—Keytab file, for kerberos.cred=keytab (Linux).

Two read-only connection options report what the logon used:

OptionValue
adbc.arpedb2.auth.negotiated_mechanismpassword, encrypted_password or kerberos; "" before the connection opens.
adbc.arpedb2.auth.negotiated_encryptionaes or des for an encrypted password logon, "" otherwise.

TLS​

OptionValuesDefaultMeaning
adbc.arpedb2.sslmodedisable, require, verify-ca, verify-fulldisableTLS mode. See TLS for what each mode checks.
adbc.arpedb2.sslrootcertpath—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.sslhostnamehost name or IP addressthe server valueThe 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.

OptionExample
adbc.arpedb2.tls.versionTLSv1.3
adbc.arpedb2.tls.groupX25519MLKEM768, x25519
adbc.arpedb2.tls.cipherTLS_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.

OptionValuesDefaultMeaning
adbc.arpedb2.package_collectionan unquoted identifier, at most 18 charactersDb2 for i: your current library; Db2 for LUW: NULLIDCollection (on Db2 for i, the library) of the package. Folded to upper case.
adbc.arpedb2.package_auto_bindtrue, falsetrueWhen 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​

OptionDefaultMeaning
adbc.arpedb2.login_timeout30Seconds for the whole logon: the TCP connect, the TLS handshake and the DRDA logon together. 0 = no limit.
adbc.arpedb2.query_timeout0Longest 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.

OptionDefaultMeaning
adbc.arpedb2.keepalivetruefalse turns keepalive off.
adbc.arpedb2.keepalive_idle60Seconds of silence before the first probe, 1..32767.
adbc.arpedb2.keepalive_interval10Seconds between probes, 1..32767.
adbc.arpedb2.keepalive_count6Unanswered 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.

OptionDefaultMeaning
adbc.arpedb2.alternate_serversnoneServers 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_count0Extra rounds after the first, 0..100. A round tries server, then each alternate.
adbc.arpedb2.connect_retry_delay3Seconds 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.

OptionRegisterMeaning
adbc.arpedb2.client.application_nameCLIENT_APPLNAMEApplication name. Db2 for LUW also lists it as the application name of the connection (LIST APPLICATIONS); unset, that name is ArpeDb2.
adbc.arpedb2.client.workstationCLIENT_WRKSTNNAMEClient host name.
adbc.arpedb2.client.userCLIENT_USERIDEnd user the work is done for (not the login user).
adbc.arpedb2.client.accountingCLIENT_ACCTNGAccounting 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​

OptionDefaultMeaning
adbc.arpedb2.decfloat_asdoubledouble reads DECFLOAT as float64 (about 16 significant digits); string reads it as utf8 holding the exact value. See Data types.
adbc.arpedb2.timestamp_precisionmicromicro reads every TIMESTAMP(p) as timestamp[us]. exact keeps the digits of TIMESTAMP(7) to (12). See Data types.
adbc.arpedb2.bit_data_ccsid00 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​

OptionDefaultMeaning
adbc.arpedb2.buffer_size32000Rows per Arrow batch, 1..10,000,000. Also a statement option.
adbc.arpedb2.batch_max_bytes0 (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.qryblksz0 (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_bytes67108864 (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_preparetruetrue: 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_resolutiondefaultHow 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:

ValueEffect
defaultThe server decides (on Db2 for LUW, the CUR_COMMIT database setting).
currently_committedRead the last committed version of a locked row instead of waiting for the writer.
wait_for_outcomeWait for the writer to commit or roll back, up to CURRENT LOCK TIMEOUT.
skip_locked_dataSkip 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:

OptionDefaultMeaning
adbc.arpedb2.ingest.commit_interval_rows0With 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_rows00: 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.

OptionEnvironment variableDefaultMeaning
adbc.arpedb2.traceARPEDB2_TRACEoffoff, summary, or hex (summary plus a hex dump of each message).
adbc.arpedb2.trace_fileARPEDB2_TRACE_FILEstandard errorFile the trace is appended to, created readable by its owner only.
adbc.arpedb2.trace_hex_maxARPEDB2_TRACE_HEX_MAX4096Bytes dumped per message at hex level.
adbc.arpedb2.trace_sqlARPEDB2_TRACE_SQLtruefalse 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
Review a trace before sending it

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​

OptionMeaning
arpeio.adbc.licenseLicence blob, inline.
arpeio.adbc.license_filePath to a .lic file.
arpeio.adbc.license.statusRead-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​

OptionValuesMeaning
adbc.connection.autocommittrue (default), falseAutocommit mode, on the database or the connection, before or after connect. See Transactions.
adbc.connection.transaction.isolation_levelsee Isolation levelIsolation level of the following statements.
adbc.connection.db_schemaa schema nameSET 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(...)):

OptionDefaultMeaning
adbc.arpedb2.buffer_sizethe database valueRows per Arrow batch for this statement.
adbc.arpedb2.batch_max_bytesthe database valueByte cap per Arrow batch for this statement.
adbc.arpedb2.defer_preparethe database valueWhether Prepare on this statement is deferred to its first execute.
adbc.arpedb2.concurrent_access_resolutionthe database valueSee Performance.
adbc.arpedb2.bulk.chunk_rows1000Most 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_rowsthe database valueRows between commits during a load under autocommit.
adbc.arpedb2.ingest.max_rejected_rowsthe database value0, -1 or the number of rejected rows to tolerate.
adbc.arpedb2.ingest.rejected_rowsread-onlyJSON array of the rows the last load on this statement rejected. See Rejected rows.
adbc.arpedb2.ingest.varchar_length4000Length of the VARCHAR that ingest create makes for a string column, 1..32672.
adbc.arpedb2.ingest.create_table_suffixnoneText 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)falsetrue 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_paramsunsetComma-separated 1-based positions of the OUT and INOUT parameters of a CALL whose values you want back. See Examples.
adbc.arpedb2.call.out_valuesread-onlyJSON object of the last CALL's OUT/INOUT values, keyed by position, for example {"2":3,"3":50}.
adbc.arpedb2.call.next_result_setunsetSet 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_rows rows), 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. Commit and Rollback are refused with INVALID_STATE. A CALL is committed once the last of its result sets is finished.
  • Autocommit off. Nothing is committed until Commit; Rollback undoes everything since the last one, DDL and the CREATE or DROP of a bulk ingest included. Closing the connection rolls back what was not committed.
  • A failed load (a bulk ingest, or an ExecuteUpdate on bound data such as DB-API executemany) leaves nothing behind. With autocommit on, the driver rolls it back; rows already committed by ingest.commit_interval_rows stay. 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 your Commit or Rollback.
  • 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.
Python

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 valueDb2 levelDb2 for LUWDb2 for i
adbc.connection.transaction.isolation.read_uncommittedUR (*CHG)✅✅
adbc.connection.transaction.isolation.read_committedCS (*CS)✅✅
adbc.connection.transaction.isolation.repeatable_readRS (*ALL)✅✅
adbc.connection.transaction.isolation.serializableRR (*RR)✅✅
adbc.connection.transaction.isolation.defaultthe server's default✅✅ (CS)
adbc.arpedb2.transaction.isolation.no_commitNC (*NONE)refused✅
...isolation.snapshot, ...isolation.linearizablenonerefusedrefused

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 intervalcommit_interval_rows=50000
167 s43 s
43 of 4 fail with SQL0911N28 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 to N rejected rows. 0 (default): one bad row fails the load.
  • Within the limit the call succeeds, and rows_affected counts 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_rows until the next execute on the statement: a JSON array of objects such as {"row": 2, "sqlcode": -803, "sqlstate": "23505", "message": "..."}, where row is 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 a null sqlcode and sqlstate. The first 10,000 rows are listed; GetOptionInt on the same key gives the total.
  • With autocommit off, the good rows stay in the open transaction for you to commit or roll back.
Python
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:

VariableMeaning
ARPEIO_ADBC_LICENCEThe licence blob, inline. See Licensing.
ARPEIO_ADBC_LICENCE_FILEPath to a .lic licence file.
ARPEIO_ADBC_ALLOCATORmimalloc (default) or system: the allocator of the Arrow column buffers. Read once, when the driver library is loaded.
ARPEIO_ADBC_LOG_TLS1: 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_SQLThe 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 return NOT_IMPLEMENTED.
  • Db2 for z/OS.

See also​