DB2 Cheat Sheet

Difficulty: Beginner · ~one page reference

A dense single-page reference covering the most-used DB2 syntax. Bookmark this page.

Connecting

db2 connect to <dbname> user <user> using <password>
db2 connect reset                -- disconnect
db2 list active databases

DDL — Create / Alter / Drop

-- Create
CREATE TABLE t (
  id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Alter
ALTER TABLE t ADD COLUMN email VARCHAR(254);
ALTER TABLE t DROP COLUMN email;
ALTER TABLE t ALTER COLUMN name SET DATA TYPE VARCHAR(160);
ALTER TABLE t ADD CONSTRAINT chk_id CHECK (id > 0);
ALTER TABLE t DROP CONSTRAINT chk_id;

-- Drop
DROP TABLE t;
DROP INDEX idx_t_name;
DROP VIEW v_summary;

DML — Insert / Select / Update / Delete / Merge

INSERT INTO t (name) VALUES ('a');
INSERT INTO t (name) VALUES ('a'), ('b'), ('c');
INSERT INTO t (name) SELECT name FROM other;

SELECT * FROM t WHERE id > 10 ORDER BY id FETCH FIRST 5 ROWS ONLY;
SELECT name, COUNT(*) FROM t GROUP BY name HAVING COUNT(*) > 1;

UPDATE t SET name = 'x' WHERE id = 1;
UPDATE t SET (name, status) = ('x', 'A') WHERE id = 1;

DELETE FROM t WHERE id = 1;

MERGE INTO t AS dst USING src ON dst.id = src.id
  WHEN MATCHED THEN UPDATE SET dst.name = src.name
  WHEN NOT MATCHED THEN INSERT (id, name) VALUES (src.id, src.name);

Pagination

-- DB2 11.1+: OFFSET / FETCH
SELECT * FROM t ORDER BY id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

Common Functions

Category	Examples
String	SUBSTR(s,1,5), LENGTH(s), UPPER(s), LOWER(s), TRIM(s), s1 || s2
Numeric	ROUND(x,2), CEIL(x), FLOOR(x), MOD(a,b), ABS(x)
Date	CURRENT_DATE, CURRENT_TIMESTAMP, DAYS(d), MONTHS_BETWEEN(d1,d2), ADD_MONTHS(d,n)
NULL	COALESCE(a,b,c), NVL(a,b), NULLIF(a,b), IFNULL(a,b)
Cast	CAST(x AS DECIMAL(10,2)), DATE(s), TIMESTAMP(s)

Joins (cheat row)

SELECT a.*, b.*
FROM   a INNER JOIN b ON a.id = b.a_id;                 -- only matches
SELECT a.*, b.* FROM a LEFT  JOIN b ON a.id = b.a_id;   -- all a, nulls for unmatched
SELECT a.*, b.* FROM a RIGHT JOIN b ON a.id = b.a_id;
SELECT a.*, b.* FROM a FULL  JOIN b ON a.id = b.a_id;
SELECT a.*, b.* FROM a CROSS JOIN b;                    -- cartesian

Window Functions

SELECT id,
       SUM(amount)  OVER (PARTITION BY dept ORDER BY id) AS running_total,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY id) AS rn,
       RANK()       OVER (ORDER BY amount DESC)          AS rnk,
       LAG(amount)  OVER (ORDER BY id)                   AS prev_amount
FROM   transactions;

System Catalog (read-only metadata)

View	Contents
SYSCAT.TABLES	Every table & view
SYSCAT.COLUMNS	Every column
SYSCAT.INDEXES	Every index
SYSCAT.VIEWS	Every view
SYSCAT.SCHEMATA	Every schema
SYSCAT.REFERENCES	Every foreign key
SYSCAT.CHECKS	Every CHECK constraint
SYSIBM.SYSDUMMY1	The one-row "DUAL" equivalent

Transactions

COMMIT;
ROLLBACK;
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;
SET CURRENT ISOLATION = CS;   -- UR | CS | RS | RR

Useful CLP Commands

db2 list db directory                   # all databases
db2 list tables for schema db2admin     # all tables in a schema
db2 describe table employees            # column-level details
db2 list applications                   # active connections
db2 force application all               # kick everyone off
db2pd -db sample -applications          # live diagnostics
db2exfmt -d sample -1 -o explain.txt    # format last explain

Performance Quick Reference

EXPLAIN PLAN FOR <query>;

RUNSTATS ON TABLE schema.tab WITH DISTRIBUTION AND DETAILED INDEXES ALL;

REORG TABLE schema.tab;
REORG INDEXES ALL FOR TABLE schema.tab;

SELECT bp_hitratio FROM sysibmadm.bp_hitratio;

Next Up

That's the cheat sheet — circle back to any earlier topic when you need depth on one of these commands.