-- Connect to a database
CONNECT TO dbname USER username USING password;

-- Connect with current OS credentials
CONNECT TO dbname;

-- Disconnect
CONNECT RESET;
DISCONNECT ALL;

-- Check current connection
SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1;

-- List databases
db2 LIST DATABASE DIRECTORY

-- List active connections
db2 LIST APPLICATIONS

-DB2SSID START DB2
-DB2SSID STOP DB2
-DB2SSID DISPLAY DATABASE(*) SPACE
-DB2SSID DISPLAY THREAD(*)
-DB2SSID DISPLAY DDF
-DB2SSID CANCEL THREAD(token) FORCE

CREATE TABLE EMPLOYEE (
    EMPNO        CHAR(6)       NOT NULL,
    FIRSTNME     VARCHAR(12)   NOT NULL,
    MIDINIT      CHAR(1)       NOT NULL,
    LASTNAME     VARCHAR(15)   NOT NULL,
    WORKDEPT     CHAR(3),
    PHONENO      CHAR(4),
    HIREDATE     DATE,
    JOB          CHAR(8),
    EDLEVEL      SMALLINT      NOT NULL,
    SEX          CHAR(1),
    BIRTHDATE    DATE,
    SALARY       DECIMAL(9,2),
    BONUS        DECIMAL(9,2),
    COMM         DECIMAL(9,2),
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO)
) IN DATABASE DSNDBC001;

-- Add a column
ALTER TABLE EMPLOYEE ADD COLUMN EMAIL VARCHAR(100);

-- Add a constraint
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_DEPT
    FOREIGN KEY (WORKDEPT) REFERENCES DEPARTMENT (DEPTNO)
    ON DELETE SET NULL;

-- Drop a column (DB2 10+)
ALTER TABLE EMPLOYEE DROP COLUMN EMAIL;

-- Rename a table (DB2 LUW)
RENAME TABLE old_name TO new_name;

-- Drop table (irreversible)
DROP TABLE EMPLOYEE;

-- Truncate — delete all rows, keep structure (faster than DELETE)
TRUNCATE TABLE EMPLOYEE IMMEDIATE;

-- Drop index
DROP INDEX EMPLOYEE_IDX;

-- Simple index
CREATE INDEX EMPNO_IDX ON EMPLOYEE (EMPNO);

-- Composite index
CREATE INDEX EMP_NAME_IDX ON EMPLOYEE (LASTNAME, FIRSTNME);

-- Unique index
CREATE UNIQUE INDEX EMP_PHONE_IDX ON EMPLOYEE (PHONENO);

-- Clustering index (z/OS — determines physical row order)
CREATE INDEX EMP_DEPT_IDX ON EMPLOYEE (WORKDEPT) CLUSTER;

-- Descending
CREATE INDEX SALARY_DESC ON EMPLOYEE (SALARY DESC);

-- Basic select
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM EMPLOYEE
WHERE WORKDEPT = 'A00'
ORDER BY SALARY DESC;

-- With FETCH FIRST (limit rows)
SELECT * FROM EMPLOYEE FETCH FIRST 10 ROWS ONLY;

-- Aggregate functions
SELECT WORKDEPT,
       COUNT(*) AS HEADCOUNT,
       AVG(SALARY) AS AVG_SALARY,
       MAX(SALARY) AS MAX_SALARY,
       MIN(SALARY) AS MIN_SALARY,
       SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEE
GROUP BY WORKDEPT
HAVING COUNT(*) > 2
ORDER BY AVG_SALARY DESC;

-- CASE expression
SELECT EMPNO,
       LASTNAME,
       SALARY,
       CASE
           WHEN SALARY > 80000 THEN 'Senior'
           WHEN SALARY > 50000 THEN 'Mid'
           ELSE 'Junior'
       END AS GRADE
FROM EMPLOYEE;

-- INNER JOIN
SELECT E.EMPNO, E.LASTNAME, D.DEPTNAME
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO;

-- LEFT OUTER JOIN (all employees, including those without a dept)
SELECT E.EMPNO, E.LASTNAME, D.DEPTNAME
FROM EMPLOYEE E
LEFT OUTER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO;

-- FULL OUTER JOIN
SELECT E.EMPNO, E.LASTNAME, D.DEPTNAME
FROM EMPLOYEE E
FULL OUTER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO;

-- Self join
SELECT E.LASTNAME AS EMPLOYEE, M.LASTNAME AS MANAGER
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M ON E.MGRNO = M.EMPNO;

-- Correlated subquery
SELECT EMPNO, LASTNAME, SALARY
FROM EMPLOYEE E
WHERE SALARY > (
    SELECT AVG(SALARY) FROM EMPLOYEE
    WHERE WORKDEPT = E.WORKDEPT
);

-- EXISTS
SELECT DEPTNO, DEPTNAME FROM DEPARTMENT D
WHERE EXISTS (
    SELECT 1 FROM EMPLOYEE E WHERE E.WORKDEPT = D.DEPTNO
);

-- IN with subquery
SELECT * FROM EMPLOYEE
WHERE WORKDEPT IN (
    SELECT DEPTNO FROM DEPARTMENT WHERE LOCATION = 'BOSTON'
);

