Row-organized tables
Db2 databases use row-organized tables as the most common storage format for relational data. In row-organized tables, data is stored physically on disk with all column values for a single row kept together in contiguous storage blocks. The row-organized format aligns naturally with OLTP (Online Transaction Processing) operations, where queries typically target specific rows using primary key lookups or indexed searches, and where insert, update, and delete operations affect entire rows at once.
Row-based organization provides optimal performance for transactional workloads where applications frequently access complete rows of data, such as retrieving all customer information for a specific order or updating multiple attributes of a single employee record.
The physical layout of row-organized tables in Db2® follows a page-based architecture, where data pages contain multiple rows stored sequentially. Each page typically holds as many complete rows as will fit within the page size, which can be configured as 4KB, 8KB, 16KB, or 32KB depending on the table space definition. This organization enables efficient buffer pool management and I/O operations, as reading a single page brings multiple related rows into memory simultaneously. Row-organized tables support all standard Db2 features including referential integrity constraints, triggers, indexes, and materialized query tables, making them the versatile foundation for most database applications.
Table compression capabilities
Db2 provides sophisticated table compression capabilities for row-organized tables that can significantly reduce storage requirements and improve I/O performance by minimizing the amount of data transferred between disk and memory. Table compression in Db2 operates at the row level using a dictionary-based compression algorithm that identifies repeating patterns and values within the table data. When compression is enabled, Db2 builds a compression dictionary that stores frequently occurring data patterns, and then replaces these patterns in the actual data rows with shorter token references to the dictionary entries. This approach is particularly effective for tables that contain repetitive data, such as status codes, category values, or standardized text strings that appear across many rows.
The compression process in Db2 is transparent to applications, meaning that data is automatically compressed when written to disk and decompressed when read into memory for query processing.
For more information, see Table compression.
Table partitioning features
Table partitioning in Db2 enables large row-organized tables to be divided into smaller, more manageable pieces called data partitions, while still presenting a single logical table to applications and queries. Partitioning is implemented using range partitioning, where rows are distributed across partitions based on the values in one or more partitioning key columns. For example, a sales transaction table might be partitioned by transaction date, with each partition containing one month's worth of data, or a customer table might be partitioned by geographic region. Each partition is stored as a separate physical object with its own data pages and can be managed independently for maintenance operations such as backup, restore, reorganization, and data archiving.
The benefits of table partitioning are substantial for very large tables. Partition elimination is a key performance optimization where the Db2 query optimizer can automatically exclude irrelevant partitions from query execution based on predicates in the WHERE clause, dramatically reducing the amount of data that needs to be scanned. For instance, a query requesting sales data for a specific month would only access that month's partition rather than scanning the entire multi-year table.
Partitioning also enables roll-in and roll-out operations for data lifecycle management, where new partitions can be attached to add recent data and old partitions can be detached to archive historical data, all without requiring full table reorganizations or impacting concurrent access to other partitions. Maintenance operations like RUNSTATS, REORG, and BACKUP can be performed at the partition level, allowing for more granular and efficient database administration. Additionally, partitions can be placed in different table spaces, enabling storage tiering strategies where recent, frequently accessed data resides on faster storage devices while older, less active data is moved to more economical storage tiers.
For more information, see Table partitioning and data organization schemes.
Integration of compression and partitioning
Db2 allows table compression and partitioning to be used together on the same row-organized table, combining the storage efficiency benefits of compression with the manageability and performance advantages of partitioning. When both features are enabled, each partition can have its own compression dictionary optimized for the data patterns within that specific partition, which can be particularly effective when data characteristics vary significantly across partitions. For example, in a multi-year transaction table partitioned by date, older partitions might compress more effectively because they contain completed, stable data with more repetitive patterns, while newer partitions containing active, frequently changing data might show different compression characteristics.
The combination of these features provides a powerful framework for managing enterprise-scale databases, enabling organizations to handle tables with billions of rows while maintaining acceptable query performance, manageable storage costs, and efficient administrative operations. Database administrators can fine-tune compression and partitioning strategies based on specific workload patterns, data growth rates, and business requirements, making row-organized tables with these features suitable for a wide range of applications from high-volume OLTP systems to large-scale data warehousing environments.