Appendix B: DB2 SQL Quick Reference

This appendix provides a compact reference for the SQL dialect supported by IBM DB2 on both z/OS and LUW platforms. It is not a substitute for the IBM Knowledge Center documentation, but it covers the statements and functions you will reach for daily. Platform-specific differences are noted where they matter.

B.1 Data Retrieval --- SELECT

B.1.1 Full SELECT Syntax

WITH cte_name (col1, col2, ...) AS (
    select-statement
)
SELECT [ALL | DISTINCT]
    column_expression [AS alias], ...
FROM table_reference
    [JOIN table_reference ON condition] ...
[WHERE search_condition]
[GROUP BY grouping_expression, ...]
[HAVING search_condition]
[ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST], ...]
[FETCH FIRST n ROWS ONLY]
[FOR READ ONLY | FOR UPDATE OF col1, col2, ...]
[WITH UR | CS | RS | RR]
[OPTIMIZE FOR n ROWS]
;

B.1.2 Join Types

-- Inner join
SELECT ... FROM T1 INNER JOIN T2 ON T1.key = T2.key;

-- Left outer join (preserves all rows from T1)
SELECT ... FROM T1 LEFT OUTER JOIN T2 ON T1.key = T2.key;

-- Right outer join
SELECT ... FROM T1 RIGHT OUTER JOIN T2 ON T1.key = T2.key;

-- Full outer join
SELECT ... FROM T1 FULL OUTER JOIN T2 ON T1.key = T2.key;

-- Cross join (Cartesian product)
SELECT ... FROM T1 CROSS JOIN T2;

-- Lateral join (correlated table function, LUW and z/OS v12+)
SELECT ... FROM T1, LATERAL (SELECT ... FROM T2 WHERE T2.fk = T1.pk) AS L;

B.1.3 Subqueries

-- Scalar subquery
SELECT EMPNO, (SELECT DEPTNAME FROM DEPT WHERE DEPT.DEPTNO = EMP.WORKDEPT) AS DNAME
FROM EMP;

-- EXISTS
SELECT * FROM DEPT D WHERE EXISTS (SELECT 1 FROM EMP E WHERE E.WORKDEPT = D.DEPTNO);

-- IN subquery
SELECT * FROM EMP WHERE WORKDEPT IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'NYC');

-- Quantified comparison
SELECT * FROM EMP WHERE SALARY > ALL (SELECT SALARY FROM EMP WHERE WORKDEPT = 'D01');

B.1.4 Common Table Expressions (CTEs)

WITH DEPT_AVG AS (
    SELECT WORKDEPT, AVG(SALARY) AS AVG_SAL
    FROM EMP
    GROUP BY WORKDEPT
)
SELECT E.EMPNO, E.SALARY, D.AVG_SAL
FROM EMP E
JOIN DEPT_AVG D ON E.WORKDEPT = D.WORKDEPT
WHERE E.SALARY > D.AVG_SAL;

Recursive CTE:

WITH ORG_TREE (EMPNO, MGRNO, LVL) AS (
    SELECT EMPNO, MGRNO, 0
    FROM EMP WHERE MGRNO IS NULL
    UNION ALL
    SELECT E.EMPNO, E.MGRNO, O.LVL + 1
    FROM EMP E JOIN ORG_TREE O ON E.MGRNO = O.EMPNO
)
SELECT * FROM ORG_TREE;

B.1.5 OLAP / Window Functions

