IBM Db2 13 z/OS DBA Professional cheat sheet

Page size choices: 4K, 8K, 16K, 32K for table spaces; the row length limit for a table space is (page size minus 22 bytes) for 4K/8K/16K and (page size minus 54 bytes) for 32K; LOB/XML data lives in a separate auxiliary table space.

SEGSIZE in a segmented table space must be a multiple of 4 and no greater than 64; a partition-by-growth (PBG) table space uses MAXPARTITIONS 1-4096 and grows automatically, while partition-by-range (PBR) uses explicit partition boundaries and supports relative page numbers (RPN) for mass deletes.

Universal table spaces are the default in Db2 13: CREATE TABLESPACE without TYPE creates a UTS; segmented (UTS) uses SEGSIZE, partitioned (UTS) uses either partition-by-range or partition-by-growth; classic segmented and partitioned table spaces are deprecated.

Index design rules: an index key is limited to 2000 bytes for indexes on tables with 4K pages and up to 8000 bytes on larger pages; include columns (INCLUDE) let you cover a query without adding to the key; index compression (COMPRESS YES) is recommended for large indexes.

Referential integrity: a parent key must be a primary key or a unique index; ON DELETE CASCADE, SET NULL, or RESTRICT are the delete rules; LOAD with ENFORCE CONSTRAINTS versus ENFORCE NO determines whether RI is checked during load.

Temporal tables: system-period temporal (SYSTEM_TIME) uses a history table and a transaction-id column; application-period temporal (BUSINESS_TIME) uses business-time periods; bitemporal combines both; time travel queries use FOR SYSTEM_TIME AS OF or BETWEEN.

Identity and sequence: GENERATED ALWAYS versus GENERATED BY DEFAULT; identity columns cannot be updated when GENERATED ALWAYS; a sequence object (CREATE SEQUENCE) is independent of any table and its cache value affects gap behavior after restart.

Schema and object naming: an unqualified object uses the SQL authorization ID or the QUALIFIER special register; CREATE SCHEMA can create multiple objects atomically; ALTER TABLE ADD COLUMN with a default value avoids a data rebuild only for certain nullable/default cases.

EXPLAIN output: PLAN_TABLE access path types include R (tablespace scan), I (index scan), M (matching index scan), and MX (multiple index scan); ACCESSTYPE plus MATCHCOLS and PREFETCH indicate how predicates are applied; use EXPLAIN STABILIZATION for access path management.

Indexability rules: a predicate on a column is stage 1 sargable only if the column is the leading column of an index key; non-column predicates, expressions, and scalar functions usually become stage 2 and are evaluated after data retrieval.

RUNSTATS options: TABLE(ALL) INDEX(ALL) KEYCARDS collects key cardinality statistics needed for matching-index decisions; FREQVAL with COUNT and percentage collects frequent values for skewed data; HISTOGRAM collects distribution statistics for non-uniform columns.

Access path stabilization: a stabilization group captures EXPLAIN output for a static or dynamic SQL statement; the stabilized access path is used unless the statement is rebound with a different group; monitor with the STABILIZATION option of EXPLAIN and the DSN_STATEMNT_TABLE.

Prefetch: sequential prefetch reads up to 32 pages per I/O; dynamic prefetch is triggered by sequential detection at run time; list prefetch is chosen when the optimizer estimates random access to many pages; index lookaside reduces index I/O for repeated lookups.

Buffer pool sizing: virtual buffer pools use VPSIZE in pages; the default pool sizes are 4K (BP0), 8K (BP8K0), 16K (BP16K0), and 32K (BP32K); page-fixed and group buffer pools (GBP) for data sharing cache pages across members.

Locking and concurrency: IS, IX, S, U, X, and SIX lock modes; lock size is controlled by LOCKSIZE (ANY, PAGE, ROW, TABLE, TABLESPACE); CURRENTDATA(NO) versus CURRENTDATA(YES) changes whether ambiguous rows are returned; RELEASE(DEALLOCATE) reduces lock overhead for long-running transactions.

Statement-level monitoring: DSN_STATEMENT_TABLE and DSN_STATEMNT_TABLE hold cost estimates; the MONITOR trace classes capture accounting, statistics, and performance records; use the IFI (instrumentation facility interface) or Db2 supplied functions to read monitor data.

