DB2 Cheat Sheet: Every SQL Command You Need (2026)

Basic CRUD

The four essential operations for reading, creating, updating, and deleting data in DB2.

SELECT - Read data

-- Select specific columns
SELECT first_name, last_name, email
FROM employees;

-- Select all columns
SELECT * FROM products;

-- Select with alias
SELECT first_name AS name, email AS contact
FROM employees;

-- Single-row select without a table (DB2-specific)
VALUES ('Hello', CURRENT DATE);

INSERT INTO - Add new rows

-- Insert a single row
INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane@example.com');

-- Insert multiple rows
INSERT INTO employees (first_name, last_name, email)
VALUES
    ('Alice', 'Johnson', 'alice@example.com'),
    ('Bob', 'Williams', 'bob@example.com');

-- Insert from a SELECT
INSERT INTO archive_orders
SELECT * FROM orders
WHERE order_date < '2025-01-01';

UPDATE - Modify existing rows

-- Update specific rows
UPDATE employees
SET email = 'newemail@example.com'
WHERE id = 42;

-- Update multiple columns
UPDATE products
SET price = price * 1.10, updated_at = CURRENT TIMESTAMP
WHERE category = 'electronics';

DELETE - Remove rows

-- Delete specific rows
DELETE FROM orders
WHERE status = 'cancelled' AND created_at < '2025-01-01';

-- Delete all rows (use with caution)
DELETE FROM temp_logs;

MERGE - Upsert in a single statement

MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.updated_at = CURRENT TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (id, name, created_at)
    VALUES (s.id, s.name, CURRENT TIMESTAMP);

Filtering & Sorting

Control which rows are returned and in what order. DB2 uses FETCH FIRST instead of LIMIT.

WHERE, AND, OR

SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND (region = 'US' OR region = 'EU');

IN and BETWEEN

-- IN: match any value in a list
SELECT * FROM products
WHERE category IN ('electronics', 'books', 'clothing');

-- BETWEEN: inclusive range
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

LIKE and IS NULL

-- % matches any sequence of characters
SELECT * FROM employees WHERE email LIKE '%@gmail.com';

-- _ matches exactly one character
SELECT * FROM products WHERE sku LIKE 'SKU-____';

-- IS NULL / IS NOT NULL
SELECT * FROM orders WHERE shipped_date IS NULL;

ORDER BY and FETCH FIRST

-- Sort ascending (default) and descending
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- Limit results (DB2 syntax)
SELECT * FROM employees
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;

-- Pagination with OFFSET (DB2 11.1+)
SELECT * FROM employees
ORDER BY created_at DESC
OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY;

Aggregation

Summarize data across multiple rows into a single result.

COUNT, SUM, AVG, MIN, MAX

SELECT
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM orders
WHERE order_date >= '2026-01-01';

GROUP BY and HAVING

-- Revenue by category
SELECT
    category,
    COUNT(*) AS num_products,
    SUM(price) AS total_value
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY total_value DESC;

Remember: WHERE filters rows before grouping. HAVING filters groups after aggregation.

LISTAGG (DB2 11.1+)

-- Concatenate values into a comma-separated list
SELECT department,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS members
FROM employees
GROUP BY department;

ROLLUP, CUBE, GROUPING SETS

-- ROLLUP: subtotals + grand total
SELECT region, category, SUM(sales) AS total_sales
FROM revenue
GROUP BY ROLLUP(region, category);

-- CUBE: all possible subtotal combinations
SELECT region, category, SUM(sales) AS total_sales
FROM revenue
GROUP BY CUBE(region, category);

-- GROUPING SETS: specify exact groupings
SELECT region, category, SUM(sales) AS total_sales
FROM revenue
GROUP BY GROUPING SETS (
    (region, category),
    (region),
    ()
);

JOINs

Combine rows from two or more tables based on a related column.

INNER JOIN

SELECT o.id, o.order_date, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT / RIGHT / FULL OUTER JOIN

-- All customers, even those with no orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT OUTER JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- All products, even those never ordered
SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM order_items oi
RIGHT OUTER JOIN products p ON oi.product_id = p.id
GROUP BY p.product_name;

-- All rows from both tables
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

CROSS JOIN

-- Generate all size/color combinations
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

LATERAL (DB2-specific correlated subquery join)

-- Get the top 3 orders per customer using LATERAL
SELECT c.name, t.order_id, t.total_amount
FROM customers c,
LATERAL (
    SELECT o.id AS order_id, o.total_amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.total_amount DESC
    FETCH FIRST 3 ROWS ONLY
) AS t;

