Glossary

This glossary defines the key terms used throughout this book. Where a term has different implications on z/OS versus LUW, both are noted. Chapter references indicate where the term is first introduced or discussed most thoroughly.

Access path. The method DB2's optimizer chooses to retrieve data for a query---such as table scan, index scan, or index-only access. (Chapter 12)

Active log. The set of log files currently in use for recording database changes. When an active log fills, it becomes an archive log. (Chapter 14)

ALLOCATE. On z/OS, the process of assigning data sets (files) to a job or utility. ALLOCATE statements in JCL map DD names to physical data sets.

Archive log. A log file that has been filled and moved to archive storage. Required for point-in-time recovery. On LUW, controlled by LOGARCHMETH1. On z/OS, managed by the archive log data sets. (Chapter 14)

Automatic storage. An LUW feature where DB2 manages table space storage allocation across defined storage paths, eliminating manual container management. (Chapter 4)

Base table. A permanent table defined with CREATE TABLE, as opposed to a view, temporary table, or derived table.

Bind. The process of converting a DBRM (z/OS) or SQL statements into an executable access plan stored as a package. On z/OS, BIND PLAN and BIND PACKAGE are explicit operations. On LUW, binding occurs implicitly for dynamic SQL. (Chapter 19)

BLU Acceleration. An LUW feature (Advanced Edition) that uses column-organized storage, SIMD processing, and actionable compression for analytical workloads. (Chapter 27)

Buffer pool. An area of memory that caches data and index pages to reduce physical I/O. Both z/OS and LUW use buffer pools extensively. (Chapter 9)

Cardinality. The number of rows in a table, or the number of distinct values in a column (column cardinality). Both are collected by RUNSTATS and stored in catalog statistics. (Chapter 11)

Catalog. System tables that store metadata about all database objects. On LUW: SYSCAT.* views. On z/OS: SYSIBM.* tables. (Chapter 3)

CHECK PENDING. A restrictive state indicating that DB2 cannot guarantee referential or check constraint integrity. Cleared with SET INTEGRITY. (Chapter 11)

Checkpoint. A point at which DB2 writes modified pages from buffer pools to disk and records the log position. Reduces recovery time by establishing a known-consistent starting point. (Chapter 14)

Clause. A component of an SQL statement, such as WHERE, GROUP BY, HAVING, or ORDER BY.

Clustering index. An index that determines the physical ordering of rows in a table. On z/OS, defined with the CLUSTER keyword. On LUW, defined implicitly or with ALTER TABLE. (Chapter 8)

CLUSTERRATIO. A statistic (0-100) measuring how well the physical order of rows matches the index order. Higher is better. (Chapter 8)

Coalesce. (1) A built-in function that returns the first non-null argument. (2) On z/OS, the process of merging adjacent free space in a table space.

Column-organized table. An LUW table stored in columnar format for analytical query performance. Part of BLU Acceleration. (Chapter 27)

COMMIT. An SQL statement that makes all changes in the current transaction permanent and releases locks. (Chapter 7)

Compensation log record (CLR). A log record written during rollback to record the undo of a previous change. Ensures idempotent recovery.

Composite index. An index on two or more columns. Also called a multi-column index or compound index. (Chapter 8)

Concurrency. The ability of multiple transactions to access the same data simultaneously. Managed through locking and isolation levels. (Chapter 7)

Connection concentrator. An LUW feature that allows many client connections to share a smaller number of database coordinator agents, reducing memory overhead. (Chapter 26)

Correlation name. An alias assigned to a table or subquery in the FROM clause (e.g., FROM EMPLOYEE E).

Coupling Facility (CF). A z/OS hardware component in a Parallel Sysplex that provides shared memory structures for data sharing: group buffer pools, lock structures, and list structures. (Chapter 18)

CURRENT SCHEMA. A special register containing the default schema used for unqualified object names.

Cursor. A named control structure used to process a result set one row at a time in a host-language program or stored procedure. (Chapter 19)

Cursor stability (CS). The default isolation level in DB2. Locks the current row of a cursor and releases it when the cursor moves to the next row. (Chapter 7)

Data page. A unit of storage (typically 4 KB, 8 KB, 16 KB, or 32 KB) that holds table rows.

