IBM DB2 SQL Formatter

How Db2 SQL is tokenized

Db2 sticks close to the SQL standard for literals, and the IBM DB2 mode reads input the same way:

• Strings use single quotes with doubled quotes inside: 'O''Brien'. A backslash is an ordinary character, so a path such as 'C:\data\' is complete as written.
• Double quotes delimit identifiers, typically upper-case catalog names such as "SALES"."ORDERS". A value written as "OPEN" is therefore a column reference, and Db2 will complain it does not exist.
• Prefixed literals are recognised: graphic G'...', national N'...', Unicode U&'...' and hex forms X'4142', BX'...', GX'...' and UX'0041'.
• # and @ are treated as name characters, so an identifier such as #x stays one word.
• Both parameter styles from application code work: ? markers from JDBC and CLI, and :h_order_id host variables from embedded SQL or SQL PL.

Backticks are not Db2 syntax, so a query pasted from MySQL with orders in backticks gets an “is not valid IBM DB2 syntax” error.

Registers, isolation and row limiting

Special registers are kept as one unit: CURRENT DATE, CURRENT TIMESTAMP, CURRENT USER and CURRENT SCHEMA are upper-cased as pairs, never split across lines. Labeled durations in date arithmetic, as in CURRENT TIMESTAMP - 30 days, are left as you wrote them.

Statement-level isolation clauses (WITH UR, WITH CS, WITH RS, WITH RR) end up on the last line of the statement, where reviewers expect to see them. FETCH FIRST 10 ROWS ONLY gets a line of its own after ORDER BY. Its casing is mixed, though (FETCH first 10 ROWS only), because sql-formatter does not list every word of the clause as a Db2 keyword; the same goes for desc. FOR UPDATE OF, OPTIMIZE FOR n ROWS and VALUES CURRENT DATE all format.

Db2 MERGE INTO ... USING (subquery) is one of the better-handled statements: the source subquery is indented, and each WHEN MATCHED / WHEN NOT MATCHED branch starts on a new line. Data-change table references such as SELECT * FROM FINAL TABLE (INSERT INTO ...) are laid out with the inner INSERT indented.

DDL and SQL PL limitations

Table DDL is the weakest area. In GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), the words START and WITH end up on separate lines, and NOT NULL can come out as not NULL. DECLARE GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS breaks after ON. The statements remain valid, but you may want to touch up DDL by hand.

SQL PL procedures with LANGUAGE SQL BEGIN ... END are formatted statement by statement, without indenting the procedure body. If your CLP scripts use an alternative terminator such as @, change it back to semicolons before formatting.

Options for reports and batch SQL

Db2 shops often keep long reporting queries in source control alongside COBOL or Java code. Indent style: Tabular, left-aligned pads clause keywords into a fixed column, a layout familiar from older mainframe code. Standard indentation is easier with deep subqueries. If you keep host-variable lists long, Commas: Leading makes adding and removing variables cleaner in diffs.

Nothing you paste is sent anywhere; the formatting runs locally in the browser tab. To check a query without reformatting it, use the SQL validator.

Examples

select o.order_id, o.customer_id, o.amount, current date - date(o.order_ts) as age_days from sales.orders o where o.status = 'OPEN' and o.order_ts > current timestamp - 30 days order by o.amount desc fetch first 10 rows only with ur

SELECT
  o.order_id,
  o.customer_id,
  o.amount,
  CURRENT DATE - DATE(o.order_ts) AS age_days
FROM
  sales.orders o
WHERE
  o.status = 'OPEN'
  AND o.order_ts > CURRENT TIMESTAMP - 30 days
ORDER BY
  o.amount desc
FETCH first 10 ROWS only
WITH UR

merge into inventory.stock t using (select sku, sum(qty) as qty from inventory.receipts where received_at > current timestamp - 1 day group by sku) s on t.sku = s.sku when matched then update set t.qty = t.qty + s.qty, t.updated_at = current timestamp when not matched then insert (sku, qty, updated_at) values (s.sku, s.qty, current timestamp)

MERGE INTO
  inventory.stock t USING (
    SELECT
      sku,
      sum(qty) AS qty
    FROM
      inventory.receipts
    WHERE
      received_at > CURRENT TIMESTAMP - 1 day
    GROUP BY
      sku
  ) s ON t.sku = s.sku
WHEN MATCHED THEN
UPDATE SET
  t.qty = t.qty + s.qty,
  t.updated_at = CURRENT TIMESTAMP
WHEN NOT MATCHED THEN
INSERT
  (sku, qty, updated_at)
VALUES
  (s.sku, s.qty, CURRENT TIMESTAMP)

with monthly as (select year(order_date) as yr, month(order_date) as mo, sum(amount) as revenue from sales.orders where customer_id = ? group by year(order_date), month(order_date)) select yr, mo, revenue, revenue - lag(revenue) over (order by yr, mo) as change from monthly order by yr, mo

WITH      monthly AS (
          SELECT    year(order_date) AS yr,
                    month(order_date) AS mo,
                    sum(amount) AS revenue
          FROM      sales.orders
          WHERE     customer_id = ?
          GROUP BY  year(order_date),
                    month(order_date)
          )
SELECT    yr,
          mo,
          revenue,
          revenue - lag(revenue) OVER (
          ORDER BY  yr,
                    mo
          ) AS change
FROM      monthly
ORDER BY  yr,
          mo

select order_id, amount into :h_order_id, :h_amount from sales.orders where order_id = :h_key with cs;
select * from final table (insert into sales.orders (customer_id, amount, order_ts) values (42, 99.50, current timestamp))

SELECT
  order_id,
  amount
INTO
  :h_order_id,
  :h_amount
FROM
  sales.orders
WHERE
  order_id = :h_key
WITH CS;

SELECT
  *
FROM
  FINAL TABLE (
    INSERT INTO
      sales.orders (customer_id, amount, order_ts)
    VALUES
      (42, 99.50, CURRENT TIMESTAMP)
  )

Common errors and how to fix them

Related tools

• SQL formatter
• sql formatter/plsql
• sql formatter/transactsql
• sql formatter/postgresql
• sql validator
• sql minifier
• CSV to SQL
• sql style guide