IBM Db2 13 z/OS DBA Professional flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Universal table space

The default table space type in Db2 13; it is either segmented (with SEGSIZE) or partitioned (partition-by-range or partition-by-growth), and it replaces classic segmented and partitioned table spaces.

Partition-by-growth table space

A universal partitioned table space that adds new partitions automatically as data grows; MAXPARTITIONS sets the upper limit from 1 to 4096, and no explicit partition boundaries are defined.

Partition-by-range table space

A universal partitioned table space where each partition has an explicit boundary value; it supports relative page numbers so mass deletes can be done by dropping or rotating partitions.

Page size row limit

For 4K, 8K, and 16K pages the maximum row length is the page size minus 22 bytes; for 32K pages it is the page size minus 54 bytes; LOB data is stored separately.

System-period temporal table

A table with a SYSTEM_TIME period that keeps a history table of prior row versions; Db2 maintains the history automatically using a transaction-id column, and time travel queries use FOR SYSTEM_TIME.

Identity column

A column whose values are generated by Db2; GENERATED ALWAYS prevents user-supplied values, while GENERATED BY DEFAULT accepts them, and the column can be defined as a primary key.

Referential integrity delete rules

ON DELETE CASCADE deletes dependent rows, SET NULL sets the foreign key to null, and RESTRICT prevents the delete when dependent rows exist; the parent key must be a primary key or unique index.

Index include columns

Columns added to an index with the INCLUDE clause; they are stored in the index leaf pages but not in the key, so a query can be covered without enlarging the key.

Access path type M

In EXPLAIN output, M indicates a matching index scan where the index key is used to limit the scan; MATCHCOLS shows how many key columns are matched by predicates.

Stage 1 and stage 2 predicates

Stage 1 predicates can be evaluated by data manager using an index or page; stage 2 predicates are evaluated later by the relational data services, so they reduce performance and should be minimized.

RUNSTATS KEYCARDS

A RUNSTATS option that collects key cardinality statistics for indexes; these statistics help the optimizer choose matching index scans and estimate filter factors.

FREQVAL statistics

A RUNSTATS option that collects the most frequent values of a column, with a specified count and percentage, so the optimizer can account for skewed data distribution.

Access path stabilization

A Db2 feature that stores EXPLAIN output for a statement in a stabilization group; the stored access path is reused unless the statement is rebound with a different group.

Sequential prefetch

A prefetch method that reads up to 32 pages per I/O when sequential access is detected; dynamic prefetch triggers this automatically, while static prefetch is chosen at bind time.

List prefetch

A prefetch method chosen when the optimizer estimates many random page accesses; it builds a list of page numbers and reads them in order to reduce I/O.

Buffer pool VPSIZE

The parameter that sets the virtual buffer pool size in pages; the default pools are BP0 (4K), BP8K0, BP16K0, and BP32K, and a group buffer pool caches pages in data sharing.

Lock modes and size

IS, IX, S, U, X, and SIX are the lock modes; LOCKSIZE controls whether locks are taken at the page, row, table, or table space level, and RELEASE(DEALLOCATE) reduces lock overhead.

DSN_STATEMNT_TABLE

A Db2 catalog table that stores statement-level cost estimates from EXPLAIN, including PROCMS and TOTALCOST; it helps compare alternative access paths for the same statement.

Active and archive logs

Active log data sets hold current log records and are referenced by the BSDS; archive log data sets hold offloaded copies, and dual logging protects against a single log data set failure.

Conditional restart

A Db2 restart using a restart control statement to specify a point in the log; BACKOUT YES rolls back uncommitted work and FORWARD YES applies committed log records.

Image copy SHRLEVEL

SHRLEVEL CHANGE allows concurrent updates during a copy, SHRLEVEL REFERENCE requires no updates, and a FlashCopy or DFSMS concurrent copy produces a consistent copy quickly.

RECOVER utility options

RECOVER TO CURRENT or TO RBA/TIMESTAMP restores a page set and applies logs; TORBA and TOCOPY select the starting point, LOGONLY applies only log records, and RESTORELOG applies archive logs.

Group buffer pool rebuild

A data sharing recovery action that reconstructs the group buffer pool contents after a failure; it restores cached pages so members can continue without a full restart.

Restricted status

A state such as UTUT, AREO*, or copy-pending that limits access to an object after a utility abend; DISPLAY DATABASE shows the status and START DATABASE clears it.

BSDS backup

The bootstrap data set records log ranges and must be backed up together with the active and archive logs; without a valid BSDS, Db2 cannot restart or recover.

SYSADM authority

The highest Db2 administrative authority, defined in the DSNZPARM or installation panel; it allows all administrative and object operations, including granting other authorities.

WITH GRANT OPTION

A clause on GRANT that lets the recipient grant the same privilege to other users; REVOKE CASCADE removes dependent grants that were made through that option.

Role

A Db2 object created with CREATE ROLE that groups privileges; it can be granted to users or other roles, simplifying administration when many users need the same privileges.

Column mask

A Db2 RCAC object created with CREATE MASK that hides or transforms column values based on an expression; it is enforced for all access paths, including utilities and dynamic SQL.

Row permission

A Db2 RCAC object created with CREATE PERMISSION that filters rows based on an expression; it restricts which rows a user can see or modify regardless of the access path.

Trusted context

A Db2 object created with CREATE TRUSTED CONTEXT that lets a mid-tier server establish a connection using a specific user identity, with optional attributes controlling the connection.

LOAD ENFORCE CONSTRAINTS

A LOAD utility option that controls whether referential integrity is checked during the load; ENFORCE NO skips checking, which is faster but can leave violations.