Data sharing. A z/OS capability where multiple DB2 members access the same data concurrently using a Coupling Facility for coordination. (Chapter 18)

Database partitioning feature (DPF). An LUW feature that distributes data across multiple database partitions (nodes) using a distribution key. Also called MPP (massively parallel processing). (Chapter 28)

DB2 Connect. An IBM product that provides DRDA connectivity from LUW clients and applications to DB2 for z/OS and DB2 for i.

DBRM (Database Request Module). On z/OS, the output of the SQL precompiler containing SQL statements extracted from a host-language program. DBRMs are input to the BIND process. (Chapter 19)

DDL (Data Definition Language). SQL statements that define or modify database objects: CREATE, ALTER, DROP, RENAME.

Deadlock. A situation where two or more transactions are each waiting for a lock held by the other. DB2 detects deadlocks and rolls back one transaction (SQLCODE -911). (Chapter 7)

DML (Data Modification Language). SQL statements that modify data: INSERT, UPDATE, DELETE, MERGE.

DRDA (Distributed Relational Database Architecture). The protocol used for communication between DB2 systems and between DB2 and other compliant databases. (Chapter 26)

Dynamic SQL. SQL statements that are prepared and executed at runtime, as opposed to static SQL that is bound ahead of time. (Chapter 19)

EDU (Engine Dispatchable Unit). An LUW internal thread or process that performs database work.

Encryption. Protection of data at rest (native encryption, table space encryption) or in transit (SSL/TLS). (Chapter 22)

EXPLAIN. A facility that records the access plan chosen by the optimizer into explain tables, without executing the query. (Chapter 12)

Expression-based index. An LUW index defined on an expression (e.g., UPPER(LAST_NAME)) rather than a plain column.

Extent. A contiguous allocation of pages in a table space. New extents are allocated as a table grows.

Fanout. The number of child pointers in a B+ tree node. Determines tree height for a given number of entries. (Appendix A)

Federated database. An LUW feature that allows queries to span DB2 and non-DB2 data sources (Oracle, SQL Server, flat files, etc.) using nicknames. (Chapter 29)

Filter factor. The estimated fraction of rows that satisfy a predicate. Used by the optimizer for cardinality estimation. (Appendix A, Chapter 12)

Free space. Unused space within a page or extent. Controlled by PCTFREE and FREEPAGE parameters.

Global temporary table. A table whose definition persists but whose data is private to each session and is automatically cleared at session end.

Group buffer pool (GBP). In z/OS data sharing, a Coupling Facility structure that caches pages shared across members. (Chapter 18)

HADR (High Availability Disaster Recovery). An LUW feature that replicates data to standby databases using log shipping. (Chapter 17)

Hash join (HSJOIN). A join method that builds a hash table from the smaller input and probes it with the larger input. Efficient for large equijoins. (Chapter 12)

Histogram. A statistical structure collected by RUNSTATS that captures the distribution of values in a column, improving cardinality estimation for non-uniform data. (Chapter 11)

Host variable. A program variable used in embedded SQL to pass data between the application and DB2. Prefixed with a colon in SQL statements. (Chapter 19)

Identity column. A column that automatically generates sequential values on INSERT. Defined with GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY.

Image copy. On z/OS, a backup of a table space or index created by the COPY utility. Analogous to BACKUP on LUW. (Chapter 15)

Index. A B+ tree structure that provides efficient access to table rows based on key column values. (Chapter 8)

Index-only access. A query execution strategy where all required columns are available in the index, eliminating the need to read data pages. (Chapter 12)

Inline statistics. Statistics collected during a LOAD or REORG operation, avoiding the need for a separate RUNSTATS step.

Isolation level. The degree of protection a transaction has from changes made by other concurrent transactions. DB2 supports UR, CS, RS, and RR. (Chapter 7)

JCL (Job Control Language). The z/OS language for defining batch jobs, including utility executions.

Join. A relational operation that combines rows from two or more tables based on a related column.

Latch. An internal, short-duration lock used by DB2 to protect in-memory structures. Not visible to applications but can cause contention under high concurrency.

Lock. A mechanism to control concurrent access to database objects. DB2 uses row-level, page-level, and table-level locks. (Chapter 7)