SELECT EMPNO, WORKDEPT, SALARY,
    ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN,
    RANK() OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS DEPT_RANK,
    SUM(SALARY) OVER (PARTITION BY WORKDEPT) AS DEPT_TOTAL,
    AVG(SALARY) OVER (ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MOVING_AVG
FROM EMP;

Supported window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, SUM, AVG, MIN, MAX, COUNT (all as windowed aggregates).

B.2 Data Modification

B.2.1 INSERT

-- Single row
INSERT INTO EMP (EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, SALARY)
VALUES ('000999', 'JANE', 'DOE', 'D01', 65000.00);

-- Multi-row (LUW; z/OS v12+)
INSERT INTO EMP (EMPNO, FIRSTNAME, LASTNAME)
VALUES ('001001', 'A', 'ALPHA'),
       ('001002', 'B', 'BRAVO'),
       ('001003', 'C', 'CHARLIE');

-- INSERT from SELECT
INSERT INTO EMP_ARCHIVE
SELECT * FROM EMP WHERE HIREDATE < '2000-01-01';

-- INSERT with identity column
INSERT INTO ORDER_LOG (ORDER_DESC) VALUES ('New order');
-- IDENTITY_VAL_LOCAL() returns the generated value

B.2.2 UPDATE

-- Simple update
UPDATE EMP SET SALARY = SALARY * 1.05 WHERE WORKDEPT = 'D01';

-- Correlated update
UPDATE EMP E SET SALARY = (
    SELECT AVG(SALARY) FROM EMP WHERE WORKDEPT = E.WORKDEPT
)
WHERE EMPNO = '000100';

-- Update with row limit (LUW)
UPDATE EMP SET BONUS = 1000 WHERE BONUS = 0 FETCH FIRST 100 ROWS ONLY;

B.2.3 DELETE

-- Conditional delete
DELETE FROM EMP WHERE HIREDATE < '1990-01-01';

-- Delete all rows (use TRUNCATE for better performance if no rollback needed)
DELETE FROM TEMP_TABLE;

-- Correlated delete
DELETE FROM EMP WHERE NOT EXISTS (
    SELECT 1 FROM DEPT WHERE DEPT.DEPTNO = EMP.WORKDEPT
);

B.2.4 MERGE (Upsert)

MERGE INTO TARGET_TABLE T
USING SOURCE_TABLE S
ON T.KEY_COL = S.KEY_COL
WHEN MATCHED THEN
    UPDATE SET T.VAL = S.VAL, T.UPDATED = CURRENT TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (KEY_COL, VAL, UPDATED)
    VALUES (S.KEY_COL, S.VAL, CURRENT TIMESTAMP);

B.2.5 TRUNCATE (LUW; z/OS v12+)

TRUNCATE TABLE TEMP_TABLE IMMEDIATE;
-- Much faster than DELETE; does not fire triggers; not logged row-by-row
-- On z/OS: TRUNCATE TABLE TEMP_TABLE IMMEDIATE REUSE STORAGE;

B.3 Data Definition

B.3.1 CREATE TABLE

CREATE TABLE ORDERS (
    ORDER_ID     INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    CUSTOMER_ID  INTEGER NOT NULL,
    ORDER_DATE   DATE NOT NULL WITH DEFAULT CURRENT DATE,
    TOTAL_AMT    DECIMAL(11,2),
    STATUS       CHAR(1) NOT NULL DEFAULT 'N',
    DESCRIPTION  VARCHAR(500),
    CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
    CONSTRAINT FK_CUST FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID),
    CONSTRAINT CHK_STATUS CHECK (STATUS IN ('N', 'P', 'C', 'X'))
)
IN USERSPACE1;           -- LUW: table space
-- z/OS: IN DATABASE DSNDB04.ORDERTS

Temporal table (z/OS v10+, LUW v10.1+):

CREATE TABLE POLICY (
    POLICY_ID    INTEGER NOT NULL,
    HOLDER_NAME  VARCHAR(100),
    SYS_START    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
    SYS_END      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
    TX_ID        TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME (SYS_START, SYS_END),
    PRIMARY KEY (POLICY_ID)
);

CREATE TABLE POLICY_HIST LIKE POLICY;

ALTER TABLE POLICY ADD VERSIONING USE HISTORY TABLE POLICY_HIST;

B.3.2 ALTER TABLE

-- Add a column
ALTER TABLE ORDERS ADD COLUMN SHIP_DATE DATE;

-- Alter column data type (LUW -- limitations apply)
ALTER TABLE ORDERS ALTER COLUMN DESCRIPTION SET DATA TYPE VARCHAR(1000);

-- Add constraint
ALTER TABLE ORDERS ADD CONSTRAINT CHK_AMT CHECK (TOTAL_AMT >= 0);

-- Drop constraint
ALTER TABLE ORDERS DROP CONSTRAINT CHK_AMT;

-- z/OS: Add a partition
ALTER TABLE ORDERS ALTER PARTITION 5 ENDING AT ('2026-12-31') INCLUSIVE;

