Oninit® SQLMorph — DB2 LUW
SPL converts to DB2 LUW SQL PL — the closest non-Informix analogue to SPL. Control flow (IF / WHILE / FOR ... AS SELECT ... DO), exception handling (DECLARE ... HANDLER FOR SQLEXCEPTION), and cursors all map cleanly. Each input procedure produces a single <name>.sql file containing the DB2 install script.
What gets emitted
| File | Contents |
|---|---|
| <name>.sql | CREATE OR REPLACE PROCEDURE or FUNCTION in SQL PL. Includes --#SET TERMINATOR @ preamble so DB2 CLP processes the body's internal semicolons. Install with db2 -td@ -vf <name>.sql. |
| REPORT.md | Only emitted when constructs were flagged. Each entry carries file:line, the construct, and a one-line note explaining what to review. |
Type mapping
| SPL type | DB2 type |
|---|---|
| SMALLINT | SMALLINT |
| INT / INTEGER / SERIAL | INTEGER |
| INT8 / BIGINT / SERIAL8 / BIGSERIAL | BIGINT |
| FLOAT / DOUBLE PRECISION | DOUBLE |
| SMALLFLOAT / REAL | REAL |
| DECIMAL / NUMERIC / MONEY | DECIMAL(p,s) |
| CHAR(n) / VARCHAR(n) | CHAR(n) / VARCHAR(n) |
| LVARCHAR | VARCHAR(32672) or CLOB |
| DATE | DATE |
| DATETIME | TIMESTAMP |
| INTERVAL | VARCHAR(64) — DB2 has no native INTERVAL; customer parses |
| BOOLEAN | BOOLEAN |
| TEXT / CLOB | CLOB |
| BYTE / BLOB | BLOB |
Procedural mapping
| SPL construct | DB2 SQL PL emit |
|---|---|
| DEFINE name TYPE | DECLARE name TYPE — all DECLAREs at top of BEGIN..END |
| LET v = expr | SET v = expr — DB2 has no :=; SET is the assignment |
| IF / ELIF / ELSE / END IF | IF / ELSEIF / ELSE / END IF |
| WHILE / END WHILE | WHILE cond DO ... END WHILE |
| FOR i = a TO b STEP s | SET i = a; WHILE i <= b DO ... SET i = i + s; END WHILE |
| FOREACH SELECT ... INTO ... ; ... END FOREACH | FOR cur AS SELECT ... DO SET v = cur.col; ... END FOR |
| EXIT FOREACH / CONTINUE FOREACH | LEAVE cur1 / ITERATE cur1 |
| ON EXCEPTION ... END EXCEPTION | DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END |
| RAISE EXCEPTION code, isam, "msg" | SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'msg' || ' (orig code ' || code || ')' |
| CALL p(args) | CALL p(args) |
| EXECUTE FUNCTION fn() INTO v | SET v = fn() |
| SELECT ... INTO TEMP <name> [WITH NO LOG] | DECLARE GLOBAL TEMPORARY TABLE SESSION.<name> AS (...) WITH DATA ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED |
| expr::TYPE | CAST(expr AS TYPE) — Informix typecast operator rewritten with DB2 type names |
| FROM a, outer(b) WHERE a.k = b.k | FROM a LEFT OUTER JOIN b ON a.k = b.k |
| "..." string literal | '...' — DB2 reserves "..." for quoted identifiers |
| CURRENT, TODAY, SYSDATE | CURRENT TIMESTAMP, CURRENT DATE, CURRENT TIMESTAMP |
| NVL(...) | COALESCE(...) — ANSI form, DB2 native |
| MDY(m, d, y) | DATE(TIMESTAMP_FORMAT(m || '/' || d || '/' || y, 'MM/DD/YYYY')) |
| DBINFO('sessionid') | APPLICATION_ID() |
| DBINFO('dbname') | CURRENT SERVER |
| DBINFO('sqlca.sqlerrd1') | IDENTITY_VAL_LOCAL() |
Multi-column RETURNING
SPL multi-column return splits two ways on DB2 based on whether the procedure yields rows (iterator) or returns a single tuple:
| SPL shape | DB2 emit shape |
|---|---|
| Iterator (any RETURN ... WITH RESUME) | Stored procedure with DYNAMIC RESULT SETS 1. Body accumulates yields into DECLARE GLOBAL TEMPORARY TABLE SESSION.<name>_yield (re-created per call via WITH REPLACE). A trailing DECLARE CURSOR WITH RETURN over the accumulator hands the row stream back to the caller. |
| Non-iterator with RETURNING t1, t2, ... | Stored procedure with trailing OUT _ret0 t1, OUT _ret1 t2, ... parameters. The body's RETURN a, b, c emits SET _ret0 = a; SET _ret1 = b; SET _ret2 = c;. Caller invokes CALL <name>(?, ?, ?) and reads the bound OUT values. |
| Single-column scalar function | Plain CREATE OR REPLACE FUNCTION <name>(...) RETURNS <type> LANGUAGE SQL DETERMINISTIC READS SQL DATA. |
Time-series workloads
DB2 has no native TimeSeries type (Informix DataBlade equivalent). SPL's TSCreate(...) calls translate to a NULL placeholder with the original arguments preserved inline as a /* ... */ comment, plus a customer hint that DB2 time-series workloads are typically modelled as range-partitioned rowstore tables (optionally with BLU column-organized acceleration) and queried with DB2's analytic SQL functions.
Calibration
DB2 is a full-coverage target alongside MSSQL and Oracle: every SPL construct that has a DB2 analogue translates without a reporter flag. Constructs without an analogue (TimeSeries, sp_tsmaint, <esc0>) get inline comment substitutions and / or REPORT.md flags.