Lock escalation. The automatic promotion of many row or page locks to a single table lock, reducing lock management overhead but increasing contention. (Chapter 7)

Log. A sequential record of all database changes, used for transaction rollback and crash recovery. (Chapter 14)

Materialized query table (MQT). A table whose content is defined by a query and can be refreshed periodically. The optimizer can automatically route queries to MQTs. Also called a summary table. (Chapter 12)

Member. In z/OS data sharing or LUW pureScale, an individual DB2 instance that participates in the cluster. (Chapter 18)

MERGE. An SQL statement that combines INSERT and UPDATE (upsert) operations based on a matching condition.

Nested-loop join (NLJOIN). A join method that, for each row of the outer table, scans the inner table (often via an index). Efficient when the outer table is small or the inner access is indexed. (Chapter 12)

Null. A special marker indicating an unknown or missing value. Not equal to zero or empty string.

OLAP function. See Window function.

Optimizer. The DB2 component that determines the most efficient access plan for a query based on statistics, available indexes, and cost models. (Chapter 12)

Package. A database object containing the compiled (bound) access plan for a set of SQL statements. On z/OS, packages are the unit of authorization and plan management. (Chapter 19)

Page. The fundamental unit of I/O and storage in DB2. Common sizes: 4 KB (z/OS default), 8 KB, 16 KB, 32 KB. (Chapter 4)

Partition. A segment of a table or index space that can be managed independently. On z/OS, partitioned table spaces divide data by key ranges. On LUW, range, list, or hash partitioning is available. (Chapter 4)

PCTFREE. The percentage of each page left free during LOAD or REORG for future inserts and updates. (Chapter 4)

Plan. On z/OS, a collection of packages bound together for execution by an application. On LUW, the term "access plan" refers to the optimizer's chosen execution strategy.

Predicate. A condition in a WHERE or HAVING clause that evaluates to TRUE, FALSE, or UNKNOWN.

Prefetch. DB2's mechanism for reading multiple pages ahead of actual demand, reducing I/O wait time. Sequential prefetch reads contiguous pages; list prefetch reads scattered pages. (Chapter 9)

pureScale. An LUW clustering technology that provides shared-data, active-active access across multiple members using a Cluster Caching Facility. (Chapter 18)

RACF (Resource Access Control Facility). The z/OS security product used to authenticate users and control access to resources, including DB2 objects. (Chapter 22)

RBDP (Rebuild Pending). A z/OS status indicating an index must be rebuilt before it can be used.

RECP (Recover Pending). A z/OS status indicating a table space requires recovery before it can be accessed.

Redbook. An IBM technical publication providing in-depth guidance on IBM products. Available free at ibm.com/redbooks.

Referential integrity (RI). A constraint that ensures foreign key values match primary key values in a parent table. (Chapter 3)

REORG. A utility that reorganizes a table space or table to reclaim fragmented space and restore physical clustering order. (Chapter 11)

Repeatable read (RR). An isolation level that locks all rows accessed by a transaction until COMMIT, preventing phantom reads. (Chapter 7)

RID (Record Identifier). A pointer that uniquely identifies the physical location of a row. Consists of page number and slot number.

ROLLBACK. An SQL statement that undoes all changes made in the current transaction. (Chapter 7)

Row and Column Access Control (RCAC). An LUW and z/OS feature for fine-grained security using row permissions and column masks. (Chapter 22)

RUNSTATS. A utility that collects optimizer statistics (cardinalities, distributions, cluster ratios) for tables and indexes. (Chapter 11)

Savepoint. A named point within a transaction to which you can roll back without rolling back the entire transaction.

Segmented table space. A z/OS table space type where each table occupies its own set of pages (segments), allowing efficient mass delete and space reclamation. (Chapter 4)

Self-Tuning Memory Manager (STMM). An LUW feature that automatically adjusts memory allocation among buffer pools, sort heap, package cache, and lock list. (Chapter 9)

Sequence. A database object that generates sequential numbers independently of any table. Created with CREATE SEQUENCE. (Chapter 5)

SPUFI (SQL Processing Using File Input). A z/OS ISPF-based tool for executing SQL statements interactively.

