ArpeDb2 — Troubleshooting
Common failures and their fixes, grouped by where they show up: connecting, TLS, authenticating, Db2 for i, the licence gate, querying, metadata and ingesting. For the options referenced here, see Connection.
Record a protocol trace and send it with the error message: set
ARPEDB2_TRACE=hex and ARPEDB2_TRACE_FILE=<path> in the environment of the
failing program and run it once. Passwords and security tokens are never
written to the trace, but SQL text and data are unless ARPEDB2_TRACE_SQL=off
is set too: review the file first. See
Protocol trace.
Connection
Cannot connect: adbc.arpedb2.<field> is required. One of server,
database, username and password was not supplied by a discrete option,
the connection string or the URI. The connect is refused before the socket
opens. See
Three ways to connect.
The connect hangs, then fails. The Db2 server cannot be reached on that
host and port: a firewall, a wrong port, or an instance that is not
listening. Db2 for LUW listens on 50000 by default and Db2 for i on
446; check with nc -vz <host> <port>. Lower
adbc.arpedb2.login_timeout (30 seconds by default) to fail faster while you
look.
login timed out after N s (adbc.arpedb2.login_timeout). The TCP connect,
the TLS handshake and the logon together took longer than the login timeout.
The usual cause is sslmode set while port is the plain port: the server
waits for a DRDA request and never answers the TLS handshake. Db2 uses a
separate port for TLS.
DSS magic mismatch: expected 0xd0, got header bytes 15 03 03 .... The
opposite case: port is the server's SSL port and sslmode is not set (or
is disable). Set adbc.arpedb2.sslmode.
database not found (RDBNFNRM). The database option, or the path of the
URI, must be the Db2 database name (for example sample), not an
instance or a schema, and the database must be started on that instance.
uri refused: "expected '<scheme>://…'". An ADO.NET Key=Value;
string was passed to the uri option. Use adbc.arpedb2.connection_string
for that grammar; uri takes a db2:// or arpedb2:// URL.
Unknown uri query parameter '…'. The URI accepts only user,
password, database / dbname, sslmode, sslrootcert and sslhostname.
A value given twice (a database in both the path and dbname=) is refused
too. Set every other option as a discrete option.
... is too long (N bytes; at most M) when setting an option. The value
does not fit the option (255 bytes for server, username and password;
18 for database; 1023 for file paths). It is refused rather than cut short.
A token longer than 255 bytes cannot be sent in the DRDA password field.
Every server of alternate_servers fails. The error lists each server and
its own error. A wrong password or an unknown database stops at the first
server, on purpose: see
Connect retry and alternate servers.
TLS / certificates
The connect waits, then fails with login timed out. port is probably
the plain port. Db2 uses implicit TLS on a dedicated SSL port (for example
50001 next to a plain 50000; 448 on IBM i).
TLS handshake failed: ... certificate verify failed with verify-full.
Either the server's CA is not trusted, or the name does not match:
- For a private or self-signed CA, set
adbc.arpedb2.sslrootcertto the CA's PEM file. - The name you connect to must be in the certificate. Connecting by IP
address, through an alias or through a tunnel fails that check: connect with
the certificate's name, or set
adbc.arpedb2.sslhostnameto it.
"sslmode=verify-ca needs adbc.arpedb2.sslrootcert". verify-ca checks no
host name, so it only means something against your own CA. Set sslrootcert
to the CA that signed the server certificate, or use verify-full, which
also accepts a certificate from the system trust store because it checks the
host name.
Certificate verification fails with sslmode=require. When
sslrootcert is set, require verifies the certificate chain against it.
Point it at the right CA, or remove it to encrypt without verifying.
To see what was negotiated, read the connection options
adbc.arpedb2.tls.version, tls.group and tls.cipher, or set
ARPEIO_ADBC_LOG_TLS=1 to print one line per handshake on standard error.
Authentication
security check failed: password invalid (SECCHKCD 0x0f), or another
security check error. The user name or the password is wrong, the password
has expired, or the user lacks CONNECT on the database. Confirm the
credentials with another client first.
A warning about a clear-text password on standard error. The connection
has no TLS and the server only takes a plain password (Db2's default
AUTHENTICATION=SERVER). Use TLS, or set
adbc.arpedb2.auth.require_encryption=true to refuse such a logon. See
User and password.
"the server does not offer encrypted password logon … and
adbc.arpedb2.auth.require_encryption refuses to send the password in
clear". That option is set, the connection has no TLS and the server only
takes a plain password. Use TLS (sslmode=verify-full on the server's SSL
port), set the server to AUTHENTICATION=SERVER_ENCRYPT, or drop the option
if a plain password on this network is acceptable.
"the server does not accept SECMEC 0x.. … it offers SECMEC …". The server
refused the requested logon mechanism. With adbc.arpedb2.auth.mechanism
unset, the driver negotiates the plain or the encrypted password logon by
itself, so this means the server offers neither. With an explicit
auth.mechanism, the driver does not switch: the message says which value to
use (for example encrypted_password against Db2
AUTHENTICATION=SERVER_ENCRYPT), or leave the option unset.
"server refused encrypted password logon with DES; it accepts AES". The
server (Db2 ALTERNATE_AUTH_ENC=AES_ONLY) refuses DES and
adbc.arpedb2.auth.encryption is des. Use auto or aes.
"auth.mechanism=token requires TLS that verifies the server
certificate". A token connection needs sslmode=verify-full, or
verify-ca or require with sslrootcert.
Kerberos on Linux: "needs MIT Kerberos, which was not found". The driver
loads MIT Kerberos at the first Kerberos logon and this host has none:
install krb5-libs (RHEL, Rocky, Fedora) or libgssapi-krb5-2 (Debian,
Ubuntu). The other logons do not need it.
Kerberos: "SPN required". Set adbc.arpedb2.kerberos.spn to the Db2
service principal, <DB2INSTANCE>/<fqdn>@<REALM>. It is never derived from
the host name.
Kerberos on Windows: an SSPI status such as 0x80090303. The service
principal name is wrong or unknown to the domain (SEC_E_TARGET_UNKNOWN).
kerberos.cred=keytab is not available on Windows.
Db2 for i
SQLCODE -805, SQLSTATE 51002: package <collection>.SYSSH200 not
found. The server has no package for the driver's statements. With
adbc.arpedb2.package_auto_bind=true (the default) the driver binds it, and
you only see this error when the bind itself failed: the message then gives
the reason. Usually the library named by adbc.arpedb2.package_collection
does not exist or you may not create objects in it. Set the option to a
library you own, or have the package bound once by someone who can. See
Packages.
SQLCODE -551, SQLSTATE 42501 on NULLID.SYSSH200 PACKAGE. The package
exists but your profile is not authorized to it. This happens with
package_collection=NULLID set explicitly or with package_auto_bind=false.
Set adbc.arpedb2.package_collection to a library you own, or leave it
unset: the driver then binds its own copy in your current library.
Every connection takes about half a second longer than expected. Your
profile has no usable current library (none, or an IBM Q… library), so the
driver keeps its package in QTEMP and binds it again on every connection.
Set adbc.arpedb2.package_collection to a library you own.
SQLCODE -7008, SQLSTATE 55019 on an INSERT, UPDATE or DELETE (bulk
ingest included). The table is not journaled, and IBM i refuses changes to
it under commitment control, which every isolation level but no-commit uses.
Three ways out:
- set the connection's isolation level to no-commit,
adbc.connection.transaction.isolation_level=adbc.arpedb2.transaction.isolation.no_commit(nothing can be rolled back then: see Isolation level); - add
WITH NCto the one statement; - start journaling the table or its library.
SQLCODE -615 on DROP PACKAGE <library>.SYSSH200. The package is in use
by your own connection, since every statement runs in it. Drop it from a
connection that uses another package_collection, with the CL command:
CALL QSYS2.QCMDEXC('DLTSQLPKG SQLPKG(<library>/SYSSH200)').
A column fails with "has CCSID N, which this driver cannot decode". The
column's text is in a CCSID the driver has no table for (the list is in
Data types). Cast it in the query to
a Unicode column, for example CAST(col AS VARCHAR(100) CCSID 1208). The same
cast helps with "has no Unicode mapping": the value holds a code its CCSID
does not define, which the driver refuses to replace.
A query fails with SQLCODE +802, SQLSTATE 01519. A DECIMAL or
NUMERIC value needs more than 31 digits. Cast the column to VARCHAR in the
query: see
Data types.
A statement on a schema that does not exist hangs until query_timeout.
Seen on IBM i 7.5 when the missing schema name is longer than 10 characters:
the server never answers. Set adbc.arpedb2.query_timeout so the call fails
instead of waiting forever.
Licence
Connection fails with an ARROW_LIC_* error. No valid Arpeio licence was
found. Supply one with arpeio.adbc.license_file (a .lic path),
arpeio.adbc.license (the blob inline), the ARPEIO_ADBC_LICENCE[_FILE]
environment variables, or an arpeio_adbc.lic file next to the driver
library. The full detail, including how to read the licence state with
arpeio.adbc.license.status, is on the
Licensing page.
Query
A SELECT fails with "unmappable SQLTYPE". The result has a column whose
Db2 type is not mapped (a row type or a structured user-defined type). Cast
the column to a supported type in the query, for example
CAST(col AS VARCHAR(200)). See Data types.
A BOOLEAN column comes back as int16, not bool. Expected on Db2 for
LUW, which describes BOOLEAN to the driver as SMALLINT. See
Boolean.
A VECTOR column comes back as text. Expected: Db2 sends vectors to
remote clients as text. See
Vector.
SQLCODE -418 on SELECT ? FROM .... Db2 cannot type a marker in a
select list. Write CAST(? AS INTEGER), with the type you bind.
ExecutePartitions, ReadPartition or SetSubstraitPlan return
NOT_IMPLEMENTED. Db2 has no partitioned result sets and no Substrait
consumer. Run the query with ExecuteQuery.
Several parameter rows on a query
ExecuteQuery with bound parameters on a query takes exactly one parameter row, got N, or ExecuteUpdate with bound parameters on a statement that returns a result set (INVALID_ARGUMENT). A query was given more than one
parameter row: DB-API executemany on a SELECT, or a bound record batch
with several rows. This is refused on purpose, before anything is sent:
running the query once per row and concatenating the results would give rows
that cannot be traced back to the parameter row that produced them. Two ways
to do it instead:
-
A few values: bind them into one query.
ids = [10, 20, 30]
marks = ", ".join("?" * len(ids))
cur.execute(f"SELECT ID, NAME FROM T WHERE ID IN ({marks})", ids) -
Many values, or several columns per row: load them into a temporary table and join against it. The key column tells you which parameter row each result row belongs to.
cur.execute("DECLARE GLOBAL TEMPORARY TABLE SESSION.KEYS (ID INTEGER) "
"ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE")
cur.executemany("INSERT INTO SESSION.KEYS (ID) VALUES (?)", [(i,) for i in ids])
cur.execute("SELECT K.ID, T.NAME FROM SESSION.KEYS K JOIN T ON T.ID = K.ID")For a large key set, bulk ingest (
cur.adbc_ingest(..., temporary=True)) is faster thanexecutemany.
To run the query separately for each row, call execute once per row.
Timeouts, cancel and broken connections
... timed out after N s (adbc.arpedb2.query_timeout); the connection is closed. The server sent nothing for query_timeout seconds. Raise
adbc.arpedb2.query_timeout (or set it to 0, no limit) and reconnect: a
connection is closed after a timeout, and later calls on it fail with
connection closed after an earlier timeout.
connection is no longer usable: an earlier operation was cancelled; reconnect. AdbcConnectionCancel or AdbcStatementCancel interrupted a
request or a result stream on this connection, which shuts its socket down.
Every later call fails this way (status IO, SQLSTATE 08S01). Open a new
connection.
connection is no longer usable: the DRDA reply stream lost sync (...); reconnect. An earlier reply on this connection could not be read to its
end: the server closed the socket in the middle of it, memory ran out, or the
reply was not valid. The connection refuses every later request instead of
reading leftover bytes as the next statement's answer. Close it and open a
new one; Db2 rolls back the uncommitted work when the socket closes.
the connection that produced this result stream was released before the stream; release the stream first. The connection was closed while one of
its result streams was still open, and the stream was read afterwards. Read
or close every result before closing the connection.
all 64 dynamic SQL sections of this connection hold an open cursor.
Each open result stream reserves one of the 64 cursor sections of the
connection until it is read to its end or released. Release a stream you no
longer need, or use another connection.
Locks
SQLCODE -911 SQLSTATE 40001, reason code 68 or 2. The statement
waited on a row another transaction had locked: 68 is a lock timeout, 2 a
deadlock that Db2 resolved by choosing this connection. Db2 has rolled back
the whole unit of work, so everything since the last commit is gone; the
connection stays usable. The error carries vendor code -911 and SQLSTATE
40001, on a fetch and on a CALL as well, so retry logic can rely on them.
To read without waiting on writers, see
concurrent access resolution.
A statement fails with malformed SQLCARD in the server's reply (...).
The outcome of the statement in the server's reply could not be parsed, so
the driver reports an error rather than a success. The connection stays
usable. Check what the statement did before running it again (it may have
succeeded), and report the case with a protocol trace.
Metadata
A schema is missing from GetObjects or GetStatistics. Without a
db_schema filter, or with a pattern, the system schemas are left out: on
Db2 for LUW SYSCAT, SYSFUN, SYSIBM, SYSIBMADM, SYSIBMINTERNAL,
SYSIBMTS, SYSPROC, SYSPUBLIC, SYSSTAT, SYSTOOLS; on Db2 for i
QSYS, QSYS2, SYSIBM, SYSIBMADM, SYSPROC, SYSTOOLS. Name one
exactly (db_schema_filter="SYSCAT") to get it.
A filter matches more than expected. A filter is a LIKE pattern: escape
a _ or % that is part of the name with a backslash (MY\_SCHEMA).
GetObjects: <level> query failed: .... A catalog query failed on the
server; the error carries its SQLCODE and SQLSTATE, for example -911 when
the catalog rows were locked by another connection's uncommitted DDL. A
missing SELECT privilege on the constraint catalog views is tolerated: the
tables and columns come back with no constraints.
GetStatistics returns no approximate statistics for a table. On Db2 for
LUW the catalog statistics are filled by RUNSTATS; a table that has never
had one has none. Ask for exact statistics (approximate=false), or run
RUNSTATS.
Ingest
Parallel loads into one table fail with SQL0911N reason code 2
(deadlock). Each load is one unit of work by default, so its row locks pile
up until Db2 escalates them to a table lock, and the escalations of the
concurrent loads deadlock. Set adbc.arpedb2.ingest.commit_interval_rows
(for example 50000) on every connection, with autocommit on. See
Parallel loads.
A large load fails with SQL0964C (transaction log full). Same cause: the
whole load is one unit of work. adbc.arpedb2.ingest.commit_interval_rows
bounds the log a load needs.
A load fails on one bad row. By default one row the server refuses fails
the load. To load the good rows and get the list of the bad ones, set
adbc.arpedb2.ingest.max_rejected_rows: see
Rejected rows.
timestamp has a time of day, which the DATE column would drop. A
timestamp column was appended to a Db2 DATE column and one value is not
midnight. Cast on the source side, or load into a TIMESTAMP column.
A temporary ingest fails for lack of a table space. temporary=True
creates a declared global temporary table, which needs a user temporary table
space on the database.
Strings are refused as too long. Ingest create makes VARCHAR(4000)
columns for strings. Raise adbc.arpedb2.ingest.varchar_length (up to
32672), or create the table yourself with CLOB columns and use append.
See also
- Connection: connection options
- Authentication: logon mechanisms and TLS
- Licensing: the licence error in context
- Compatibility: is your target supported?