Control Record(FSCr)on every 500th data page. These records map the free space for new records on each of the following 500 data pages(until the next SCR). This available free space is used when inserting records into the table. Logically, index pages are organized as a B-tree which can efficiently locate records in the table data that have a given key value. The number of entities n an index page is not fixed but depends on the size of the key. For tables in DMS table spaces, record identifiers(RIDs) in the index pages use table space-relative page numbers, not object-relative page numbers. This allows an index scan to directly access the data pages without requiring an Extent Map page(EMP) for mapping Each data page has the following format: A page header begins each data page. After the page header there is a slot directory. Each entry in the slot directory corresponds to a different record on the page. The entry itself is the Entries of minus (-1)correspond to deleted records Record identifiers and pages Record identifiers(RIDs) are a three-byte page number followed by a one-byte slot number. Type-2 index records also contain an additional byte called the ridFlag. The ridFlag stores information about the status of keys in the index, such as whether this key has been marked deleted. Once the index is used to identify a RID, the Rid is used to the correct data page and slot number on that page. The contents of the slot is the byte-offset within the page to the beginning of the record being sought. Once a record is assigned a RID, it does not change until a table reorganization Chapter 2 Architecture and processes 25
Control Record (FSCR) on every 500th data page. These records map the free space for new records on each of the following 500 data pages (until the next FSCR). This available free space is used when inserting records into the table. Logically, index pages are organized as a B-tree which can efficiently locate records in the table data that have a given key value. The number of entities on an index page is not fixed but depends on the size of the key. For tables in DMS table spaces, record identifiers (RIDs) in the index pages use table space-relative page numbers, not object-relative page numbers. This allows an index scan to directly access the data pages without requiring an Extent Map page (EMP) for mapping. Each data page has the following format: A page header begins each data page. After the page header there is a slot directory. Each entry in the slot directory corresponds to a different record on the page. The entry itself is the byte-offset into the data page where the record begins. Entries of minus one (-1) correspond to deleted records. Record identifiers and pages Record identifiers (RIDs) are a three-byte page number followed by a one-byte slot number. Type-2 index records also contain an additional byte called the ridFlag. The ridFlag stores information about the status of keys in the index, such as whether this key has been marked deleted. Once the index is used to identify a RID, the RID is used to get to the correct data page and slot number on that page. The contents of the slot is the byte-offset within the page to the beginning of the record being sought. Once a record is assigned a RID, it does not change until a table reorganization. Chapter 2. Architecture and processes 25
Data page and RID format slot 3 bytes 1 byte Page 473 Page Header Supported page sizes 3800-13400 6KB (usable without page et on table space creation. ecord 2 Each table space must be Embedded free space >Record 1 a matching page size Exception: Any space reserved by an uncommitted DELETE is not usable Figure 5. Data page and record-id(RID) format When a table page is reorganized, embedded free space that is left on the page after a record is physically deleted is converted to usable free space RIDs are redefined based on movement of records on a data page to take advantage of the usable free space DB2 supports different page sizes. Use larger page sizes for workloads that tend to access rows sequentially. For example, sequential access is used for Decision Support applications or where temporary tables are extensively usec Use smaller page sizes for workloads that tend to be more random in their access. For example, random access is used in OLTP environments ndex management in standard tables DB2 indexes use an optimized B-tree implementation based on an efficient and high concurrency index management method using write-ahead logging The optimized B-tree implementation has bi-directional pointers on the leaf pages that allows a single index to support scans in either forward or reverse direction. Index page are usually split in half except at the high-key page where a 90/10 split is used. That is, the high ten percent of the index keys are placed on a new page. This type of index page split is useful for workloads where INSERT requests are often completed with new high-keys. type-1 indexes to type-2. The index type determines how deleted keys are v If you migrate from previous versions of DB2, both type-l and type-2 indexe are in use until you reorganize indexes or perform other actions that con physically removed from the index pages. 26 Administration Guide: Performance
When a table page is reorganized, embedded free space that is left on the page after a record is physically deleted is converted to usable free space. RIDs are redefined based on movement of records on a data page to take advantage of the usable free space. DB2 supports different page sizes. Use larger page sizes for workloads that tend to access rows sequentially. For example, sequential access is used for Decision Support applications or where temporary tables are extensively used. Use smaller page sizes for workloads that tend to be more random in their access. For example, random access is used in OLTP environments. Index management in standard tables DB2 indexes use an optimized B-tree implementation based on an efficient and high concurrency index management method using write-ahead logging. The optimized B-tree implementation has bi-directional pointers on the leaf pages that allows a single index to support scans in either forward or reverse direction. Index page are usually split in half except at the high-key page where a 90/10 split is used. That is, the high ten percent of the index keys are placed on a new page. This type of index page split is useful for workloads where INSERT requests are often completed with new high-keys. If you migrate from previous versions of DB2, both type-1 and type-2 indexes are in use until you reorganize indexes or perform other actions that convert type-1 indexes to type-2. The index type determines how deleted keys are physically removed from the index pages. Data page and RID format Page 473 Page Header 3800 3400 -1 Record 2 Record 1 473 Page # 0 3 bytes 1 byte slot # RID Free space (usable without page reorganization *) Embedded free space (usable after online page reorganization*) * Exception: Any space reserved by an uncommitted DELETE is not usable. Supported page sizes: 4KB, 8KB, 16KB, 32KB Set on table space creation. Each table space must be assigned a buffer pool with a matching page size. Figure 5. Data page and record-id (RID) format 26 Administration Guide: Performance
For type-1 indexes, keys are removed from the index pages during key deletion and index pages are freed when the last index key on the page is For type-2 indexes, index keys are removed from the page during key deletion only if there is an X lock on the table. If keys cannot be removed immediately, they are marked deleted and physically removed later. For more information, refer to the section that describes type-2 indexes. If you have enabled online index defragmentation by setting the MINPCTUSED clause to a value greater than zero when you create the index, index leaf pages can be merged online. The value that you specify is the threshold for the minimum percentage of space used on the index leaf pages. to merge the remaining keys with those of a neighboring page. If there is After a key is removed from an index page, if the percentage of space used the page is at or below the value given, then the database manager attemp sufficient room, the merge is performed and an index leaf page is deleted. Online index defragmentation can improve space reuse, but if the MINPCTUSED value is too high then the time taken to attempt a merge ncreases but becomes less likely to succeed. The recommended value for this clause is fifty percent or less Note: Because online defragmentation occurs only when keys are remove from an index page, in a type-2 index it does not occur if keys are nerely marked deleted, but have not been physically removed from the page. he include clause of the create indeX statement allows the inclusion of a specified column or columns on the index leaf pages in addition to the key columns. This can increase the number of queries that are eligible for index-only access. However, this can also increase the index space requirements and, possibly, index maintenance costs if the included columns are updated frequently. The maintenance cost of updating include columns is less than that of updating key columns, but more than that of updating columns that do not appear in the index. Ordering the index B-tree done using the key columns and not the included columns. Space requirements for database objects"in the Administration Guide Planning Considerations when choosing table spaces for your tables"in the Administration Guide: Planning Considerations when choosing MDC table dimensions"in the Administration Guide: Planning Table and index management for MDC tables"on page 28 Chapter 2 Architecture and processes 27
v For type-1 indexes, keys are removed from the index pages during key deletion and index pages are freed when the last index key on the page is removed. v For type-2 indexes, index keys are removed from the page during key deletion only if there is an X lock on the table. If keys cannot be removed immediately, they are marked deleted and physically removed later. For more information, refer to the section that describes type-2 indexes. If you have enabled online index defragmentation by setting the MINPCTUSED clause to a value greater than zero when you create the index, index leaf pages can be merged online. The value that you specify is the threshold for the minimum percentage of space used on the index leaf pages. After a key is removed from an index page, if the percentage of space used on the page is at or below the value given, then the database manager attempts to merge the remaining keys with those of a neighboring page. If there is sufficient room, the merge is performed and an index leaf page is deleted. Online index defragmentation can improve space reuse, but if the MINPCTUSED value is too high then the time taken to attempt a merge increases but becomes less likely to succeed. The recommended value for this clause is fifty percent or less. Note: Because online defragmentation occurs only when keys are removed from an index page, in a type-2 index it does not occur if keys are merely marked deleted, but have not been physically removed from the page. The INCLUDE clause of the CREATE INDEX statement allows the inclusion of a specified column or columns on the index leaf pages in addition to the key columns. This can increase the number of queries that are eligible for index-only access. However, this can also increase the index space requirements and, possibly, index maintenance costs if the included columns are updated frequently. The maintenance cost of updating include columns is less than that of updating key columns, but more than that of updating columns that do not appear in the index. Ordering the index B-tree is only done using the key columns and not the included columns. Related concepts: v “Space requirements for database objects” in the Administration Guide: Planning v “Considerations when choosing table spaces for your tables” in the Administration Guide: Planning v “Considerations when choosing MDC table dimensions” in the Administration Guide: Planning v “Table and index management for MDC tables” on page 28 Chapter 2. Architecture and processes 27
Considerations when creating MDC tables"in the Administration Guide Index cleanup and maintenance"on page 302 Insert process on page Table and index management for MDc tables Table and index organization for multi-dimensional clustering(MDC) tables is based on the logical structures as standard table organization. Like standard tables, MDC tables are organized into pages that contain rows of data, divided into columns, and the rows on each page are identified by row IDs(RIDs). In addition, however, the pages of MdC tables are grouped into extent-sized blocks. For example, in the illustration below, which shows a de wi ze of four, the first four pages, numbered 0 through 3, are the sest d block th hae tab lehe next set of pages, numbered 4 through 7 28 Administration Guide: Performance
v “Considerations when creating MDC tables” in the Administration Guide: Planning v “Index cleanup and maintenance” on page 302 v “Insert process” on page 34 Table and index management for MDC tables Table and index organization for multi-dimensional clustering (MDC) tables is based on the same logical structures as standard table organization. Like standard tables, MDC tables are organized into pages that contain rows of data, divided into columns, and the rows on each page are identified by row IDs (RIDs). In addition, however, the pages of MDC tables are grouped into extent-sized blocks. For example, in the illustration below, which shows a table with an extent size of four, the first four pages, numbered 0 through 3, are the first block in the table. The next set of pages, numbered 4 through 7, are the second block in the table. 28 Administration Guide: Performance
Logical gical view of table view dimension block index 区B 4021 block 0 E520 tt」 BID (block ld)= Page 252, slot 0 (first physical page of block, slot always O view of bl first 3 blocks block 1 Legend block 2 reserved for system records x reserved Figure 6. Logical table, record, and index structure for MDC tables The first block contains special internal records that are used by DB2 te nanage the table including the free-space control record(FSCr). In subsequent blocks, the first page contains the FSCR. An FSCR maps the free Chapter 2. Architecture and processes 29
The first block contains special internal records that are used by DB2® to manage the table, including the free-space control record (FSCR). In subsequent blocks, the first page contains the FSCR. An FSCR maps the free Legend user records X reserved U F in use free Logical view of block map for first 3 blocks Logical index view of dimension block reserved for system records FSCR A C K S K X 0 U 1 F 2 ... BID K BID 252,0 C BID BID BID BID Logical table view Physical table view ... 0 4020 4021 4022 4023 1 2 3 4 252 253 254 255 5 6 7 1488 1489 1490 1491 8 9 10 11 block 0 block 2 block 1 BID (block Id) = Page 252, slot 0 (first physical page of block, slot always 0) Figure 6. Logical table, record, and index structure for MDC tables Chapter 2. Architecture and processes 29