SQL PL (SQL Procedural Language). DB2's procedural language extension for stored procedures, functions, and triggers. (Chapter 21)

SQLCODE. A DB2-specific integer return code from SQL execution. 0 = success, positive = warning, negative = error. (Appendix B)

SQLSTATE. A five-character string return code following the ISO/ANSI SQL standard. More portable than SQLCODE across platforms and vendors. (Appendix B)

Static SQL. SQL statements embedded in a host-language program that are precompiled and bound into packages before execution. The access plan is determined at bind time. (Chapter 19)

Stored procedure. A named routine stored in the database that encapsulates SQL and procedural logic. Called with the CALL statement. (Chapter 21)

Sysplex. An IBM z/OS cluster of multiple systems sharing workload and data. DB2 data sharing operates within a Parallel Sysplex. (Chapter 18)

Table function. A user-defined function that returns a table (result set) rather than a scalar value. Used in the FROM clause. (Chapter 21)

Table space. A logical storage structure that contains one or more tables. On z/OS: segmented, partitioned, or universal. On LUW: DMS, SMS, or automatic storage. (Chapter 4)

Temporal table. A table that automatically maintains historical versions of rows using system-time or application-time periods. (Chapter 30)

Thread. On z/OS, a DB2 execution unit representing an application connection. Analogous to an application handle on LUW.

Throughput. The number of transactions or operations completed per unit of time.

Trigger. A database object that automatically executes SQL statements in response to INSERT, UPDATE, or DELETE operations on a table. (Chapter 21)

UDF (User-Defined Function). A function created by users to extend DB2's built-in function library. Can be scalar, table, or aggregate. (Chapter 21)

Uncommitted read (UR). The lowest isolation level. Reads do not acquire locks and may see uncommitted changes (dirty reads). (Chapter 7)

UNION. An SQL operator that combines the results of two SELECT statements, eliminating duplicates. UNION ALL preserves duplicates.

Unit of work (UOW). A sequence of operations that DB2 treats as a single logical transaction, bounded by COMMIT or ROLLBACK. Also called a transaction.

Universal table space (UTS). A z/OS table space type (introduced in DB2 9) that supports partition-by-growth and partition-by-range with a single, flexible architecture. The recommended type for new development. (Chapter 4)

View. A named query stored in the database. Does not store data; it is re-evaluated each time it is referenced. (Chapter 3)

Window function. A function that operates on a set of rows related to the current row, defined by an OVER clause with optional PARTITION BY and ORDER BY. Also called an OLAP function or analytic function. (Chapter 6)

Workload management (WLM). On z/OS, the MVS component that manages system resources across address spaces. DB2 integrates with WLM for stored procedure and UDF execution. On LUW, DB2's built-in workload management controls resource allocation by workload class. (Chapter 23)

XML. An extensible markup language data type supported natively in DB2 for storing and querying hierarchical data. DB2 supports XQuery and SQL/XML functions. (Chapter 31)

ZPARM (DSNZPARM). The z/OS DB2 subsystem parameter module containing system-wide configuration settings. (Appendix D)

Additional Terms

Actionable compression. A column-organized compression technique used in BLU Acceleration where data remains compressed during processing, reducing both I/O and CPU costs. (Chapter 27)

Address space. On z/OS, a range of virtual memory allocated to a program or subsystem. DB2 uses multiple address spaces: MSTR (master), DBM1 (database manager), DIST (distributed), and IRLM (lock manager).

Alias. An alternative name for a table, view, or sequence. On z/OS, aliases are used to provide location-independent names for objects accessed via DRDA.

Application plan. See Plan.

Automatic client reroute (ACR). An LUW feature that automatically reconnects client applications to an alternate server when the primary server fails. Works with HADR and pureScale. (Chapter 17)

Archive log. See the main listing under A above.

BIND. See the main listing under B above.

BSDS (Bootstrap Data Set). On z/OS, a VSAM data set that contains log inventory information and the DB2 checkpoint queue. Critical for recovery; always dual-copied. (Chapter 14)

Catalog partition. The partition of the DB2 catalog; on z/OS, this is DSNDB06.

CCSID (Coded Character Set Identifier). A numeric identifier for a character encoding scheme. z/OS DB2 uses CCSIDs to handle EBCDIC, ASCII, and Unicode data. (Chapter 26)

