Db2 12 z/OS DBA cheat sheet

A Db2 subsystem runs as z/OS address spaces: the DIST (distributed), DBM1 (database services), MSTR (master), IRLM, plus optional DDF enclaves; DBM1 owns most database buffers and thread storage.

The Db2 catalog is a set of Db2 tables (SYSIBM.SYSTABLES, SYSCOLUMNS, SYSINDEXES, SYSROUTINES, etc.) in the Db2 catalog/directory; the directory holds internal control blocks used by the subsystem itself.

Installation uses job DSNTIJMV (or the CLIST/ISPF panels: DSNTINST) to generate the subsystem; the IVP (installation verification procedure) job is DSNTEJ1.

Migration to Db2 12 uses job DSNTIJTC (catalog migration) after installing the new function level; the catalog level and function level are separate concepts - function level is controlled by -START DB2 ... (ACTIVE) and by the migration job.

The Db2 12 function levels run from V12R1M500 (base) upward; each function level enables new features; APPLCOMPAT at the package/plan level controls SQL behavior independent of the subsystem function level.

DSNZPARM members (DSNTIJUZ) hold subsystem parameters; changes can be made online for many parameters (e.g., some buffer pool sizes) but others require a Db2 restart; use -SET SYSPARM to reload.

The IRLM (Internal Resource Lock Manager) provides locking; its size must be coordinated with Db2 (IRLMPROC, lock structure sizing in data sharing).

Db2 12 requires z/OS at a supported level and the subsystem must be at a supported function level for new SQL features; always check the PSP (Preventive Service Planning) bucket for required maintenance before migration.

A subsystem's SSID is a four-character identifier used in JCL, DSNZPARM, and commands; multiple subsystems can coexist on one z/OS system with separate catalogs.

A table space is the physical storage unit; a table is a logical object within it. Types include partitioned (PBG/PBR), segmented, simple, LOB, and XML; universal (UTS) table spaces are the modern default.

Index-controlled partitioning (PBR) uses the partitioning index to define limits; table-controlled partitioning defines limits in the table's PARTITION BY clause and is generally preferred for new designs.

A clustering index determines the physical order of rows; one clustering index per table; a partitioning index must be clustering for partitioned table spaces.

Referential integrity: parent/child relationships with ON DELETE RESTRICT/CASCADE/SET NULL; check pending status (CHECK DATA) must be resolved before the table space is fully available.

Identity columns (GENERATED ALWAYS/ BY DEFAULT) and sequence objects provide system-generated values; identity values are not guaranteed gap-free.

Temporal tables: system-period data versioning uses a history table and a transaction ID; application-period (business time) uses a business-time period; both are defined via the table's PERIOD clauses.

Encoding schemes: EBCDIC vs ASCII vs Unicode; CCSID settings on the table space/table affect data representation; Unicode (UTF-8) is common for distributed access.

Schema management: CREATE/ALTER/DROP with schema qualifiers; use of SET CURRENT SCHEMA and SET CURRENT SQLID controls unqualified object resolution and authorization.

Large objects (LOBs) are stored in LOB table spaces with auxiliary tables for the LOB data; inline LOBs can be stored in the base table space for small values.

EXPLAIN output (PLAN_TABLE / DSN_STATEMNT_TABLE) shows access path, join method (nested loop, merge scan, hybrid), and cost; use EXPLAIN to validate index usage and sort avoidance.

Predicates: matching (indexable) vs non-matching; stage 1 vs stage 2; sargable predicates (e.g., col = literal, col BETWEEN) can use indexes; expressions and functions on columns often prevent index use.

Index design: consider columns in equality predicates first, then range, then ORDER BY; a covering index avoids table access; index-only access is possible when all referenced columns are in the index.

Join methods: nested loop (good for small outer, indexed inner), merge scan join (sorted inputs), hybrid join (uses index on inner for outer rows in sort order); choose based on cardinality and statistics.

Statistics: RUNSTATS populates catalog statistics; FREQVAL and HISTOGRAM statistics help the optimizer for skewed data; COLGROUP statistics for correlated columns.

The optimizer uses cost-based decisions; REBIND with APREUSE/APCOMPARE can detect access path changes; PLANMGMT (BASIC/EXTENDED) retains previous access paths for fallback.

Cursor sensitivity: SENSITIVE, INSENSITIVE, ASENSITIVE; WITH UR (uncommitted read) vs CS (cursor stability) vs RS (read stability) vs RR (repeatable read) affect locking and concurrency.

Data sharing: the optimizer considers the cost of accessing data on other members; GBP-dependent access paths may be chosen when the group buffer pool is used.

SQL performance tuning: avoid SELECT *, use predicates that allow index access, consider FETCH FIRST n ROWS, and use EXPLAIN to verify that the plan matches expectations.

LOAD replaces or adds data; REPLACE vs RESUME; LOAD with SHRLEVEL NONE/CHANGE; LOG NO/YES controls whether loaded rows are logged for recovery.

UNLOAD extracts data to a sequential file; can be used with LOAD for data movement; consider UNLOAD with SHRLEVEL CHANGE for concurrent access.

REORG reorganizes table spaces and indexes; SHRLEVEL NONE/REFERENCE/CHANGE; REORG with inline copy (COPY YES) can create an image copy during reorganization.

COPY creates image copies (full or incremental); inline copies during REORG/LOAD reduce the need for separate copy jobs; SHRLEVEL CHANGE allows concurrent updates.

RECOVER restores from image copies and applies log records; RECOVER to a point in time (TORBA) requires log availability; RECOVER with BACKOUT YES can undo uncommitted changes.

