Appendix D: Command and Utility Reference

This appendix serves as a quick-reference for the commands and utilities you will use most frequently when administering DB2 on z/OS and LUW. It is organized by platform, then by category.

Part I: DB2 for z/OS Commands and Utilities

D.1 DB2 System Commands

DB2 commands on z/OS are issued from the operator console, DSN session, or through IFI (Instrumentation Facility Interface). The command prefix (e.g., -DB2P) identifies the DB2 subsystem.

D.1.1 -START DATABASE

Starts a database, table space, or index space, making it available for access.

-START DATABASE(DBNAME) SPACENAM(TSNAME) ACCESS(RW)

Options: - ACCESS(RW) --- Read/write access (default). - ACCESS(RO) --- Read-only access. - ACCESS(UT) --- Utility-only access. - ACCESS(FORCE) --- Force start, clearing restrictive status.

Examples:

-START DATABASE(ORDERSDB)                          -- Start all spaces in database
-START DATABASE(ORDERSDB) SPACENAM(ORDERTS)        -- Start one table space
-START DATABASE(ORDERSDB) SPACENAM(IX_ORD*) ACCESS(RO)  -- Start indexes, read-only

D.1.2 -STOP DATABASE

Stops a database, table space, or index space.

-STOP DATABASE(DBNAME) SPACENAM(TSNAME) AT(COMMIT)

Options: - AT(COMMIT) --- Wait for current units of work to commit before stopping. - AT(FORCE) --- Immediately stop; active threads are rolled back.

D.1.3 -DISPLAY DATABASE

Shows the status of databases, table spaces, and index spaces.

-DISPLAY DATABASE(DBNAME) SPACENAM(*) RESTRICT LIMIT(*)

Key status codes in output: - RW --- Read/write, normal. - RO --- Read-only. - STOP --- Stopped. - COPY --- Copy pending (backup required before further updates). - RECP --- Recover pending. - CHKP --- Check pending (referential integrity check needed). - GRECP --- Group buffer pool recover pending (data sharing). - RBDP --- Rebuild pending (for indexes).

D.1.4 -DISPLAY THREAD

Shows active threads (connections).

-DISPLAY THREAD(*) TYPE(*)

Filter options: - TYPE(ACTIVE) --- Only active threads. - TYPE(INDOUBT) --- Only indoubt threads (two-phase commit). - TYPE(SYSTEM) --- System threads. - DETAIL --- Show additional information including plan name, connection ID.

D.1.5 -DISPLAY BUFFERPOOL

Shows buffer pool statistics.

-DISPLAY BUFFERPOOL(BP0) DETAIL

Key output fields: GETPAGE, SYNCHRONOUS READ, PREFETCH READ, PAGES IN USE, sequential and random GETPAGE counts.

D.1.6 -ALTER BUFFERPOOL

Dynamically changes buffer pool size.

-ALTER BUFFERPOOL(BP0) VPSIZE(50000)

Changes take effect immediately. No restart required.

D.1.7 -RECOVER

Initiates recovery of a table space or index.

-RECOVER TABLESPACE DBNAME.TSNAME TORBA X'0000000000000000' LOGONLY

Options: - TOCOPY --- Recover to a specific image copy. - TORBA --- Recover to a specific relative byte address in the log. - TOLOGPOINT --- Recover to a specific log point. - LOGONLY --- Apply only log records (no image copy restore).

D.1.8 -TERM UTILITY

Terminates a running utility.

-TERM UTILITY(UTILID)

Use this when a utility is stuck or must be stopped. The utility's work is rolled back.

D.1.9 -CANCEL THREAD

Cancels an active thread.

-CANCEL THREAD(token)

Obtain the token from -DISPLAY THREAD.

D.1.10 -SET LOG

Controls logging behavior.

-SET LOG LOGLOAD(0)    -- Force a checkpoint immediately

D.2 z/OS Utility Reference

Utilities run as batch jobs via JCL. Each utility has a UTILITY statement in SYSIN.

D.2.1 REORG TABLESPACE

Reorganizes a table space to reclaim fragmented space and restore clustering order.

REORG TABLESPACE DBNAME.TSNAME
    LOG YES
    SHRLEVEL CHANGE
    MAPPINGTABLE ADMIN.REORG_MAP
    SORTDEVT SYSDA
    SORTNUM 8

Key options: - SHRLEVEL NONE --- Exclusive access (fastest but table unavailable). - SHRLEVEL CHANGE --- Online reorg (requires mapping table). - SHRLEVEL REFERENCE --- Read-only access during reorg. - LOG YES|NO --- Whether to log changes (YES for recoverability). - PART n --- Reorganize a single partition.

D.2.2 RUNSTATS

Collects statistics for the optimizer.

RUNSTATS TABLESPACE DBNAME.TSNAME
    TABLE(ALL) SAMPLE 25
    INDEX(ALL)
    COLGROUP(COL1, COL2, COL3)
    HISTOGRAM(COL1) NUMCOLS 20 NUMQUANTILES 100
    SHRLEVEL CHANGE
    REPORT YES

Key options: - TABLE(ALL) --- Collect table statistics for all tables in the table space. - INDEX(ALL) --- Collect index statistics. - SAMPLE n --- Sample n percent of rows (faster for large tables). - COLGROUP --- Multi-column statistics for correlated columns. - HISTOGRAM --- Distribution histograms for better cardinality estimation. - UPDATE ALL / UPDATE SPACE / UPDATE ACCESSPATH --- Control what gets updated.

D.2.3 COPY

Creates an image copy (backup) of a table space or index.

COPY TABLESPACE DBNAME.TSNAME
    COPYDDN(COPY1)
    FULL YES
    SHRLEVEL CHANGE

Options: - FULL YES --- Full image copy. - FULL NO --- Incremental copy (pages changed since last full copy). - SHRLEVEL REFERENCE --- Read-only during copy (consistent copy). - SHRLEVEL CHANGE --- Online copy (fuzzy copy; requires log apply during recovery).

D.2.4 RECOVER

Recovers a table space from image copies and log records. (This is the utility, distinct from the -RECOVER command.)

RECOVER TABLESPACE DBNAME.TSNAME
    TOLOGPOINT X'...'
    TORBA X'...'
    TOCOPY DSNAME('HLQ.COPY.DATASET')

D.2.5 LOAD

Loads data from a sequential file into a table.

LOAD DATA INDDN(SYSREC)
    INTO TABLE SCHEMA.TABLENAME
    (COL1 POSITION(1:10) CHAR(10),
     COL2 POSITION(12:20) DECIMAL,
     COL3 POSITION(22:31) DATE EXTERNAL)
    REPLACE
    LOG YES
    ENFORCE CONSTRAINTS
    STATISTICS YES

Options: - REPLACE --- Replace existing data. - RESUME YES --- Append to existing data. - LOG YES|NO --- Logging (NO is faster but requires a COPY afterward). - ENFORCE CONSTRAINTS --- Check referential integrity during load. - STATISTICS YES --- Collect inline statistics during load.

D.2.6 UNLOAD

Unloads data from a table to a sequential data set.

UNLOAD TABLESPACE DBNAME.TSNAME
    FROM TABLE SCHEMA.TABLENAME
    HEADER NONE
    PUNCHDDN(SYSPUNCH)

Produces both data records and a SYSPUNCH data set containing the LOAD control statements needed to reload.

Part II: Db2 LUW Commands

D.3 Database Operations

D.3.1 CREATE DATABASE

db2 "CREATE DATABASE PRODDB
    AUTOMATIC STORAGE YES
    ON /data/db2
    USING CODESET UTF-8
    TERRITORY US
    COLLATE USING IDENTITY
    PAGESIZE 32768"

Key options: - AUTOMATIC STORAGE YES --- DB2 manages storage paths automatically. - PAGESIZE --- Default page size for new table spaces (4096, 8192, 16384, or 32768). - RESTRICTIVE --- No public access by default.

D.3.2 CONNECT / DISCONNECT

db2 "CONNECT TO PRODDB USER db2admin USING password"
db2 "CONNECT RESET"       # Disconnect
db2 "TERMINATE"            # Disconnect and free CLP resources

D.3.3 BACKUP DATABASE

# Offline backup
db2 "BACKUP DATABASE PRODDB TO /backup/db2"

# Online backup (requires archival logging)
db2 "BACKUP DATABASE PRODDB ONLINE TO /backup/db2 COMPRESS"

# Incremental backup
db2 "BACKUP DATABASE PRODDB ONLINE INCREMENTAL TO /backup/db2"

# Table space level backup
db2 "BACKUP DATABASE PRODDB TABLESPACE(USERSPACE1) ONLINE TO /backup/db2"

D.3.4 RESTORE DATABASE

# Full restore
db2 "RESTORE DATABASE PRODDB FROM /backup/db2 TAKEN AT 20260315120000"

# Restore to a different name
db2 "RESTORE DATABASE PRODDB FROM /backup/db2 INTO TESTDB"

# Restore specific table spaces
db2 "RESTORE DATABASE PRODDB TABLESPACE(USERSPACE1) ONLINE FROM /backup/db2"

D.3.5 ROLLFORWARD DATABASE

After a restore from an online backup, rollforward applies archived logs to bring the database to a consistent point.

# Roll forward to end of logs
db2 "ROLLFORWARD DATABASE PRODDB TO END OF LOGS AND COMPLETE"

# Roll forward to a specific point in time
db2 "ROLLFORWARD DATABASE PRODDB TO 2026-03-15-12.00.00 USING LOCAL TIME AND STOP"

# Check rollforward status
db2 "ROLLFORWARD DATABASE PRODDB QUERY STATUS"

D.3.6 RUNSTATS (LUW)

db2 "RUNSTATS ON TABLE SCHEMA.TABLENAME
    WITH DISTRIBUTION AND DETAILED INDEXES ALL
    ALLOW WRITE ACCESS"

# With column groups for multi-column statistics
db2 "RUNSTATS ON TABLE SCHEMA.TABLENAME
    ON COLUMNS ((COL1, COL2) LIKE STATISTICS)
    AND INDEXES ALL
    ALLOW WRITE ACCESS"

D.3.7 REORG TABLE (LUW)

# Offline reorg
db2 "REORG TABLE SCHEMA.TABLENAME"

# Online (inplace) reorg
db2 "REORG TABLE SCHEMA.TABLENAME INPLACE ALLOW WRITE ACCESS"

# Index reorg
db2 "REORG INDEXES ALL FOR TABLE SCHEMA.TABLENAME ALLOW WRITE ACCESS"

D.4 Admin Commands and Tools

D.4.1 db2pd --- Problem Determination

db2pd provides a non-intrusive snapshot of DB2 internals. It reads shared memory directly without acquiring latches, making it safe to run even on a busy production system.

# Buffer pool summary
db2pd -db PRODDB -bufferpools

# Lock information
db2pd -db PRODDB -locks showlocks

# Transaction information
db2pd -db PRODDB -transactions

# Dynamic SQL cache (top statements by execution time)
db2pd -db PRODDB -dynamic

# Table space information
db2pd -db PRODDB -tablespaces

# Log information
db2pd -db PRODDB -logs

# Wait conditions (for diagnosing hangs)
db2pd -db PRODDB -wlocks

# EDU (Engine Dispatchable Unit) threads
db2pd -db PRODDB -edus

# Memory usage
db2pd -dbpartitionnum 0 -memsets

D.4.2 db2top --- Real-Time Monitoring

db2top is an interactive, ncurses-based monitoring tool (Linux/Unix only).

db2top -d PRODDB

Screens (toggle with letter keys): - d --- Dynamic SQL (top SQL by various metrics) - l --- Locks - t --- Table spaces - b --- Buffer pools - s --- Sessions (applications) - D --- Database overview

Press h for help, q to quit.

D.4.3 db2look --- DDL Extraction

Extracts DDL (CREATE statements) and statistics from a database.

# Extract all DDL for a schema
db2look -d PRODDB -z MYSCHEMA -e -o schema_ddl.sql

# Extract DDL plus statistics (for optimizer reproducibility)
db2look -d PRODDB -z MYSCHEMA -e -m -l -o full_extract.sql

# Extract only statistics
db2look -d PRODDB -z MYSCHEMA -m -o stats_only.sql

Flags: - -e --- Generate DDL (CREATE TABLE, INDEX, VIEW, etc.) - -m --- Generate UPDATE STATISTICS statements (mimics RUNSTATS output) - -l --- Include table space and buffer pool DDL - -x --- Include authorization (GRANT) statements - -z schema --- Filter by schema