Subqueries & CTEs

Break complex queries into manageable parts.

Scalar subquery

-- Use a subquery that returns a single value
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

IN and EXISTS

-- IN: customers who placed orders over $500
SELECT name, email
FROM customers
WHERE id IN (
    SELECT customer_id FROM orders WHERE total_amount > 500
);

-- EXISTS: products ordered at least once
SELECT p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

WITH (CTE)

WITH monthly_revenue AS (
    SELECT
        YEAR(order_date) AS yr,
        MONTH(order_date) AS mn,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT yr, mn, revenue,
       SUM(revenue) OVER (ORDER BY yr, mn) AS running_total
FROM monthly_revenue
ORDER BY yr, mn;

Recursive CTE

-- Traverse an org chart hierarchy
WITH org_chart (emp_id, name, manager_id, lvl) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.lvl + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT emp_id, name, lvl
FROM org_chart
ORDER BY lvl, name;

OLAP / Window Functions

Perform calculations across a set of rows related to the current row without collapsing them. DB2 was one of the first databases to implement OLAP functions.

ROW_NUMBER, RANK, DENSE_RANK, NTILE

SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk,
    NTILE(4)     OVER (ORDER BY salary DESC) AS quartile
FROM employees;

ROW_NUMBER gives unique sequential numbers. RANK leaves gaps after ties (1,2,2,4). DENSE_RANK has no gaps (1,2,2,3). NTILE(n) divides rows into n roughly equal buckets.

LAG and LEAD

-- Compare each month's revenue to the previous month
SELECT yr, mn, revenue,
    LAG(revenue, 1)  OVER (ORDER BY yr, mn) AS prev_month,
    LEAD(revenue, 1) OVER (ORDER BY yr, mn) AS next_month
FROM monthly_revenue;

SUM() OVER with ROWS BETWEEN

-- Running total
SELECT order_date, total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

-- 3-row moving average
SELECT order_date, total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM orders;

DB2-Specific Features

Features unique to DB2 that you will not find in most other databases.

GENERATED ALWAYS AS IDENTITY

CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    customer_id INTEGER NOT NULL,
    order_date DATE DEFAULT CURRENT DATE,
    PRIMARY KEY (id)
);

