IBM Db2 Dialect

IBM Db2 Dialect Overview IBM Db2 (specifically Db2 for LUW — Linux/Unix/Windows) is IBM's flagship relational database. This ORM's DB2Dialect targets Db2 LUW and connects through the ibmdb driver, an ODBC-based CLI driver that IBM ships for Node.js. Db2 is a standards-leaning SQL engine, and the dialect reflects that: it uses double-quoted delimited identifiers (like Oracle/ANSI SQL), the SQL-standard OFFSET ... FETCH FIRST pagination clause, GENERATED ALWAYS AS IDENTITY for auto-increment, MERGE INTO for upserts, and CREATE SEQUENCE / NEXT VALUE FOR for sequences. Key facts (from src/dialects/db2/index.ts): Dialect name is 'db2'; library is 'ibmdb' (the actual driver in use). Default port is 50000 (Db2's standard TCP/IP listener). Other structured defaults are host: 'localhost', database: 'testdb', username: 'db2inst1', password: ''. Bind markers are ODBC-style positional ? placeholders, matching ibmdb (not $1/:name). Identifiers are double-quoted. Db2 folds unquoted identifiers to uppercase, so anything emitted through escapeId/quoteIdentifier stays case-sensitive and must be referenced the same way everywhere. Connection The dialect either assembles a Db2 keyword=value connection string from structured options, or uses a raw connectionString you supply verbatim. Internally this becomes: Db2-specific options (DB2DialectOptions): connectionString — a raw override, e.g. "DATABASE=x;HOSTNAME=y;PORT=50000;PROTOCOL=TCPIP;UID=u;PWD=p;". When set, the structured host/port/etc. fields are ignored. schema — default schema for operations (qualifies quoteTable). ssl: true — appends SECURITY=SSL to the connection string. extraConnectionParams — a Record<string, string> of extra keyword=value pairs appended to the string. pool — pool options (max defaults to 10); retry — connection retry options (retries on Db2 network errors like SQL30081N, SQL30061N, SQL1224N). On connect() the dialect opens an ibmdb pool, verifies connectivity by opening and immediately closing one connection, and marks itself connected. Data types Type mapping is handled by getDataTypeSql (via mapDataType). Verified in tests/dialects/db2.test.ts: ORM type descriptor | Generated SQL | --------------------------------------------- | ------------------- | 'STRING' / { key: 'STRING' } | VARCHAR(255) | { key: 'STRING', length: 100 } | VARCHAR(100) | { key: 'STRING', length: 'max' } | CLOB | { key: 'CHAR', length: 10 } | CHAR(10) | 'TEXT' | CLOB | 'INTEGER' | INTEGER | { key: 'INTEGER', length: 1 } / length: 2 | SMALLINT | { key: 'INTEGER', length: 8 } | BIGINT | 'BIGINT' | BIGINT | 'FLOAT' | REAL | 'DOUBLE' | DOUBLE | { key: 'DECIMAL', precision: 12, scale: 4 } | DECIMAL(12,4) | 'BOOLEAN' | BOOLEAN | 'DATE' / 'DATETIME' / 'TIMESTAMP' | TIMESTAMP | { key: 'TIMESTAMP', precision: 12 } | TIMESTAMP(12) | 'DATEONLY' | DATE | 'TIME' | TIME | 'BLOB' | BLOB | 'JSON' | CLOB | 'JSONB' | BLOB | 'UUID' | CHAR(36) | { key: 'ENUM', values: [...] } | VARCHAR(255) | 'GEOMETRY' | DB2GSE.STGEOMETRY| Note that Db2 has no native JSON/JSONB column type, so JSON is stored as CLOB (text) and JSONB as BLOB (binary). ENUM degrades to a plain VARCHAR(255) — Db2 has no enum type. Unknown keys fall back to VARCHAR(255). Pagination — OFFSET ... FETCH FIRST n ROWS ONLY Db2 LUW (9.7+) uses the SQL-standard row-limiting clause rather than MySQL's LIMIT/OFFSET. buildLimitOffset emits OFFSET first, then FETCH FIRST (the order Db2 requires when both are present): This is applied automatically by buildSelectQuery when limit/offset are set: With lock set, buildSelectQuery appends FOR UPDATE (and WITH RS for a shared/'SHARE' lock). Identity columns Db2 auto-increment uses GENERATED ALWAYS AS IDENTITY. A column with autoIncrement: true emits: Because the column is GENERATED ALWAYS, the application cannot supply its own value for it — Db2 always generates it. A defaultValue is ignored when autoIncrement is set. Sequences For standalone sequence-based keys, Db2 offers first-class sequences: nextSequenceValue runs SELECT NEXT VALUE FOR "orderseq" ... FROM SYSIBM.SYSDUMMY1; currSequenceValue uses PREVIOUS VALUE FOR. RETURNING via FINAL TABLE / OLD TABLE Db2 has no RETURNING clause, but it supports the standard data-change-table wrappers, and the dialect uses them when you pass returning. buildInsertQuery and buildUpdateQuery wrap the statement in SELECT ... FROM FINAL TABLE (...); buildDeleteQuery uses OLD TABLE: buildDeleteQuery with { truncate: true } instead emits TRUNCATE TABLE "Users" IMMEDIATE. Upsert — MERGE INTO Db2 has no INSERT ... ON DUPLICATE KEY/ON CONFLICT. buildUpsertQuery emits a MERGE against a single-row VALUES source: If conflictFields is omitted, the first column is used as the match key; if updateOnDuplicate is omitted, every non-key column is updated. Db2 does not support RETURNING on MERGE. Passing returning throws: Other Db2-specific behavior Row compression. Db2TableOptions.compress controls COMPRESS YES ADAPTIVE ('ADAPTIVE'), COMPRESS YES STATIC ('STATIC'), or COMPRESS NO (false). It is emitted on CREATE TABLE and can be changed later via alterTableCompression, which follows the ALTER with a best-effort REORG TABLE (failures swallowed, since REORG needs elevated privileges). Materialized views are MQTs. createMaterializedView emits a CREATE TABLE ... AS (query) DATA INITIALLY DEFERRED REFRESH {DEFERRED|IMMEDIATE} Materialized Query Table, then populates it with REFRESH TABLE unless withData: false. MQTs are identified by TYPE = 'S' in SYSCAT.TABLES. Temporal tables. The dialect has first-class support for Db2's bitemporal features — system-period (createSystemTemporalTable, using GENERATED ALWAYS AS ROW BEGIN/END + PERIOD SYSTEMTIME + ADD VERSIONING) and application-period (createApplicationTemporalTable, PERIOD BUSINESSTIME) — plus FOR SYSTEMTIME / FOR BUSINESSTIME query clauses. Partitioning is RANGE-only. createPartitionedTable emits PARTITION BY RANGE (...); requesting list or hash throws (Db2 only supports RANGE partitioning ...). Recursive CTEs. buildRecursiveCteClause emits standard WITH name(cols) AS (base UNION ALL recursive). Db2 requires UNION ALL for recursion. DROP SCHEMA is RESTRICT-only. Db2 has no CASCADE; the schema must be empty. The dialect always appends RESTRICT. Not supported These throw explicit errors rather than emitting invalid SQL: Extensions — createExtension/dropExtension/getExtensions/ hasExtension all throw (Extensions are not supported by Db2. This is a PostgreSQL-specific feature...). Fulltext indexes — throw (... requires Net Search Extender). Spatial indexes — throw (... requires Db2 Spatial Extender). Transactions Db2 transactions via ibmdb are pinned to a single connection: the connection that issued beginTransaction() must be the one that commits or rolls back. startTransaction() returns a DB2Transaction carrying its own connection, and query() routes any statement whose options.transaction is that object onto the pinned connection (rather than a fresh pooled, autocommit connection). Passing a query to a transaction that has already finished throws. Caveats / not-yet-verified SQL generation is verified; live execution is not. The SQL shown here is produced and asserted by the pure/synchronous build builders in tests/dialects/db2.test.ts, which run without any database connection. Runtime behavior against a live Db2 LUW server (identity generation, MERGE, FETCH FIRST, MQT refresh, temporal versioning) has not been exercised end-to-end here and requires a running Db2 instance plus the ibmdb CLI driver to confirm. ibmdb is an ODBC/CLI driver. It ships a native client library; ensure the platform is supported and the driver installs cleanly before relying on live connectivity. Boolean encoding. escape() renders JS booleans as 1/0, not the Db2 BOOLEAN literals TRUE/FALSE — relevant only for values inlined via escape() rather than bound as ? parameters. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL