Wrong results without errors against Db2 LUW 12.1/11.5 (UTF-8): non-ASCII text, row framing, LOBs, CALL, bigint panic #12

Thanks for db2-node. We are evaluating it as the driver for an IBM Db2 LUW provider in LibreDB Studio (libredb/libredb-studio#786), because it is the only Node driver we found that needs no IBM CLI driver and ships Linux arm64 binaries. Before building on it we ran it against real servers, and found several decoding problems that return wrong data without raising an error, plus a few smaller gaps. Everything below reproduces with the script at the end.

Environment

• db2-node 1.0.22 (gitHead 68264e0), Node v24.14.0 and Bun 1.4.2, Linux x86_64. Results are identical on both runtimes.
• Servers: icr.io/db2_community/db2:12.1.0.0 and 11.5.9.0, stock configuration (AUTHENTICATION=SERVER), database created by the image with DBNAME, so the codeset is UTF-8 (CCSID 1208). Results are identical on both server versions.

Silent wrong results

1. Non-ASCII text is decoded as EBCDIC 037. The database is UTF-8 and the stored bytes are correct, but the string the driver returns is not:
   SELECT CAST('Grüße 世界' AS VARCHAR(40)) AS V, HEX(...) AS H gives V = "åÊC¯C¤ÁU½oXnð", H = "4772C3BCC39F6520E4B896E7958C" (correct UTF-8).
   A bound parameter round trip SELECT CAST(? AS VARCHAR(40)) with "héllo" returns "ÇCz%%?".
   For an application that reads a row and writes it back, this turns into stored data loss. Possibly related to typeDefinitionName is silently ignored on non-z/OS connections #11 (the ACCRDB type definition is hardcoded), but we have not traced it.
2. An INTEGER in the same row as a DECFLOAT is byte-swapped: SELECT 1 AS I, CAST(1.5 AS DECFLOAT(34)) AS D returns {"I":16777216,"D":"1.5"}. The same happens next to a BOOLEAN.
3. A BOOLEAN column returns phantom rows: a table with 3 rows (TRUE, FALSE, NULL) gives rowCount=7 for SELECT B FROM T.
4. SELECT * on a table with BOOLEAN, TIMESTAMP(0), TIMESTAMP(12), CLOB, BIGINT and XML columns returns rowCount=0 with no error, and the BOOLEAN column is missing from columns.
5. An XML column drops rows whose value is NULL: 3 rows in the table, 1 returned.
6. BIGINT is returned as a JS number, so 9223372036854775807 comes back as 9223372036854776000. Binding the value as a string is refused (expected integer-compatible parameter, got VarChar(...)), so there is no lossless path either way.

Errors

7. Any LOB in the result fails the fetch: CLOB, DBCLOB, BLOB, XMLSERIALIZE(... AS CLOB), and catalog columns that are LOBs such as SYSCAT.COLUMNS."DEFAULT", SYSCAT.VIEWS.TEXT and SYSCAT.ROUTINES.TEXT, all give SQLSTATE=58009, SQLCODE=-30020: Error during fetch: 0x1245("0116"). On 11.5 the message adds sqlrr_fetch: rowset size not spec....
8. TIMESTAMP(0) and TIMESTAMP(12) fail to decode: Protocol error: query ended with undecoded row data ... type=Timestamp len=26 ... buffer too short: expected 26 bytes, got 19. TIMESTAMP(6) works.
9. Every CALL fails with SQLSTATE=07005, SQLCODE=-517, through query, prepare, a transaction and the compatibility layer, for example CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE ...'). Wrapping it as BEGIN CALL ...; END works, without result sets or OUT parameters.
10. Binding a JS bigint aborts the process (SIGABRT), which cannot be caught: panicked at napi-2.16.17/src/bindgen_runtime/js_values/serde.rs:45:28: not yet implemented.
11. Binding into a BOOLEAN, BLOB or CLOB column, or a CAST(? AS ...) list mixing them, fails with Protocol error: parameter descriptor count N does not match parameter count M. A JS Date parameter is refused. A GRAPHIC parameter is padded with U+0000 instead of U+0020.

Security mechanism

12. Against a stock server the client requests SECMEC 9 and the server answers with 3 and 5; the driver then retries with SECMEC 3 and sends the password in the clear (EBCDIC) over a non-TLS connection. This also happens with an explicit securityMechanism: 'encrypted', where we would expect the connect to fail instead of downgrading. We checked it with a byte-forwarding proxy that searches the client bytes for the EBCDIC encoding of the password. Against AUTHENTICATION=SERVER_ENCRYPT the connect is nondeterministic (2 or 3 of 10 succeed, the rest fail with No SECCHKRM ... received UNKNOWN(0x1232) or check_code 0x0F), and with ALTERNATE_AUTH_ENC=AES_ONLY none succeed. TLS (ssl: true with caCert) works well, TLS 1.3 negotiated.

Smaller items

13. The currentSchema option has no effect: VALUES CURRENT SCHEMA still returns the user's schema.
14. serverInfo() returns {"productName":"db2inst1","serverRelease":"SQL12010"}: the instance name where the README shows a product name like DB2/LINUXX8664.
15. queryTimeout rejects on time and closes the connection, but the statement keeps executing on the server (visible in MON_GET_ACTIVITY). There is no cancel API, so this is the only way to stop a query.

What works well

Pagination (FETCH FIRST, OFFSET ... FETCH NEXT), prepare and execute, transactions with commit and rollback, the pool, four concurrent queries on one client, sqlstate and sqlcode on server errors, the connection staying usable after an error, TLS, zero-row results with full columns metadata, and a 200k-row result in about 400 ms.

We would be glad to help with fixes. Is the project still maintained, and would you take pull requests for these?

// db2-node 1.0.22 repro. Usage: DB2_HOST=127.0.0.1 DB2_PORT=50000 DB2_DB=TESTDB DB2_USER=db2inst1 DB2_PASSWORD=... node repro.mjs
import db2 from "db2-node";
import { spawnSync } from "node:child_process";
const cfg = { host: process.env.DB2_HOST ?? "127.0.0.1", port: Number(process.env.DB2_PORT ?? 50000), database: process.env.DB2_DB ?? "TESTDB", user: process.env.DB2_USER ?? "db2inst1", password: process.env.DB2_PASSWORD };
const c = new db2.Client(cfg);
await c.connect();
const run = async (label, sql, params) => {
  try { const r = await c.query(sql, params); console.log(`${label}: rowCount=${r.rowCount} rows=${JSON.stringify(r.rows)} columns=${r.columns.map(x => x.name).join(",")}`); }
  catch (e) { console.log(`${label}: ERROR ${e.message}`); }
};
const ddl = async (sql) => { try { await c.query(sql); } catch {} };
await run("0 database codeset", "SELECT VALUE FROM SYSIBMADM.DBCFG WHERE NAME = 'codeset'");
await run("1 non-ASCII literal", "SELECT CAST('Grüße 世界' AS VARCHAR(40)) AS V, HEX(CAST('Grüße 世界' AS VARCHAR(40))) AS H FROM SYSIBM.SYSDUMMY1");
await run("1b non-ASCII bound parameter round trip", "SELECT CAST(? AS VARCHAR(40)) AS V FROM SYSIBM.SYSDUMMY1", ["héllo"]);
await run("2 INTEGER next to DECFLOAT", "SELECT 1 AS I, CAST(1.5 AS DECFLOAT(34)) AS D FROM SYSIBM.SYSDUMMY1");
await ddl("DROP TABLE REPRO_T");
await c.query("CREATE TABLE REPRO_T (ID INTEGER NOT NULL PRIMARY KEY, B BOOLEAN, TS0 TIMESTAMP(0), TS12 TIMESTAMP(12), CL CLOB(1K), BIG BIGINT, X XML)");
await c.query("INSERT INTO REPRO_T (ID, B, TS0, TS12, CL, BIG, X) VALUES (1, TRUE, '2024-02-29-23.59.59', '2024-02-29-23.59.59.123456789012', 'clob text', 9223372036854775807, '<a>1</a>')");
await c.query("INSERT INTO REPRO_T (ID, B) VALUES (2, FALSE)");
await c.query("INSERT INTO REPRO_T (ID) VALUES (3)");
await run("3 BOOLEAN column alone (3 rows in table)", "SELECT B FROM REPRO_T ORDER BY ID");
await run("4 SELECT * on the table (3 rows in table)", "SELECT * FROM REPRO_T ORDER BY ID");
await run("5 CLOB column", "SELECT CL FROM REPRO_T WHERE ID = 1");
await run("5b SYSCAT.COLUMNS.DEFAULT (a CLOB)", "SELECT COLNAME, \"DEFAULT\" FROM SYSCAT.COLUMNS WHERE TABNAME = 'REPRO_T' FETCH FIRST 1 ROW ONLY");
await run("6 TIMESTAMP(0)", "SELECT TS0 FROM REPRO_T WHERE ID = 1");
await run("6b TIMESTAMP(12)", "SELECT TS12 FROM REPRO_T WHERE ID = 1");
await run("7 BIGINT max read", "SELECT BIG FROM REPRO_T WHERE ID = 1");
await run("7b BIGINT bound as string", "SELECT ID FROM REPRO_T WHERE BIG = ?", ["9223372036854775807"]);
await run("8 XML column (3 rows in table)", "SELECT ID, X FROM REPRO_T ORDER BY ID");
await run("9 CALL", "CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE DB2INST1.REPRO_T')");
await run("9b CALL wrapped in BEGIN ... END", "BEGIN CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE DB2INST1.REPRO_T'); END");
await ddl("DROP TABLE REPRO_T");
await c.close();
const cs = new db2.Client({ ...cfg, currentSchema: "SYSCAT" }); await cs.connect();
console.log("10 currentSchema option:", JSON.stringify((await cs.query("VALUES CURRENT SCHEMA")).rows)); await cs.close();
console.log("serverInfo:", JSON.stringify(await (async () => { const s = new db2.Client(cfg); await s.connect(); const i = await s.serverInfo(); await s.close(); return i; })()));
const child = spawnSync(process.execPath, ["--input-type=module", "-e", `import db2 from "db2-node"; const c = new db2.Client(${JSON.stringify(cfg)}); await c.connect(); await c.query("SELECT ? AS V FROM SYSIBM.SYSDUMMY1", [1n]); console.log("survived");`], { encoding: "utf8" });
console.log(`11 JS bigint parameter: exit status=${child.status} signal=${child.signal} stderr=${(child.stderr || "").split("\n").filter(l => /panic|not yet implemented/.test(l)).join(" | ")}`);

0 database codeset: rowCount=1 rows=[{"VALUE":"utf-8"}] columns=VALUE
1 non-ASCII literal: rowCount=1 rows=[{"V":"åÊC¯C¤Á�U½oXnð","H":"4772C3BCC39F6520E4B896E7958C"}] columns=V,H
1b non-ASCII bound parameter round trip: rowCount=1 rows=[{"V":"ÇCz%%?"}] columns=V
2 INTEGER next to DECFLOAT: rowCount=1 rows=[{"I":16777216,"D":"1.5"}] columns=I,D
3 BOOLEAN column alone (3 rows in table): rowCount=7 rows=[{"B":false},{"B":false},{"B":true},{"B":false},{"B":false},{"B":false},{"B":null}] columns=B
4 SELECT * on the table (3 rows in table): rowCount=0 rows=[] columns=ID,TS0,TS12,CL,BIG,X
5 CLOB column: ERROR SQL Error [SQLSTATE=58009, SQLCODE=-30020]: Error during fetch: 0x1245"("0116")"
5b SYSCAT.COLUMNS.DEFAULT (a CLOB): ERROR SQL Error [SQLSTATE=58009, SQLCODE=-30020]: Error during fetch: 0x1245"("0116")"
6 TIMESTAMP(0): ERROR Protocol error: query ended with undecoded row data; pending_tail=22 progress=stopped at column=1 abs_offset=3 body_offset=1 col_start=0 type=Timestamp drda=0x25 len=26 nullable=true ccsid=1208 byte_order=LittleEndian err=buffer too short: expected 26 bytes, got 19 remaining=19 preview=32 30 32 34 2D 30 32 2D 32 39 2D 32 33 2E 35 39 | 2E 35 39
6b TIMESTAMP(12): ERROR Protocol error: query ended with undecoded row data; pending_tail=6 progress=stopped at column=1 abs_offset=1 body_offset=1 col_start=0 type=Timestamp drda=0x25 len=26 nullable=true ccsid=1208 byte_order=LittleEndian err=buffer too short: expected 26 bytes, got 5 remaining=5 preview=38 39 30 31 32
7 BIGINT max read: rowCount=1 rows=[{"BIG":9223372036854776000}] columns=BIG
7b BIGINT bound as string: ERROR expected integer-compatible parameter, got VarChar("9223372036854775807")
8 XML column (3 rows in table): rowCount=1 rows=[{"ID":1,"X":"<a>1</a>"}] columns=ID,X
9 CALL: ERROR SQL Error [SQLSTATE=07005, SQLCODE=-517]: Query failed: SQLCODE=-517
9b CALL wrapped in BEGIN ... END: rowCount=0 rows=[] columns=
10 currentSchema option: [{"1":"DB2INST1"}]
serverInfo: {"productName":"db2inst1","serverRelease":"SQL12010"}
11 JS bigint parameter: exit status=null signal=SIGABRT stderr=thread '<unnamed>' (1941479) panicked at /usr/local/cargo/registry/src/index.crates.io-1949cf8c6b5b557f/napi-2.16.17/src/bindgen_runtime/js_values/serde.rs:45:28: | not yet implemented | fatal runtime error: failed to initiate panic, error 5, aborting