WITH HIGH_EARNERS AS (
    SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
    FROM EMPLOYEE
    WHERE SALARY > 70000
),
DEPT_STATS AS (
    SELECT WORKDEPT, COUNT(*) AS CNT, AVG(SALARY) AS AVG_SAL
    FROM HIGH_EARNERS
    GROUP BY WORKDEPT
)
SELECT H.LASTNAME, H.SALARY, D.AVG_SAL, D.CNT
FROM HIGH_EARNERS H
JOIN DEPT_STATS D ON H.WORKDEPT = D.WORKDEPT
ORDER BY H.SALARY DESC;

-- ROW_NUMBER
SELECT EMPNO, LASTNAME, SALARY,
       ROW_NUMBER() OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS RANK_IN_DEPT
FROM EMPLOYEE;

-- RANK and DENSE_RANK
SELECT EMPNO, LASTNAME, SALARY,
       RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK,
       DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_DENSE_RANK
FROM EMPLOYEE;

-- LAG and LEAD
SELECT EMPNO, HIREDATE, SALARY,
       LAG(SALARY, 1, 0) OVER (ORDER BY HIREDATE) AS PREV_SALARY,
       SALARY - LAG(SALARY, 1, 0) OVER (ORDER BY HIREDATE) AS SALARY_CHANGE
FROM EMPLOYEE;

-- Running total
SELECT EMPNO, HIREDATE, SALARY,
       SUM(SALARY) OVER (ORDER BY HIREDATE ROWS UNBOUNDED PRECEDING) AS RUNNING_TOTAL
FROM EMPLOYEE;

-- Single row
INSERT INTO EMPLOYEE (EMPNO, FIRSTNME, LASTNAME, EDLEVEL, WORKDEPT, SALARY)
VALUES ('200340', 'ROB', 'JONES', 18, 'A00', 72500.00);

-- Multi-row
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
VALUES
    ('X01', 'INNOVATION', '000010', 'A00'),
    ('X02', 'RESEARCH', '000020', 'A00');

-- Insert from SELECT
INSERT INTO EMPLOYEE_ARCHIVE
SELECT * FROM EMPLOYEE WHERE HIREDATE < '2010-01-01';

-- Update single column
UPDATE EMPLOYEE SET SALARY = SALARY * 1.05 WHERE WORKDEPT = 'A00';

-- Update multiple columns
UPDATE EMPLOYEE
SET SALARY = 85000.00,
    BONUS = 5000.00,
    JOB = 'MANAGER'
WHERE EMPNO = '000010';

-- Update with subquery
UPDATE EMPLOYEE E
SET SALARY = (
    SELECT AVG(SALARY) * 1.1 FROM EMPLOYEE
    WHERE WORKDEPT = E.WORKDEPT
)
WHERE JOB = 'ANALYST';

-- Delete with condition
DELETE FROM EMPLOYEE WHERE HIREDATE < '1985-01-01';

-- Delete all rows (use TRUNCATE for performance on large tables)
DELETE FROM TEMP_TABLE;

-- Populate PLAN_TABLE with access path for a query
EXPLAIN PLAN FOR
SELECT * FROM EMPLOYEE WHERE WORKDEPT = 'A00';

-- View the EXPLAIN output
SELECT * FROM PLAN_TABLE ORDER BY QUERYNO, PLANNO;

//RUNSTATS EXEC PGM=DSNUTILB,PARM='DB2SSID,RUNSTATS'
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  RUNSTATS TABLESPACE DSNDBC001.EMPTS01
    TABLE(SYSIBM.EMPLOYEE)
    INDEX(ALL)
    SHRLEVEL CHANGE
/*

//REORG    EXEC PGM=DSNUTILB,PARM='DB2SSID,REORG'
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  REORG TABLESPACE DSNDBC001.EMPTS01
    SHRLEVEL CHANGE
    DEADLINE NONE
/*

-- Lock a table for exclusive access
LOCK TABLE EMPLOYEE IN EXCLUSIVE MODE;

-- Lock in share mode (allow reads, block writes)
LOCK TABLE EMPLOYEE IN SHARE MODE;

-- Avoid locking with UR (Uncommitted Read)
SELECT * FROM EMPLOYEE WITH UR;

-- Repeatable Read
SELECT * FROM EMPLOYEE WITH RR;

-- Cursor Stability (default)
SELECT * FROM EMPLOYEE WITH CS;

       EXEC SQL
           DECLARE EMP_CURSOR CURSOR FOR
           SELECT EMPNO, LASTNAME, SALARY
           FROM EMPLOYEE
           WHERE WORKDEPT = :WS-DEPT
           ORDER BY SALARY DESC
       END-EXEC.

       EXEC SQL OPEN EMP_CURSOR END-EXEC.

       PERFORM UNTIL SQLCODE NOT = 0
           EXEC SQL
               FETCH EMP_CURSOR INTO
               :WS-EMPNO, :WS-LASTNAME, :WS-SALARY
           END-EXEC
           IF SQLCODE = 0
               DISPLAY 'Employee: ' WS-EMPNO ' ' WS-LASTNAME
           END-IF
       END-PERFORM.

       EXEC SQL CLOSE EMP_CURSOR END-EXEC.

# Start/stop instance
db2start
db2stop force

# Create database
db2 CREATE DATABASE mydb USING CODESET UTF-8 TERRITORY US

# Drop database  
db2 DROP DATABASE mydb

# Back up database (offline)
db2 BACKUP DATABASE mydb TO /backup/

# Restore database
db2 RESTORE DATABASE mydb FROM /backup/

# Connect and run SQL from file
db2 CONNECT TO mydb
db2 -tf query.sql

# Monitor active SQL
db2 GET SNAPSHOT FOR DYNAMIC SQL ON mydb