Skip to main content

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.

For anything not listed here

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.sslrootcert to 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.sslhostname to 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 NC to 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 than executemany.

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​