SYSIBM.SYSDUMMY1 (like Oracle's DUAL)

-- DB2's single-row dummy table
SELECT CURRENT DATE, CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1;

VALUES (single-row SELECT without a table)

-- No table needed
VALUES (CURRENT DATE, CURRENT TIMESTAMP, 42);

-- Use in a subquery
SELECT * FROM (VALUES (1, 'A'), (2, 'B'), (3, 'C')) AS t(id, code);

FINAL TABLE (like RETURNING)

-- Return inserted rows immediately
SELECT * FROM FINAL TABLE (
    INSERT INTO employees (first_name, last_name, email)
    VALUES ('Jane', 'Smith', 'jane@example.com')
);

-- Return updated rows
SELECT * FROM FINAL TABLE (
    UPDATE employees SET salary = salary * 1.05 WHERE department = 'ENG'
);

SEQUENCE (NEXT VALUE FOR)

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;

-- Use the sequence
INSERT INTO orders (id, customer_id)
VALUES (NEXT VALUE FOR order_seq, 42);

-- Check current value
SELECT PREVIOUS VALUE FOR order_seq FROM SYSIBM.SYSDUMMY1;

Temporal Tables (SYSTEM_TIME, BUSINESS_TIME)

-- System-time versioned table (automatically tracks history)
CREATE TABLE policies (
    id INTEGER NOT NULL,
    name VARCHAR(100),
    sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
    sys_end   TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
    trans_id  TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (sys_start, sys_end),
    PRIMARY KEY (id)
);

CREATE TABLE policies_history LIKE policies;

ALTER TABLE policies ADD VERSIONING USE HISTORY TABLE policies_history;

-- Query data as of a point in time
SELECT * FROM policies
FOR SYSTEM_TIME AS OF '2025-06-15-12.00.00';

-- Business-time table
CREATE TABLE insurance (
    id INTEGER NOT NULL,
    coverage VARCHAR(50),
    bus_start DATE NOT NULL,
    bus_end   DATE NOT NULL,
    PERIOD BUSINESS_TIME (bus_start, bus_end)
);

SELECT * FROM insurance
FOR BUSINESS_TIME AS OF '2026-01-01';

MQTs (Materialized Query Tables)

-- Create an MQT for fast pre-aggregated queries
CREATE TABLE monthly_sales_mqt AS (
    SELECT YEAR(order_date) AS yr,
           MONTH(order_date) AS mn,
           region,
           SUM(total_amount) AS total_sales,
           COUNT(*) AS order_count
    FROM orders
    GROUP BY YEAR(order_date), MONTH(order_date), region
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

-- Populate the MQT
REFRESH TABLE monthly_sales_mqt;

-- Query it like a normal table
SELECT * FROM monthly_sales_mqt
WHERE yr = 2026 AND region = 'US';

String Functions

Manipulate and transform text data in DB2.

-- CONCAT or ||: combine strings
SELECT first_name || ' ' || last_name AS full_name FROM employees;
SELECT CONCAT(CONCAT(first_name, ' '), last_name) AS full_name FROM employees;

-- SUBSTR: extract part of a string
SELECT SUBSTR(phone, 1, 3) AS area_code FROM contacts;

-- TRIM: remove whitespace
SELECT TRIM(name) FROM products;
SELECT TRIM(BOTH ' ' FROM name) FROM products;

-- UPPER / LOWER: change case
SELECT UPPER(email) FROM employees;
SELECT LOWER(city) FROM addresses;

-- LENGTH: count characters
SELECT name, LENGTH(name) AS name_length
FROM products WHERE LENGTH(name) > 50;

-- REPLACE: substitute text
SELECT REPLACE(url, 'http://', 'https://') FROM links;

-- LOCATE: find position of substring
SELECT LOCATE('@', email) AS at_position FROM employees;

-- VARCHAR_FORMAT: format numbers as strings
SELECT VARCHAR_FORMAT(salary, '999,999.99') AS formatted_salary
FROM employees;

-- REGEXP_LIKE (DB2 11.1+)
SELECT * FROM employees
WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@gmail\.com$');

-- XMLSERIALIZE: convert XML to string
SELECT XMLSERIALIZE(XMLELEMENT(NAME "emp", first_name) AS VARCHAR(200))
FROM employees;

Date & Time

DB2 uses special registers (not functions) for current date/time. It also supports labeled durations for date arithmetic.

-- Current date/time special registers
SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1;

-- Date arithmetic with labeled durations (DB2-specific)
SELECT order_date,
       order_date + 7 DAYS AS one_week_later,
       order_date + 3 MONTHS AS three_months_later,
       order_date - 1 YEAR AS one_year_ago
FROM orders;

-- TIMESTAMPADD and TIMESTAMPDIFF
SELECT TIMESTAMPADD(SQL_TSI_DAY, 30, order_date) AS plus_30_days
FROM orders;

SELECT TIMESTAMPDIFF(16, CHAR(CURRENT TIMESTAMP - created_at))
    AS days_since_creation
FROM employees;
-- Note: 16 = days, 32 = hours, 64 = minutes, 128 = seconds

-- Extract parts of a date
SELECT YEAR(order_date) AS yr,
       MONTH(order_date) AS mn,
       DAY(order_date) AS dy,
       DAYOFWEEK(order_date) AS dow
FROM orders;

-- Cast between types
SELECT DATE('2026-03-12') AS d,
       TIME('14:30:00') AS t,
       TIMESTAMP('2026-03-12 14:30:00') AS ts
FROM SYSIBM.SYSDUMMY1;

-- VARCHAR_FORMAT for date formatting
SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS formatted
FROM SYSIBM.SYSDUMMY1;

SELECT VARCHAR_FORMAT(order_date, 'Mon DD, YYYY') AS pretty_date
FROM orders;

-- Filter by relative dates using labeled durations
SELECT * FROM orders
WHERE order_date >= CURRENT DATE - 30 DAYS;

Table Operations

Define and modify your database structure in DB2.

CREATE TABLE (with ORGANIZE BY)

CREATE TABLE employees (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT TIMESTAMP
);

-- Column-organized table (DB2 BLU Acceleration)
CREATE TABLE sales_data (
    id INTEGER NOT NULL,
    region VARCHAR(50),
    amount DECIMAL(12,2),
    sale_date DATE
) ORGANIZE BY COLUMN;

ALTER TABLE

-- Add a column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Change data type (DB2 syntax)
ALTER TABLE products ALTER COLUMN price SET DATA TYPE DECIMAL(12,2);

-- Add a foreign key
ALTER TABLE orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Drop a column
ALTER TABLE employees DROP COLUMN phone;

DROP TABLE and TRUNCATE

-- Drop table
DROP TABLE temp_imports;

-- Truncate (faster than DELETE, DB2 requires IMMEDIATE)
TRUNCATE TABLE temp_logs IMMEDIATE;

CREATE INDEX

-- Standard index
CREATE INDEX idx_emp_email ON employees(email);

-- Composite index
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

-- Unique index
CREATE UNIQUE INDEX idx_emp_email_uniq ON employees(email);

-- Drop an index
DROP INDEX idx_emp_email;

REORG, RUNSTATS, Views, and Aliases

-- Reorganize table data (critical for DB2 performance)
REORG TABLE employees;

-- Update statistics for the optimizer
RUNSTATS ON TABLE schema.employees WITH DISTRIBUTION AND DETAILED INDEXES ALL;

-- Create a view
CREATE VIEW active_employees AS
SELECT id, first_name, last_name, email
FROM employees
WHERE status = 'active';

-- Create an alias (DB2-specific shortcut)
CREATE ALIAS emp FOR employees;

Pro Tips

Write faster, more efficient DB2 queries and leverage DB2-specific tools.

EXPLAIN with db2expln

-- From the command line: explain a query plan
db2expln -d mydb -f query.sql -g -t

-- Inside SQL: populate explain tables
EXPLAIN PLAN FOR
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- Then query the explain tables
SELECT * FROM EXPLAIN_STATEMENT;

db2advis (Index Advisor)

-- Let DB2 recommend indexes for a workload
db2advis -d mydb -i workload.sql -o recommendations.sql

db2top Monitoring

-- Real-time monitoring (like top for DB2)
db2top -d mydb

-- Key screens:
-- D = database overview
-- L = locks
-- S = SQL statements (top queries by CPU/IO)
-- T = tablespaces

ADMIN_CMD Procedures

-- Run admin commands via SQL (useful in apps/scripts)
CALL SYSPROC.ADMIN_CMD('REORG TABLE employees');
CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE schema.employees WITH DISTRIBUTION');
CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/data.csv OF DEL SELECT * FROM employees');

Range Partitioning

CREATE TABLE sales (
    id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE(sale_date) (
    PART q1_2026 STARTING ('2026-01-01') ENDING ('2026-03-31'),
    PART q2_2026 STARTING ('2026-04-01') ENDING ('2026-06-30'),
    PART q3_2026 STARTING ('2026-07-01') ENDING ('2026-09-30'),
    PART q4_2026 STARTING ('2026-10-01') ENDING ('2026-12-31')
);

MDC (Multi-Dimensional Clustering)

-- Cluster data by multiple dimensions for faster range queries
CREATE TABLE transactions (
    id INTEGER NOT NULL,
    region VARCHAR(20),
    product_type VARCHAR(30),
    txn_date DATE,
    amount DECIMAL(10,2)
) ORGANIZE BY DIMENSIONS (region, product_type, txn_date);

Compression

-- Enable row compression
ALTER TABLE large_table COMPRESS YES;
REORG TABLE large_table;

-- Adaptive compression (automatic, DB2 10.1+)
ALTER TABLE large_table COMPRESS YES ADAPTIVE;
REORG TABLE large_table;

See also: SQL Cheat Sheet for universal SQL syntax, What is SQL? for fundamentals, and our conversion tools for MySQL to DB2, Oracle to DB2, and PostgreSQL to DB2.

Frequently Asked Questions

What is DB2 LUW and how is it different from DB2 for z/OS?

DB2 LUW (Linux, UNIX, Windows) is IBM's relational database for distributed platforms. DB2 for z/OS runs on mainframes. While both share the DB2 name and core SQL, they differ in system catalog views, utility commands, storage management, and some SQL extensions. This cheat sheet covers DB2 LUW syntax.

Does DB2 support LIMIT like MySQL or PostgreSQL?

DB2 does not use LIMIT. Instead, use FETCH FIRST n ROWS ONLY to limit results. Starting with DB2 11.1, you can also use OFFSET for pagination: SELECT * FROM employees ORDER BY id OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY.

How do I get the current date and time in DB2?

Use the special registers: CURRENT DATE for the date, CURRENT TIME for the time, and CURRENT TIMESTAMP for the full timestamp. Unlike some databases, these do not use parentheses. They are registers, not functions.

What is the DB2 equivalent of Oracle's DUAL table?

DB2 uses SYSIBM.SYSDUMMY1 as its single-row dummy table. For example: SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1. Alternatively, DB2 supports the VALUES clause for single-row queries without a table: VALUES (CURRENT DATE).

Can AI generate DB2 SQL for me?

Yes. Tools like AI2SQL let you describe what you need in plain English and generate DB2-compatible SQL automatically. This is especially useful for complex DB2 features like temporal queries, MQTs, and OLAP functions where the syntax can be tricky to remember.