Column mask. An RCAC security object that controls what value is returned for a column based on the requesting user's role or authorization. (Chapter 22)

Commit scope. In distributed processing, determines whether commits are coordinated across multiple sites (two-phase commit) or handled locally. Controlled by CMTSTAT zparm on z/OS.

Connection handle. On LUW, a unique identifier for each application connection, used in monitoring and the FORCE APPLICATION command.

Correlation ID. On z/OS, a string that identifies a thread, typically derived from the plan name, connection ID, or CICS transaction ID. Useful for tracing specific application activity.

Data set. On z/OS, the fundamental unit of file storage, analogous to a file on Unix/Windows. DB2 table spaces and indexes are stored as VSAM linear data sets.

DB2 Governor (LUW). A deprecated tool that was used to control resource consumption by individual applications. Replaced by workload management (WLM) features in modern DB2 versions.

DBCLOB. Double-byte character large object. Used for storing large amounts of DBCS (double-byte character set) data.

DDF (Distributed Data Facility). The z/OS DB2 component that handles distributed (remote) SQL requests using the DRDA protocol. (Chapter 26)

Delta backup. A backup that captures pages changed since the last successful backup (full or delta). More granular than incremental backup but potentially slower to restore. (Chapter 15)

Dirty read. Reading uncommitted data from another transaction. Possible only at the UR (Uncommitted Read) isolation level. (Chapter 7)

DSN. The z/OS TSO command prefix for DB2 interactive sessions, and historically the product prefix for DB2 for z/OS subsystem components.

DSNTEP2. A z/OS batch program that executes dynamic SQL statements from SYSIN. A simple alternative to SPUFI for batch SQL execution.

DSNTIAD. A z/OS batch program for executing dynamic SQL with autocommit. Similar to DSNTEP2 but commits after each statement.

Dynamic statement cache. An in-memory cache of prepared dynamic SQL statements and their access plans. Avoids recompilation when the same SQL text is executed repeatedly. Controlled by CACHEDYN zparm on z/OS and PCKCACHESZ on LUW. (Chapter 12)

EDU (Engine Dispatchable Unit). See the main listing under E above.

EXPLAIN tables. A set of tables (EXPLAIN_INSTANCE, EXPLAIN_STATEMENT, EXPLAIN_OPERATOR, etc.) populated by the EXPLAIN facility with access plan details. (Chapter 12)

Extent size. The number of pages allocated to a table space or index in each extent. Larger extent sizes reduce the frequency of extent allocation but may waste space for small tables. (Chapter 4)

Fast insert. A z/OS optimization that buffers INSERT operations in memory, reducing log and I/O overhead for high-volume insert workloads.

FCM (Fast Communication Manager). An LUW component that handles inter-partition communication in a DPF (multi-partition) environment. (Chapter 28)

Frequency value. A catalog statistic recording how often the most common values appear in a column. Used by the optimizer for non-uniform distribution estimation. (Chapter 11)

Function path. The ordered list of schemas searched when resolving an unqualified function name. Set via the CURRENT PATH special register.

GET DIAGNOSTICS. An SQL statement that retrieves diagnostic information (SQLCODE, SQLSTATE, message text, row count) after executing a previous SQL statement. (Chapter 19)

Heuristic resolution. The process of manually resolving an indoubt transaction (from a failed two-phase commit) by forcing a COMMIT or ROLLBACK decision.

Incremental backup. A backup that captures pages changed since the last full backup. Faster than a full backup but requires the full backup plus all incrementals for a restore. (Chapter 15)

IRLM (Internal Resource Lock Manager). The z/OS component that manages locks for DB2. Runs in its own address space. (Chapter 7)

ISPF (Interactive System Productivity Facility). The z/OS menu-driven interface used to navigate TSO and access tools like SPUFI.

LIKE statistics. Multi-column statistics on LUW collected using the ON COLUMNS ((col1, col2) LIKE STATISTICS) syntax in RUNSTATS. Captures frequency and quantile statistics for the column group. (Chapter 11)

List prefetch. A prefetch technique where DB2 first builds a list of needed pages (from index access), sorts them by page number, and then reads them in order to minimize random I/O. (Chapter 9)

