IBM Db2 13 z/OS DBA Professional key terms

A single running instance of Db2 on z/OS, identified by a four-character subsystem ID and made up of address spaces such as MSTR, DBM1, IRLM, and DIST. It owns catalogs, logs, and buffer pools.

The set of Db2-maintained tables in database DSNDB06 that describe every object, authorization, and statistic in the subsystem. Db2 updates it for DDL; user SQL can read it but should never modify it directly.

Internal Db2 structures in database DSNDB01 that hold executable forms of objects, such as skeleton packages, the DBDs, and log inventory. Unlike the catalog, its contents are not directly readable by SQL.

A pair of VSAM data sets that record the inventory of active and archive log data sets, checkpoint information, and utility history. Db2 needs at least one valid BSDS to start, so both copies should be backed up.

The preallocated set of log data sets (COPY1 and COPY2) to which Db2 writes log records before they are offloaded. It must be sized to hold the work between offloads, including the longest running unit of recovery.

Log data sets produced when the active log fills and is offloaded by Db2. They preserve log records for rollback, restart, and conditional restart, and are the input for log-based recovery of objects and indexes.

The scope of a single commit or rollback, spanning all changes made by a logical unit of work in the Db2 subsystem. Db2 tracks its log range (URID) and uses it for restart, indoubt resolution, and log-based recovery.

The protocol Db2 uses with other resource managers, coordinated through z/OS RRS, so that a transaction either commits at all participants or rolls back at all of them. Phase one prepares and logs; phase two commits or backs out.

A unit of recovery that was prepared but whose commit or rollback decision was lost, typically after a failure during two-phase commit. Db2 resolves it by querying the coordinator or by operator command, and it can hold locks until resolved.

A Db2 startup that uses a modified copy of the BSDS and log to start from a chosen log point, optionally with a new log inventory. It is used to bypass damaged log data or to recover to a specific point in time.

Restoring a table space or index to its state at a chosen log RBA or timestamp by restoring a image copy and applying log records up to that point. It requires the object to be recoverable and the needed log to be available.

A copy of a table space or index space used as the starting point for recovery. It can be produced by the COPY utility, by the inline copy option of LOAD or REORG, or by a vendor tool, and is registered in SYSIBM.SYSCOPY.

The catalog table that records image copies, their types, and related recovery information such as LOAD and REORG log ranges. Recovery utilities read it to choose the copies and log ranges needed to rebuild an object.

The Db2 utility that restores a table space or index to a consistent point, either current or point-in-time, by restoring registered image copies and applying log records. It can also rebuild indexes and report what it would do.

The Db2 utility that creates full or incremental image copies of table spaces and index spaces and registers them in SYSCOPY. Options include SHRLEVEL CHANGE for concurrent updates and CHANGELIMIT to skip unchanged objects.

A utility option that lets applications read and write the target object while the utility runs. For COPY and REORG it relies on log processing or drain handling to keep the result consistent, at the cost of extra overhead.

A Db2 mechanism that claims exclusive or shared access to an object by stopping new claimers and waiting for existing ones to release it. Utilities use drains to reach a point of consistency; long-running claims can delay or fail them.

A Db2 threshold that limits how long an application can hold a claim on a table space, index, or partition. When the claim timeout is exceeded, Db2 can fail the claim or the utility, which affects availability during maintenance.

The Db2 utility that reorganizes table spaces and indexes, reclaiming space and restoring clustering and index order. It can run online with SHRLEVEL CHANGE, uses shadow data sets, and supports partition-level and index rebuild options.

The Db2 utility that gathers statistics on table spaces, indexes, partitions, and columns and stores them in the catalog. The optimizer uses these statistics for access path selection, so stale statistics can cause poor plans.

The Db2 component that chooses an access path for each SQL statement by comparing estimated costs. It uses catalog statistics, filter factors, and cost parameters, and its choice is stored in the plan or package at bind time.

The strategy Db2 uses to satisfy a query, such as a tablespace scan, index scan, or index-only access, plus join order and method. It is determined at bind time and recorded in the plan table and package.

A Db2 facility that writes the chosen access path and cost estimates for static or dynamic SQL into plan tables. DBAs use it to diagnose why a statement performs poorly and to compare paths before and after changes.

A user-created table with a Db2-defined layout into which EXPLAIN writes rows describing access paths, joins, and costs. It must exist before EXPLAIN runs and is usually queried with formatted reports.

The optimizer's estimate of the fraction of rows that satisfy a predicate. It is derived from catalog statistics and column cardinality, and it drives cost estimates and join order decisions.