D.4.4 db2move --- Data Movement

Exports or imports all tables in a database.

# Export all tables
db2move PRODDB EXPORT -sn MYSCHEMA

# Import into target database
db2move TARGETDB IMPORT

# Load (faster than import, but more restrictions)
db2move TARGETDB LOAD

D.4.5 db2audit --- Audit Configuration

# Configure audit
db2audit configure scope all status both

# Start auditing
db2audit start

# Flush and extract audit records
db2audit flush
db2audit extract file audit_output.txt from path /home/db2inst1/sqllib/security/

# Stop auditing
db2audit stop

D.4.6 db2diag --- Diagnostic Log

The db2diag.log is DB2's primary diagnostic log. View it with:

# Filter by severity
db2diag -level ERROR -lastrecords 50

# Filter by time range
db2diag -time "2026-03-15-12.00.00:2026-03-15-13.00.00"

# Location
db2 "GET DBM CFG" | grep DIAGPATH

D.5 LUW Configuration Parameters Quick Reference

D.5.1 Database Manager (Instance) Parameters --- GET/UPDATE DBM CFG

D.5.2 Database Parameters --- GET/UPDATE DB CFG

D.5.3 Checking and Modifying Configuration

# View all database configuration
db2 "GET DB CFG FOR PRODDB SHOW DETAIL"

# The SHOW DETAIL flag shows current value, delayed value, and flags (automatic, etc.)

# Update a parameter (takes effect immediately unless noted)
db2 "UPDATE DB CFG FOR PRODDB USING LOCKTIMEOUT 30"

# Parameters requiring instance restart
db2 "UPDATE DBM CFG USING INTRA_PARALLEL YES"
db2stop force
db2start

# Parameters requiring database deactivation
db2 "UPDATE DB CFG FOR PRODDB USING LOGFILSIZ 65536"
db2 "DEACTIVATE DATABASE PRODDB"
db2 "ACTIVATE DATABASE PRODDB"

D.6 Cross-Platform Quick Reference

This table provides a starting point for z/OS practitioners learning LUW, or vice versa. The concepts are the same; the commands differ.

D.7 z/OS Additional Commands

D.7.1 -DISPLAY UTILITY

Shows the status of running utilities.

-DISPLAY UTILITY(*)
-DISPLAY UTILITY(UTILID) DETAIL

Useful for monitoring long-running REORG, LOAD, or COPY jobs. The DETAIL option shows the current phase and progress.

D.7.2 -DISPLAY ARCHIVE

Shows the status of archive log processing.

-DISPLAY ARCHIVE

Output includes current active log, archive status, and offload processing. Essential for diagnosing archive log delays.

D.7.3 -DISPLAY GROUP

In a data sharing environment, shows the status of all members in the group.

-DISPLAY GROUP DETAIL

Shows member names, status (ACTIVE, QUIESCED, FAILED), and group-level information.

D.7.4 -DISPLAY STATS

Shows performance statistics for the subsystem.

-DISPLAY STATS(*)

Includes counts for SQL statements executed, commits, rollbacks, deadlocks, and timeouts since the last statistics reset.

D.7.5 -DISPLAY DDF

Shows the status of the Distributed Data Facility (DDF), which handles remote connections.

-DISPLAY DDF DETAIL

Output includes: DDF status (STARTED/STOPPED), location name, DRDA port, number of active distributed threads, and connection details.

D.8 z/OS Additional Utilities

D.8.1 CHECK DATA

Checks referential integrity and check constraints for a table space.

CHECK DATA TABLESPACE DBNAME.TSNAME
    FOR EXCEPTION IN SCHEMA.EXCEPT_TABLE
    DELETE YES

Options: - FOR EXCEPTION IN --- Table to hold rows that violate constraints. - DELETE YES --- Automatically delete violating rows. - DELETE NO --- Report violations without correcting them (default). - SCOPE PENDING --- Check only table spaces in CHECK PENDING status.

D.8.2 CHECK INDEX

Verifies that index entries are consistent with the data they point to.

CHECK INDEX(SCHEMA.INDEX_NAME) SHRLEVEL REFERENCE

Run this after recovery or when you suspect index corruption. If errors are found, rebuild the index with REBUILD INDEX.

D.8.3 REBUILD INDEX