CHECK DATA validates referential integrity and check constraints; CHECK INDEX validates index consistency; CHECK LOB validates LOB structures.

RUNSTATS gathers statistics; use REPORT YES to see statistics; consider sampling (SAMPLE) for large objects and FREQVAL for skewed columns.

REBUILD INDEX rebuilds indexes; useful after LOAD with LOG NO or after index corruption; consider REBUILD with SHRLEVEL CHANGE.

Utility control: TEMPLATE and LISTDEF simplify JCL; use of DSNUTILB (utility program) and the utility's restart capability (RESTART) after failures.

Image copies: full vs incremental; SHRLEVEL CHANGE vs REFERENCE; inline copies (COPYDDN) during REORG/LOAD; use of DFSMS concurrent copy or FlashCopy for large objects.

Logs: active logs (DSNJLOG1/2), archive logs (DSNJLOGA), and BSDS (DSNJBSDS) record log data sets; BSDS must be backed up and is critical for recovery.

Recovery types: current (to last commit), point-in-time (to a specific time), and conditional restart (to a specific log point); TORBA and TOCOPY options.

RECOVER with TOLOGPOINT or TORBA applies log records forward; RECOVER with BACKOUT YES rolls back uncommitted transactions.

Log management: DSNZPARM parameters (LOGLOAD, MAXLOG, etc.) control log sizing; archive log offload to tape/disk; consider dual logging (DUAL YES) for availability.

Backup strategy: regular full image copies, incremental copies, and log backups; test recovery regularly; consider FlashCopy for large table spaces.

BSDS backup and recovery: BSDS contains log inventory; loss of BSDS can prevent recovery; use DSNJU003 (change log inventory) carefully.

Data sharing: group buffer pool recovery and the need to coordinate backups across members; use of the GBP for page recovery.

Point-in-time recovery requires that the log data sets covering the target time are available; ensure that archive logs are retained per policy.

Monitor with Db2 commands (-DISPLAY STATISTICS, -DISPLAY BUFFERPOOL, -DISPLAY THREAD, -DISPLAY DATABASE) and the statistics trace (DSNSTATS) plus SMF records (IFCID 1, 2, 3, 5, 6, 7, 100, 101).

Buffer pool tuning: hit ratio and synchronous I/O; consider increasing buffer pool size or using page-fixed (PGSTEAL) options; check for deferred writes.

Locking and timeouts: -DISPLAY THREAD shows lock waits; use of LOCKMAX, DEADLOCK detection; consider isolation level and lock avoidance (CS with current data).

Statistics: RUNSTATS with INDEX, TABLE, and HISTOGRAM; use of FREQVAL; update statistics after significant data changes; consider statistics on columns used in predicates.

The optimizer uses catalog statistics; stale or missing statistics cause poor access paths; use EXPLAIN to verify.

DSNZPARM parameters affecting performance: EDM_SKELETON_POOL, EDM_POOL, CTHREAD, IDFORE/IDBACK, MAXDBAT, etc.

Real-time monitoring: Db2 12 provides real-time statistics (RTS) for table spaces and indexes; use them to decide when to REORG or RUNSTATS.

Data sharing: monitor GBP and lock structure usage; use of the IRLM and XCF for communication; check for false contentions.

Performance tuning: use of EXPLAIN, monitor trace, and accounting trace (IFCID 3) to identify expensive statements; consider package and plan rebind.

Authorization IDs: primary auth ID, secondary auth IDs, and group IDs; SET CURRENT SQLID changes the current SQL ID for dynamic SQL if authorized.

Privileges: table (SELECT, INSERT, UPDATE, DELETE, ALTER, REFERENCES), database (CREATETS, etc.), system (BINDADD, CREATETS, etc.), and schema privileges; use GRANT/REVOKE.

Roles: a role is a collection of privileges granted to a role, then the role is granted to users; roles simplify administration and are recommended over individual grants.

RLF (Resource Limit Facility): controls CPU consumption for dynamic SQL; RLF tables define limits; use of RLF for runaway queries.

Auditing: Db2 audit trace (SMF) records authorization failures and other events; use of AUDIT YES on objects; the audit trace is separate from the statistics trace.

Security: use of RACF (or equivalent) for authentication; Db2 uses the external security manager for auth ID verification; consider multi-factor authentication.

Trusted contexts: allow a middle-tier server to establish a trusted connection with a user's identity; used for distributed applications.

Row and column access control (RCAC): column masks and row permissions; enforced by the Db2 engine; consider performance impact.

Authorization checks: use of EXPLAIN to see authorization; use of DISPLAY DATABASE ... ACCESS to see granted privileges; consider revoking PUBLIC privileges.

Data sharing: multiple Db2 members share a single database; uses a coupling facility (CF) for group buffer pools, lock structure, and shared communications area (SCA).

Group buffer pool (GBP): caches pages for cross-member access; GBP-dependent pages must be written to disk before invalidation; consider GBP sizing and structure.

Lock structure: global locks for data sharing; IRLM manages locks; consider lock structure size and false contention.

Member restart and recovery: a failed member's locks are released; indoubt transactions are resolved; use of the SCA for member status.

Availability: use of SHRLEVEL CHANGE for utilities; online REORG; consider data sharing for continuous availability; use of FlashCopy for backups.

Operations: -START/-STOP commands for members, databases, and utilities; use of the command interface (DSN) and the Db2 console.

Planned outages: migration to new function levels requires careful planning; use of the catalog migration and fallback considerations.

Monitoring availability: check for check pending status, restricted states, and utility failures; use of DISPLAY DATABASE to see status.

Data sharing recovery: use of the GBP and lock structure recovery; consider the impact of a CF failure; use of the SCA for member communication.