Lock avoidance. A z/OS optimization where DB2 determines that a row has been committed by inspecting the page's log RBA, avoiding the overhead of requesting a lock from the IRLM. (Chapter 7)

Lock suspension. On z/OS, a state where a thread is waiting to acquire a lock held by another thread. Visible in -DISPLAY THREAD output.

Nickname. In federated databases, a local identifier for a remote table or view. Queries against nicknames are transparently routed to the remote data source. (Chapter 29)

OLTP (Online Transaction Processing). A workload pattern characterized by high volumes of short, simple transactions (e.g., banking, e-commerce). DB2 is heavily optimized for OLTP on both platforms.

OMEGAMON. IBM's enterprise monitoring product for z/OS, providing real-time and historical DB2 performance data. The z/OS equivalent of db2top and MON_GET functions on LUW.

Partition-by-growth (PBG). A z/OS universal table space type where partitions are automatically added as data grows. Simplifies administration for unpredictable growth patterns. (Chapter 4)

Partition-by-range (PBR). A z/OS universal table space type where partitions are defined by key ranges, enabling partition-level operations (REORG, COPY, RECOVER). (Chapter 4)

Phantom read. A phenomenon where a transaction re-executes a query and finds new rows inserted by another committed transaction. Prevented by RR (Repeatable Read) isolation. (Chapter 7)

REBIND. On z/OS, the process of regenerating the access plan for a package, incorporating current statistics. On LUW, REBIND PACKAGE serves the same purpose. (Chapter 19)

Row permission. An RCAC security object that defines a predicate controlling which rows a user can access. Applied automatically and transparently to all SQL statements. (Chapter 22)

SECADM (Security Administrator). A database-level authority (LUW) responsible for security object management: creating roles, audit policies, RCAC objects, and managing trusted contexts. (Chapter 22)

Sequential prefetch. Prefetching contiguous pages ahead of the current read position, triggered when DB2 detects sequential access patterns. (Chapter 9)

SIMD (Single Instruction, Multiple Data). A CPU instruction set feature that processes multiple data elements in a single operation. Used by BLU Acceleration for vectorized query processing. (Chapter 27)

Skip-level insert. An optimization in partitioned table spaces where DB2 inserts into the correct partition based on the clustering key, even if it is not the "current" partition.

Snapshot monitoring. An older LUW monitoring framework (superseded by MON_GET functions) that captures point-in-time statistics using GET SNAPSHOT commands.

Sort pool. The memory area used for sorting operations (GROUP BY, ORDER BY, DISTINCT, merge join). On z/OS, controlled by the sort pool size parameter. On LUW, controlled by SORTHEAP. (Chapter 9)

Statement concentrator. An LUW feature that replaces literal values in SQL with parameter markers, increasing the reuse rate of cached dynamic statements. (Chapter 12)

Stogroup (Storage Group). On z/OS, a named set of DASD volumes used for DB2 data set allocation. On LUW, automatic storage groups serve a similar purpose.

System-period temporal table. A temporal table that automatically tracks row versions using system-generated timestamps. Enables "AS OF" time-travel queries. (Chapter 30)

Table access. The method DB2 uses to read data from a table: table scan (sequential read of all pages), index scan (navigate B+ tree to locate qualifying rows), or index-only access. (Chapter 12)

Tablespace scan (TBSCAN). An access method where DB2 reads every page of a table space to find qualifying rows. Efficient for non-selective queries on small tables or when buffer pool caching is effective. (Chapter 12)

TSO (Time Sharing Option). The z/OS interactive environment used to access ISPF, SPUFI, and other tools.

Trusted context. A database security object that defines a trust relationship based on connection attributes (system user, IP address, encryption level). Can grant additional authorities or suppress authentication when conditions are met. (Chapter 22)

Two-phase commit. A distributed transaction protocol that ensures all participating databases either commit or rollback a transaction. Coordinated by the transaction manager (DB2 or CICS on z/OS, the transaction manager on LUW). (Chapter 26)

VSAM (Virtual Storage Access Method). The z/OS file access method used for DB2 data sets. DB2 table spaces and indexes are stored as VSAM linear data sets (LDS).