Rebuilds one or more indexes from the table data.

REBUILD INDEX(ALL) TABLESPACE DBNAME.TSNAME
    SHRLEVEL CHANGE
    SORTDEVT SYSDA
    SORTNUM 4

Faster than dropping and recreating indexes. The SHRLEVEL CHANGE option allows concurrent read/write access during the rebuild.

D.8.4 MODIFY STATISTICS

Manually modifies catalog statistics without running RUNSTATS. Used for optimizer testing and troubleshooting.

MODIFY STATISTICS TABLESPACE DBNAME.TSNAME
    TABLE SCHEMA.TABLENAME
    COLCARDF(COLUMN_NAME, 50000)

Use with caution: incorrect manual statistics can cause poor access plans. Always document manual statistics changes and revert them after testing.

D.8.5 DIAGNOSE

A utility for diagnostic data collection, typically used at IBM support's direction.

DIAGNOSE TYPE(DISPLAY SYSIBM.SYSTABLES) SHRLEVEL CHANGE

Can also display internal page formats and lock chains.

D.9 LUW Additional Commands

D.9.1 db2batch --- Benchmark and Timing

# Time a query from a file
db2batch -d PRODDB -f query.sql -r results.txt -o p 3

# Options:
# -d database     Database name
# -f file         SQL file
# -r file         Results output file
# -o p n          Performance detail level (0-5, higher = more detail)
# -o r n          Rows fetched per fetch call

db2batch provides precise elapsed time, CPU time, and row counts---more reliable than timing with shell commands.

D.9.2 db2expln --- Explain from Command Line

# Explain a dynamic SQL statement
db2expln -d PRODDB -q "SELECT * FROM ORDERS WHERE CUSTOMER_ID = 100" -g -t

# Explain a static package
db2expln -d PRODDB -c SCHEMA -p PACKAGE_NAME -s 0 -g -t

# Options:
# -g     Show graph (tree format)
# -t     Include total cost estimates

More detailed than DBeaver's explain, and scriptable for automated plan analysis.

D.9.3 db2dart --- Database Analysis and Reporting Tool

# Inspect a specific table space
db2dart PRODDB /TSID 2 /RPT

# Inspect the entire database
db2dart PRODDB /DB

db2dart inspects internal database structures for corruption. It requires exclusive access (database not active). Use it when you suspect data corruption or at IBM support's direction.

D.9.4 db2ckbkp --- Check Backup Integrity

# Verify a backup image
db2ckbkp -d PRODDB.0.db2inst1.DBPART000.20260315120000.001

# Options:
# -d     Display detailed backup header information
# -h     Display only backup header
# -s     Verify stogroup information

Always validate backup images, especially before relying on them for disaster recovery.

D.9.5 db2level --- Version Information

db2level

Displays the installed DB2 version, fix pack level, and build information. Essential for support cases and compatibility checking.

D.9.6 db2val --- Validate Installation

db2val -a

Validates the DB2 installation, instance configuration, and database connectivity. Run after installation or fix pack application.

D.9.7 db2support --- Collect Diagnostic Data

db2support /tmp/db2support_output -d PRODDB -cl 0

Collects system information, DB2 configuration, diagnostic logs, and optionally database-specific data into a compressed archive for IBM support.

D.10 Monitoring Table Functions (LUW)

DB2 LUW provides MON_GET_* table functions for programmatic monitoring. These are more flexible than snapshot monitoring and are the preferred approach for Db2 11.1+.

-- Top 10 SQL statements by execution time
SELECT SUBSTR(STMT_TEXT, 1, 100) AS SQL_TEXT,
       NUM_EXECUTIONS,
       TOTAL_ACT_TIME / 1000 AS TOTAL_MS,
       ROWS_READ,
       ROWS_RETURNED
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) AS T
WHERE NUM_EXECUTIONS > 0
ORDER BY TOTAL_ACT_TIME DESC
FETCH FIRST 10 ROWS ONLY;