B.3.3 CREATE INDEX

-- Standard index
CREATE INDEX IX_ORDERS_CUST ON ORDERS (CUSTOMER_ID);

-- Unique index
CREATE UNIQUE INDEX IX_ORDERS_PK ON ORDERS (ORDER_ID);

-- Composite index with included columns (LUW)
CREATE INDEX IX_ORDERS_DATE ON ORDERS (ORDER_DATE DESC)
    INCLUDE (CUSTOMER_ID, TOTAL_AMT);

-- z/OS: Index with CLUSTER attribute
CREATE INDEX IX_ORDERS_CL ON ORDERS (ORDER_DATE)
    CLUSTER;

-- Expression-based index (LUW 11.1+)
CREATE INDEX IX_UPPER_NAME ON CUSTOMERS (UPPER(LAST_NAME));

B.4 Built-in Functions

B.4.1 String Functions

B.4.2 Numeric Functions

B.4.3 Date and Time Functions

B.4.4 Conversion and Casting Functions

B.5 DB2-Specific SQL Extensions

B.5.1 Isolation Levels in the Query

DB2 allows specifying isolation at the statement level---a feature uncommon in other RDBMS:

SELECT * FROM ORDERS WITH UR;    -- Uncommitted Read (dirty read)
SELECT * FROM ORDERS WITH CS;    -- Cursor Stability (default)
SELECT * FROM ORDERS WITH RS;    -- Read Stability
SELECT * FROM ORDERS WITH RR;    -- Repeatable Read (serializable)

B.5.2 OPTIMIZE FOR n ROWS

Hints the optimizer that only n rows will be fetched, favoring nested-loop index access over table scans:

SELECT * FROM ORDERS WHERE CUSTOMER_ID = 100
ORDER BY ORDER_DATE DESC
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 10 ROWS;

B.5.3 Special Registers

B.5.4 SELECT FROM INSERT / UPDATE / DELETE

DB2 supports selecting from modification statements:

SELECT ORDER_ID FROM FINAL TABLE (
    INSERT INTO ORDERS (CUSTOMER_ID, ORDER_DATE) VALUES (100, CURRENT DATE)
);

This returns the generated ORDER_ID without a separate query.

B.5.5 XMLTABLE and JSON Functions

-- XMLTABLE
SELECT T.* FROM XML_DOC D,
    XMLTABLE('$doc/orders/order' PASSING D.XML_COL AS "doc"
        COLUMNS
            ORDER_ID INTEGER PATH '@id',
            AMOUNT DECIMAL(11,2) PATH 'amount'
    ) AS T;

-- JSON_VALUE (Db2 LUW 11.1+)
SELECT JSON_VALUE(JSON_COL, '$.customer.name') FROM ORDERS;

B.6 Common SQLCODE and SQLSTATE Values

B.6.1 Successful Completion

B.6.2 Warning Codes (Positive)

B.6.3 Common Error Codes (Negative)

B.6.4 Utility and Resource Codes

B.6.5 Using SQLCODE in Application Programs

-- In a stored procedure
DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
    SET duplicate_found = 1;

-- Or using SQLCODE
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        err_sqlcode = DB2_RETURNED_SQLCODE,
        err_msg = MESSAGE_TEXT;
END;

B.6.6 SQLSTATE Class Structure

SQLSTATE values are portable across DB2 platforms and (mostly) across database vendors. SQLCODE values are DB2-specific. Prefer SQLSTATE in new application code for better portability.

B.7 Quick Syntax Reference Card

SELECT cols FROM tabs [WHERE cond] [GROUP BY cols] [HAVING cond] [ORDER BY cols]
INSERT INTO tab [(cols)] VALUES (vals) | SELECT ...
UPDATE tab SET col=val [WHERE cond]
DELETE FROM tab [WHERE cond]
MERGE INTO tab USING src ON cond WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...
CREATE TABLE tab (col type [constraints], ...) [IN tbsp]
ALTER TABLE tab ADD|ALTER|DROP COLUMN|CONSTRAINT ...
CREATE [UNIQUE] INDEX idx ON tab (cols) [INCLUDE (cols)]
DROP TABLE|INDEX|VIEW name
GRANT privs ON tab TO user|role
REVOKE privs ON tab FROM user|role
CALL procedure(args)