Logging: the active log (BSDS-referenced) holds current log records; the archive log holds offloaded copies; dual logging (two active log data sets) protects against a single log data set failure; the BSDS records log ranges and must be backed up with the logs.

Recovery scenarios: conditional restart (CRESTART) uses a restart control statement to specify a point in the log; BACKOUT YES rolls back uncommitted work; FORWARD YES applies committed log records; a point-in-time recovery restores a copy and applies logs to a chosen RBA or timestamp.

Image copies: FULL versus INCREMENTAL; SHRLEVEL CHANGE allows concurrent updates during copy; SHRLEVEL REFERENCE requires no updates; a FlashCopy or DFSMS concurrent copy can produce a consistent copy quickly; COPY YES/NO on the table space controls whether the utility may take a copy.

RECOVER utility: RECOVER TO CURRENT or TO RBA/TIMESTAMP; TORBA and TOCOPY options select the starting point; LOGONLY applies only log records; RESTORELOG applies archive logs; a recover to a point prior to the last quiesce point requires care with data consistency.

Data sharing recovery: the coupling facility holds the shared lock structure and the group buffer pool; a failed member's locks are released by the surviving members; GBP-dependent pages must be written to disk before a member can restart cleanly; use the RECOVER utility with the data sharing scope.

Backup and recovery strategy: the BSDS, active logs, and archive logs must be backed up together; a consistent full image copy plus archive logs supports recovery to any point after the copy; catalog and directory table spaces require their own recovery plan and cannot be recovered independently of each other in all cases.

Restart and abend handling: a Db2 restart reads the BSDS, opens active logs, and performs forward and backward log processing; a utility abend may leave objects in a restricted state (UTUT, AREO*, or copy-pending); the DISPLAY DATABASE command shows restricted status and the START DATABASE command clears it.

Availability features: the Db2 restart light (RL) mode allows a member to restart without full log processing in some cases; the group buffer pool rebuild (GBP rebuild) restores data sharing cache after a failure; the RECOVER POSTPONED status indicates a page set needing recovery.

Privilege model: administrative authorities (SYSADM, SYSCTRL, SYSMAINT, SYSOPS) versus object privileges (SELECT, INSERT, UPDATE, DELETE, ALTER, REFERENCES, TRIGGER, EXECUTE); SYSADM is the highest authority and is defined in the DSNZPARM or via the installation panel.

GRANT and REVOKE: WITH GRANT OPTION lets a recipient grant the privilege onward; REVOKE CASCADE removes dependent grants; a role (CREATE ROLE) groups privileges and can be granted to users or other roles; roles simplify administration in large environments.

Row and column access control (RCAC): a column mask (CREATE MASK) hides column values based on an expression; a row permission (CREATE PERMISSION) filters rows based on an expression; RCAC is enforced for all access paths including utilities and dynamic SQL, and requires the appropriate authority to define.

Trusted context and encryption: a trusted context (CREATE TRUSTED CONTEXT) allows a mid-tier server to establish a connection with a specific user identity; encryption at rest uses the Db2 encryption key (DSN6ENCR) and the ENCRYPTION KEY LABEL; SSL/TLS protects network traffic.

Utility authorization: LOAD, REORG, RECOVER, COPY, RUNSTATS, and other utilities require the appropriate utility authority (SYSADM, SYSCTRL, SYSMAINT, or the relevant privilege); the Db2 utility job must have the correct RACF/ACF2/Top Secret profile access to the data sets.

LOAD utility: LOAD REPLACE versus LOAD RESUME; ENFORCE CONSTRAINTS YES/NO controls RI checking; LOG YES/NO controls log records; SHRLEVEL CHANGE allows concurrent access; the utility can produce an inline copy (COPY YES) and can collect statistics with STATISTICS.

REORG utility: REORG TABLESPACE rebuilds the table space and can be run with SHRLEVEL REFERENCE or CHANGE; REORG with inline COPY produces a backup; REBUILD INDEX rebuilds indexes; the utility can collect RUNSTATS inline; REORG discards the pending state and resets the REORG-pending flag.

RUNSTATS and CHECK utilities: RUNSTATS collects catalog statistics used by the optimizer; CHECK DATA verifies referential integrity and can report or delete violations; CHECK INDEX verifies index consistency; CHECK LOB verifies LOB data; the REPORT option controls output.