-- Buffer pool hit ratios by buffer pool
SELECT BP_NAME,
       POOL_DATA_L_READS AS DATA_LOGICAL,
       POOL_DATA_P_READS AS DATA_PHYSICAL,
       POOL_INDEX_L_READS AS INDEX_LOGICAL,
       POOL_INDEX_P_READS AS INDEX_PHYSICAL,
       CASE WHEN POOL_DATA_L_READS > 0
            THEN DEC((1.0 - FLOAT(POOL_DATA_P_READS)/POOL_DATA_L_READS) * 100, 5, 2)
            ELSE 0 END AS DATA_HR,
       CASE WHEN POOL_INDEX_L_READS > 0
            THEN DEC((1.0 - FLOAT(POOL_INDEX_P_READS)/POOL_INDEX_L_READS) * 100, 5, 2)
            ELSE 0 END AS INDEX_HR
FROM TABLE(MON_GET_BUFFERPOOL(NULL, -2)) AS T;

-- Table space usage
SELECT TBSP_NAME, TBSP_TYPE, TBSP_STATE,
       TBSP_USED_PAGES, TBSP_TOTAL_PAGES,
       DEC(FLOAT(TBSP_USED_PAGES) / TBSP_TOTAL_PAGES * 100, 5, 2) AS PCT_USED
FROM TABLE(MON_GET_TABLESPACE(NULL, -2)) AS T
ORDER BY PCT_USED DESC;

-- Lock waits
SELECT APPLICATION_HANDLE, LOCK_WAIT_START_TIME,
       LOCK_NAME, LOCK_MODE, LOCK_OBJECT_TYPE
FROM TABLE(MON_GET_APPL_LOCKWAIT(NULL, -2)) AS T;

-- Connection summary
SELECT APPLICATION_NAME, APPLICATION_HANDLE,
       CLIENT_HOSTNAME, CONNECTION_START_TIME,
       UOW_START_TIME, ELAPSED_TIME_SEC
FROM TABLE(MON_GET_CONNECTION(NULL, -2)) AS T
ORDER BY CONNECTION_START_TIME;

These queries form the building blocks of a lightweight monitoring dashboard. Combine them with scheduled collection (via a stored procedure and ADMIN_TASK_ADD) for historical trending.

D.11 Data Movement Commands (LUW)

D.11.1 EXPORT

Exports query results to a file.

# Export to delimited (CSV) format
db2 "EXPORT TO /data/emp.csv OF DEL
    MODIFIED BY NOCHARDEL COLDEL,
    SELECT EMPNO, LASTNAME, SALARY FROM EMPLOYEE"

# Export to IXF format (preserves data types for reimport)
db2 "EXPORT TO /data/emp.ixf OF IXF
    SELECT * FROM EMPLOYEE"

# Export with LOBs
db2 "EXPORT TO /data/docs.del OF DEL
    LOBS TO /data/lobs/
    LOBFILE lobfile
    MODIFIED BY LOBSINFILE
    SELECT * FROM DOCUMENT_TABLE"

D.11.2 IMPORT

Imports data using SQL INSERT operations (triggers fire, constraints checked live).

# Import from CSV
db2 "IMPORT FROM /data/emp.csv OF DEL
    MODIFIED BY COLDEL,
    INSERT INTO EMPLOYEE_COPY"

# Import with commit interval
db2 "IMPORT FROM /data/emp.csv OF DEL
    COMMITCOUNT 5000
    INSERT INTO EMPLOYEE_COPY"

# Import from IXF with CREATE (creates target table automatically)
db2 "IMPORT FROM /data/emp.ixf OF IXF
    CREATE INTO EMPLOYEE_COPY"

# Import modes: INSERT, INSERT_UPDATE, REPLACE, REPLACE_CREATE
db2 "IMPORT FROM /data/emp.csv OF DEL REPLACE INTO EMPLOYEE_COPY"

D.11.3 LOAD

Loads data by writing pages directly (faster than IMPORT but more restrictions).

# Basic load
db2 "LOAD FROM /data/emp.csv OF DEL
    INSERT INTO EMPLOYEE_COPY
    NONRECOVERABLE"

# Load with statistics collection
db2 "LOAD FROM /data/emp.csv OF DEL
    INSERT INTO EMPLOYEE_COPY
    STATISTICS YES WITH DISTRIBUTION
    COPY YES TO /backup/loadcopy"

# Load modes: INSERT, REPLACE, RESTART, TERMINATE
# After LOAD, table may be in SET INTEGRITY PENDING state
db2 "SET INTEGRITY FOR EMPLOYEE_COPY IMMEDIATE CHECKED"