This reference covers roughly 80% of the SQL you will write against DB2 in a production environment. For the remaining 20%---global temporary tables, array types, inline SQL PL, autonomous transactions, and advanced XML/JSON processing---consult the IBM Knowledge Center or the relevant chapters in this book.

B.8 Aggregate Functions

LISTAGG example:

SELECT WORKDEPT,
       LISTAGG(LASTNAME, ', ') WITHIN GROUP (ORDER BY LASTNAME) AS EMP_LIST
FROM EMP
GROUP BY WORKDEPT;

Result: D01 -> ADAMS, BAKER, CHEN

B.9 Transaction Control

-- Commit current transaction
COMMIT;

-- Rollback current transaction
ROLLBACK;

-- Savepoint management
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;
-- ... perform operations ...
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- Implicit commit behavior: DDL statements (CREATE, ALTER, DROP) implicitly
-- commit on LUW. On z/OS, DDL does NOT implicitly commit.

B.10 GRANT and REVOKE

-- Table privileges
GRANT SELECT, INSERT, UPDATE ON TABLE MYSCHEMA.ORDERS TO USER appuser;
GRANT ALL PRIVILEGES ON TABLE MYSCHEMA.ORDERS TO ROLE app_admin;
GRANT SELECT ON TABLE MYSCHEMA.ORDERS TO PUBLIC;

-- Schema privileges
GRANT CREATEIN ON SCHEMA MYSCHEMA TO USER developer1;

-- Database authorities (LUW)
GRANT DBADM ON DATABASE TO USER db2admin;
GRANT CONNECT ON DATABASE TO USER appuser;

-- Revoke
REVOKE INSERT ON TABLE MYSCHEMA.ORDERS FROM USER appuser;
REVOKE DBADM ON DATABASE FROM USER oldadmin;

-- Role management (LUW 9.5+, z/OS v9+)
CREATE ROLE app_reader;
GRANT SELECT ON TABLE MYSCHEMA.ORDERS TO ROLE app_reader;
GRANT ROLE app_reader TO USER analyst1;

B.11 Global Temporary Tables

-- Declared global temporary table (session scope, not logged)
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_RESULTS (
    ID INTEGER,
    RESULT VARCHAR(200)
) ON COMMIT PRESERVE ROWS NOT LOGGED;

INSERT INTO SESSION.TEMP_RESULTS VALUES (1, 'Intermediate result');
SELECT * FROM SESSION.TEMP_RESULTS;
-- Automatically dropped at end of session

-- Created global temporary table (definition persists; data is per-session)
CREATE GLOBAL TEMPORARY TABLE MYSCHEMA.GTT_WORK (
    BATCH_ID INTEGER,
    WORK_ITEM VARCHAR(500)
) ON COMMIT PRESERVE ROWS;

B.12 Sequence Objects

-- Create a sequence
CREATE SEQUENCE ORDER_SEQ
    AS INTEGER
    START WITH 1000
    INCREMENT BY 1
    NO MAXVALUE
    NO CYCLE
    CACHE 50;

-- Use in INSERT
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID)
VALUES (NEXT VALUE FOR ORDER_SEQ, 100);

-- Get current value (must call NEXT VALUE first in the session)
SELECT PREVIOUS VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;

-- Alter a sequence
ALTER SEQUENCE ORDER_SEQ RESTART WITH 5000;

B.13 Views and Materialized Query Tables

-- Standard view
CREATE OR REPLACE VIEW ACTIVE_EMPLOYEES AS
SELECT EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, SALARY
FROM EMPLOYEE
WHERE STATUS = 'A';

-- With check option (prevents inserts/updates that would not be visible through the view)
CREATE VIEW HIGH_EARNERS AS
SELECT * FROM EMPLOYEE WHERE SALARY > 100000
WITH CHECK OPTION;