A compiled, stored collection of SQL statements bound from a DBRM, with its own consistency token and access paths. Packages are the modern unit of binding and are referenced by plans or by application packages directly.

A Db2 object that associates an application with the packages and authorization it may use at run time. It controls which packages are available and can carry bind options that affect execution.

The Db2 process that converts SQL in a DBRM or package into executable form and selects access paths, storing the result in the catalog. Bind options such as ISOLATION, RELEASE, and EXPLAIN affect behavior and diagnostics.

The degree to which a Db2 transaction is protected from the uncommitted or committed changes of others, set at bind time or per statement. Levels include UR, CS, RS, and RR, trading concurrency against consistency and lock duration.

The granularity at which Db2 locks data: table space, partition, page, row, or LOB. It is set by the LOCKSIZE option of CREATE or ALTER and interacts with isolation level to determine concurrency.

A Db2 limit that ends a statement or utility when it waits too long for a lock or for I/O. IRLMRWT controls lock wait, and timeouts surface as resource unavailable codes that DBAs tune with parameters and design.

The z/OS address space that maintains Db2 lock and latch information and arbitrates lock requests between Db2 members. Its parameters, such as lock timeout, strongly influence concurrency and deadlock behavior.

The Db2 master address space that handles startup, shutdown, commands, and subsystem-wide services. It coordinates other address spaces and must be active for the subsystem to run.

A Db2 configuration in which multiple members of a group access the same data concurrently. It requires a coupling facility for locks and caching, a shared catalog, and careful design of groups and member names.

A z/OS shared memory resource that Db2 data sharing members use for global locking and for group buffer pool structures. Its structure sizes and link speeds affect cross-member performance and recovery.

A coupling facility cache structure that holds pages shared among data sharing members and coordinates cross-invalidation. Its size and the GBP-dependent state of objects affect read efficiency and write cost.

A Db2 memory area that caches table space and index pages to reduce disk I/O. Sizes, page sizes, and thresholds such as VPSEQT and DWQT are tuned with ALTER BUFFERPOOL and monitored for hit ratios.

A subdivision of a Db2 buffer pool that isolates pages of one table space or index from others to protect its working set. It is defined with ALTER BUFFERPOOL VPSIZE and can improve performance for critical objects.

Db2's technique of reading multiple pages in one I/O before they are requested, using sequential, list, or dynamic prefetch. It reduces I/O waits for scans and is triggered by access patterns and buffer pool thresholds.

An index whose key order determines the physical order of rows in the table space. Choosing it well improves sequential access and REORG effectiveness, and only one clustering index is allowed per table.

A table space split into partitions by key ranges or by growth, each with its own data set and optionally its own indexes. Partitioning enables partition-level utilities, improved availability, and limited partition scans.

A partitioned table space that adds new partitions automatically as data grows, up to a maximum defined at creation. It simplifies capacity planning but limits key-range-based partition elimination.

A partitioned table space whose partitions are defined by explicit key limits, allowing partition elimination for matching predicates. Limits can be added or rotated with ALTER, supporting data lifecycle management.

The portion of a partitioned index that corresponds to one partition of the underlying table space. It can be managed and recovered separately, and its partitioning scheme must be compatible with the table space.

SQL statements that give or remove privileges on Db2 objects and packages, or that assign roles to auth IDs. Privileges are stored in the catalog and checked at execution, and they can be granted with GRANT OPTION.

An identifier, such as an authorization ID from RACF or a Db2 role, against which Db2 checks privileges. Primary and secondary auth IDs affect which privileges apply during a connection or thread.

A Db2 object that groups privileges and can be granted to auth IDs, simplifying administration. Roles can be granted to other roles and are especially useful in data sharing and large user populations.

A Db2 object that lets a middle-tier server establish a session and then switch to a different end-user auth ID with defined privileges. It reduces the need to grant broad privileges to application servers.

Db2 features that enforce row-level security with permission and mask rules, and column-level masking, independent of application code. They are defined on tables and apply to all access, including utilities and dynamic SQL.

The Db2 utility that loads data into tables from sequential input, with options for replacing or adding rows, enforcing constraints, and producing inline copies. It is faster than INSERT for bulk data but affects availability.

The Db2 utility that extracts data from tables or table spaces into sequential data sets, often as input to LOAD or for archiving. It supports delimited formats and can unload selected partitions or columns.

The Db2 utility that verifies the internal consistency of table spaces and indexes, and can report or fix inconsistencies. It is often run after a failure or before recovery to decide what needs repair.

The Db2 utility that corrects inconsistencies in the catalog, directory, or data, such as resetting an object's status or fixing broken page sets. It is a targeted repair tool used when CHECK reports damage.