Key difference from IMPORT: - LOAD does not fire triggers - LOAD does not check constraints inline (sets CHECK PENDING) - LOAD writes pages directly (bypasses buffer pool for write) - LOAD is 2-10x faster than IMPORT for large data volumes - NONRECOVERABLE load does not log; requires a backup afterward for recoverability

D.11.4 INGEST (Db2 11.1+)

A newer data movement command optimized for continuous streaming ingestion.

db2 "INGEST FROM FILE /data/stream.csv
    FORMAT DELIMITED
    INSERT INTO STREAM_TABLE"

INGEST handles file locking, concurrent appends, and can ingest from pipes and named files continuously. It sits between IMPORT (fully logged) and LOAD (bulk, restrictive) in terms of overhead.

D.12 Table Space Management (LUW)

# List table spaces
db2 "LIST TABLESPACES SHOW DETAIL"

# Create a table space
db2 "CREATE TABLESPACE ORDERS_TS
    PAGESIZE 32768
    MANAGED BY AUTOMATIC STORAGE
    EXTENTSIZE 32
    PREFETCHSIZE AUTOMATIC
    BUFFERPOOL BP32K"

# Create a large table space for LOBs
db2 "CREATE LARGE TABLESPACE LOB_TS
    PAGESIZE 32768
    MANAGED BY AUTOMATIC STORAGE"

# Create a temporary table space
db2 "CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2
    PAGESIZE 32768
    MANAGED BY AUTOMATIC STORAGE
    BUFFERPOOL BP32K_TEMP"

# Resize a DMS table space (non-automatic storage)
db2 "ALTER TABLESPACE USERSPACE1 EXTEND (ALL CONTAINERS 1000)"

# Drop a table space
db2 "DROP TABLESPACE OLD_TS"

# Rebalance after adding containers
db2 "ALTER TABLESPACE USERSPACE1 REBALANCE"

# Reduce high water mark (reclaim unused space)
db2 "ALTER TABLESPACE USERSPACE1 REDUCE MAX"

D.13 Security Commands (LUW)

# Create a role
db2 "CREATE ROLE app_readonly"
db2 "GRANT SELECT ON TABLE MYSCHEMA.ORDERS TO ROLE app_readonly"
db2 "GRANT ROLE app_readonly TO USER analyst1"

# Create an audit policy
db2 "CREATE AUDIT POLICY sensitive_data_policy
    CATEGORIES EXECUTE STATUS BOTH
    ERROR TYPE AUDIT"
db2 "AUDIT TABLE MYSCHEMA.CUSTOMER USING POLICY sensitive_data_policy"

# Row and Column Access Control
db2 "CREATE PERMISSION branch_filter ON MYSCHEMA.ACCOUNTS
    FOR ROWS WHERE BRANCH_ID = SESSION_USER
    ENFORCED FOR ALL ACCESS ENABLE"
db2 "ALTER TABLE MYSCHEMA.ACCOUNTS ACTIVATE ROW ACCESS CONTROL"

# Enable native encryption
db2 "UPDATE DB CFG FOR PRODDB USING ENCRYPT_DATABASE YES"

D.14 HADR Commands (LUW)

# Setup HADR (on primary)
db2 "UPDATE DB CFG FOR PRODDB USING
    HADR_LOCAL_HOST primary.example.com
    HADR_LOCAL_SVC 55000
    HADR_REMOTE_HOST standby.example.com
    HADR_REMOTE_SVC 55000
    HADR_REMOTE_INST db2inst1
    HADR_SYNCMODE NEARSYNC
    HADR_PEER_WINDOW 120
    LOGINDEXBUILD ON"

# Start HADR
db2 "START HADR ON DATABASE PRODDB AS PRIMARY"    # On primary
db2 "START HADR ON DATABASE PRODDB AS STANDBY"    # On standby

# Check HADR status
db2pd -db PRODDB -hadr

# Takeover (planned)
db2 "TAKEOVER HADR ON DATABASE PRODDB"

# Takeover (forced, when primary is down)
db2 "TAKEOVER HADR ON DATABASE PRODDB BY FORCE PEER WINDOW ONLY"

# Stop HADR
db2 "STOP HADR ON DATABASE PRODDB"