-- Materialized query table (MQT) -- LUW
CREATE TABLE DEPT_SUMMARY AS (
    SELECT WORKDEPT, COUNT(*) AS EMP_COUNT, AVG(SALARY) AS AVG_SAL
    FROM EMPLOYEE
    GROUP BY WORKDEPT
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

-- Refresh the MQT
REFRESH TABLE DEPT_SUMMARY;

-- The optimizer may automatically route qualifying queries to the MQT
-- (controlled by CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION)
SET CURRENT REFRESH AGE ANY;
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION ALL;

B.14 Data Type Quick Reference

Platform differences in character types: On z/OS, CHAR and VARCHAR are stored in EBCDIC by default (unless the table space uses Unicode). On LUW, the encoding depends on the database code set (typically UTF-8). When connecting cross-platform, DB2 handles conversion via DRDA, but be aware of potential length expansion with multi-byte characters.

B.15 Conditional Expressions

-- Simple CASE
SELECT EMPNO,
    CASE STATUS
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        WHEN 'T' THEN 'Terminated'
        ELSE 'Unknown'
    END AS STATUS_DESC
FROM EMPLOYEE;

-- Searched CASE
SELECT EMPNO,
    CASE
        WHEN SALARY > 100000 THEN 'Executive'
        WHEN SALARY > 60000  THEN 'Senior'
        WHEN SALARY > 35000  THEN 'Mid-Level'
        ELSE 'Entry'
    END AS PAY_BAND
FROM EMPLOYEE;

-- COALESCE (returns first non-null)
SELECT EMPNO, COALESCE(PHONE_WORK, PHONE_MOBILE, PHONE_HOME, 'No phone') AS CONTACT
FROM EMPLOYEE;

-- NULLIF (returns NULL if equal)
SELECT EMPNO, NULLIF(BONUS, 0) AS BONUS_OR_NULL
FROM EMPLOYEE;
-- Useful to avoid division by zero: SALARY / NULLIF(HOURS, 0)

B.16 SET Operators

-- UNION (removes duplicates)
SELECT EMPNO, LASTNAME FROM EMP_NYC
UNION
SELECT EMPNO, LASTNAME FROM EMP_LON;

-- UNION ALL (preserves duplicates, faster)
SELECT EMPNO, LASTNAME FROM EMP_NYC
UNION ALL
SELECT EMPNO, LASTNAME FROM EMP_LON;

-- INTERSECT (rows in both)
SELECT EMPNO FROM ACTIVE_EMPLOYEES
INTERSECT
SELECT EMPNO FROM BONUS_ELIGIBLE;

-- EXCEPT (rows in first but not second)
SELECT EMPNO FROM ALL_EMPLOYEES
EXCEPT
SELECT EMPNO FROM TERMINATED_EMPLOYEES;

-- Multiple set operations with precedence (use parentheses for clarity)
(SELECT EMPNO FROM DEPT_A UNION SELECT EMPNO FROM DEPT_B)
EXCEPT
SELECT EMPNO FROM ON_LEAVE;

B.17 Stored Procedure Quick Syntax

-- Create a stored procedure (SQL PL)
CREATE OR REPLACE PROCEDURE process_order(
    IN  p_customer_id  INTEGER,
    IN  p_amount       DECIMAL(11,2),
    OUT p_order_id     INTEGER
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
    DECLARE v_status CHAR(1) DEFAULT 'N';
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_order_id = -1;
    END;

    INSERT INTO ORDERS (CUSTOMER_ID, TOTAL_AMT, STATUS)
    VALUES (p_customer_id, p_amount, v_status);

    SET p_order_id = IDENTITY_VAL_LOCAL();

    IF p_amount > 10000 THEN
        INSERT INTO AUDIT_LOG (ORDER_ID, EVENT)
        VALUES (p_order_id, 'HIGH_VALUE_ORDER');
    END IF;

    COMMIT;
END;

-- Call the procedure
CALL process_order(100, 15000.00, ?);

B.18 Trigger Quick Syntax

-- BEFORE trigger (validation)
CREATE OR REPLACE TRIGGER check_salary
BEFORE UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN (N.SALARY > O.SALARY * 1.50)
SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'Salary increase exceeds 50% limit';

-- AFTER trigger (audit logging)
CREATE OR REPLACE TRIGGER audit_salary_change
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
INSERT INTO SALARY_AUDIT (EMPNO, OLD_SALARY, NEW_SALARY, CHANGE_DATE, CHANGED_BY)
VALUES (N.EMPNO, O.SALARY, N.SALARY, CURRENT TIMESTAMP, SESSION_USER);