such as bulk inserts you can improve performance with the db2empfa tool to tell DB2 to expand the table space in groups of pages or extents. The db2empfa tool is located in the bin subdirectory of the sqllib directory. Wher you run db2empfa, the multipage_alloc database configuration parameter is se to Yes for more information on this tool. refer to the command reference When all space in a single container in an SMs table space is allocated to tables, the table space is considered full, even if space remains in other containers. You can add containers to an Sms table space only on a partition that does not yet have any containers Related concepts Table space design"in the Administration Guide: Planning Comparison of SMS and DMS table spaces"in the Administration Guide Related task Adding a container to an SMs table space on a partition"in the dministration Guide: Implementation DMS table spaces With database-managed space(DMS)table spaces, the database manager controls the storage space. A list of devices or files is selected to belong to a table space when the DMS table space is defined. The space on those devices or files is managed by the DB2 database manager. As with SMS table spaces and containers, DMS table spaces and the database manager use striping by extent to ensure an even distribution of data across all containers DMS table spaces differ from SMs table spaces in that for DMs table spaces, space is allocated when the table space is created and not allocated when Also, placement of data can differ on the two types of table spaces. For example, consider the need for efficient table scans: it is important that the pages in an extent are physically contiguous With SMS, the file system of th operating system decides where each logical file page is physically placed The pages may, or may not, be allocated contiguously depending on the level of other activity on the file system and the algorithm used to determine placement. With DMS, however, the database manager can ensure the pages are physically contiguous because it interfaces with the disk directly. Note: Like SMS table spaces, DMS file containers can take advantage of le-system prefetching and caching. However, DMS table spaces cannot 20 Administration Guide: Performance
such as bulk inserts you can improve performance with the db2empfa tool to tell DB2® to expand the table space in groups of pages or extents. The db2empfa tool is located in the bin subdirectory of the sqllib directory. When you run db2empfa, the multipage_alloc database configuration parameter is set to Yes. For more information on this tool, refer to the Command Reference. When all space in a single container in an SMS table space is allocated to tables, the table space is considered full, even if space remains in other containers. You can add containers to an SMS table space only on a partition that does not yet have any containers. Related concepts: v “Table space design” in the Administration Guide: Planning v “Comparison of SMS and DMS table spaces” in the Administration Guide: Planning Related tasks: v “Adding a container to an SMS table space on a partition” in the Administration Guide: Implementation DMS table spaces With database-managed space (DMS) table spaces, the database manager controls the storage space. A list of devices or files is selected to belong to a table space when the DMS table space is defined. The space on those devices or files is managed by the DB2® database manager. As with SMS table spaces and containers, DMS table spaces and the database manager use striping by extent to ensure an even distribution of data across all containers. DMS table spaces differ from SMS table spaces in that for DMS table spaces, space is allocated when the table space is created and not allocated when needed. Also, placement of data can differ on the two types of table spaces. For example, consider the need for efficient table scans: it is important that the pages in an extent are physically contiguous. With SMS, the file system of the operating system decides where each logical file page is physically placed. The pages may, or may not, be allocated contiguously depending on the level of other activity on the file system and the algorithm used to determine placement. With DMS, however, the database manager can ensure the pages are physically contiguous because it interfaces with the disk directly. Note: Like SMS table spaces, DMS file containers can take advantage of file-system prefetching and caching. However, DMS table spaces cannot. 20 Administration Guide: Performance
There is one exception to this general statement regarding contiguous with DMS table spaces: raw devices and files. When working with file ork placement of pages in storage. There are two container options when working containers, the database manager allocates the entire container at table space creation time. A result of this initial allocation of the entire table space is that the physical allocation is typically, but not guaranteed to be, contiguous even though the file system is doing the allocation. When working with raw device containers, the database manager takes control of the entire device and always ensures the pages in an extent are contiguo Unlike SMS table spaces, the containers that make up a dMs table space do not need to be close to being equal in their capacity. However, it is recommended that the containers are equal, or close to being equal, in their capacity. Also, if any container is full, any available free space from other containers can be used in a DMs table space. When working with DMS table spaces, you should consider associating each container with a different disk. This allows for a larger table space capacity and the ability to take advantage of parallel I/O operations. The CREATE TABLESPACE statement creates a new table space within a database, assigns containers to the table space, and records the table space definition and attributes in the catalog. When you create a table space, the extent size is defined as a number of contiguous pages. The extent is the unit of space allocation within a table space. Only one table or other object, such an index, can use the pages in any single extent. All objects created in the table space are allocated extents in a logical table space address map. Extent allocation is managed through Space Map Pages(SMP) The first extent in the logical table space address map is a header for the table space containing internal control information. The second extent is the first extent of Space Map Pages(SMP) for the table space. SMP extents are spread at regular intervals throughout the table space. Each SMP extent is simply a bit map of the extents from the current SMP extent to the next SMP extent. The bit map is used to track which of the intermediate extents are in use The next extent following the SMP is the object table for the table space. The object table is an internal table that tracks which user objects exist in the table space and where their first Extent Map Page(EMP)extent is located. Each bject has its own EMPs which provide a map to each page of the object that is stored in the logical table space address map Related concepts Table space design"in the Administration Guide: Planning Chapter 2 Architecture and processes 21
There is one exception to this general statement regarding contiguous placement of pages in storage. There are two container options when working with DMS table spaces: raw devices and files. When working with file containers, the database manager allocates the entire container at table space creation time. A result of this initial allocation of the entire table space is that the physical allocation is typically, but not guaranteed to be, contiguous even though the file system is doing the allocation. When working with raw device containers, the database manager takes control of the entire device and always ensures the pages in an extent are contiguous. Unlike SMS table spaces, the containers that make up a DMS table space do not need to be close to being equal in their capacity. However, it is recommended that the containers are equal, or close to being equal, in their capacity. Also, if any container is full, any available free space from other containers can be used in a DMS table space. When working with DMS table spaces, you should consider associating each container with a different disk. This allows for a larger table space capacity and the ability to take advantage of parallel I/O operations. The CREATE TABLESPACE statement creates a new table space within a database, assigns containers to the table space, and records the table space definition and attributes in the catalog. When you create a table space, the extent size is defined as a number of contiguous pages. The extent is the unit of space allocation within a table space. Only one table or other object, such as an index, can use the pages in any single extent. All objects created in the table space are allocated extents in a logical table space address map. Extent allocation is managed through Space Map Pages (SMP). The first extent in the logical table space address map is a header for the table space containing internal control information. The second extent is the first extent of Space Map Pages (SMP) for the table space. SMP extents are spread at regular intervals throughout the table space. Each SMP extent is simply a bit map of the extents from the current SMP extent to the next SMP extent. The bit map is used to track which of the intermediate extents are in use. The next extent following the SMP is the object table for the table space. The object table is an internal table that tracks which user objects exist in the table space and where their first Extent Map Page (EMP) extent is located. Each object has its own EMPs which provide a map to each page of the object that is stored in the logical table space address map. Related concepts: v “Table space design” in the Administration Guide: Planning Chapter 2. Architecture and processes 21
Comparison of SMS and DMS table spaces"in the Administration Guide lanning DMS device considerations"on page 307 Database directories and files"on page 16 SMS table spaces"on page 19 "Illustration of the DMS table-space address map"on page 22 Related task Adding a container to a DMs table space" in the Administration Guide Implementation Related reference CREATE TABLESPACE statement"in the Sql Reference, volume 2 Illustration of the DMS table-space address map The following figure shows the logical address map for a DMS table space Table space(logical) address map able EMP First Extent of SMPs First Extent of Object Table Extent Map for T1 T1 to table space-relative First Extent of T1 Data Pages Indirect Entries econd Extent of T1 Data Pages Extent Map for T2 table space-relative page number First Extent of T2 Data Pages Double Indirect Entries Third Extent of T1 Data Pages Object ID for 31968 Second Extent of SMPs Figure 3. DMS table space The object table is an internal relational table that maps an object identifier to the location of the first EMP extent in the table. This EMP extent, directly or 22 Administration Guide: Performance
v “Comparison of SMS and DMS table spaces” in the Administration Guide: Planning v “DMS device considerations” on page 307 v “Database directories and files” on page 16 v “SMS table spaces” on page 19 v “Illustration of the DMS table-space address map” on page 22 Related tasks: v “Adding a container to a DMS table space” in the Administration Guide: Implementation Related reference: v “CREATE TABLESPACE statement” in the SQL Reference, Volume 2 Illustration of the DMS table-space address map The following figure shows the logical address map for a DMS table space. The object table is an internal relational table that maps an object identifier to the location of the first EMP extent in the table. This EMP extent, directly or 0 Header 1 2 3 16 20 32 4 5 6 7 8 31968 Object Table EMP T1 T2 12 24 Table space (logical) address map Indirect Entries Maps object-relative extent number within T2 to table space-relative page number Object ID for the table First EMP Reserved First Extent of SMPs First Extent of Object Table Extent Map for T1 First Extent of T1 Data Pages Second Extent of T1 Data Pages Extent Map for T2 First Extent of T2 Data Pages Third Extent of T1 Data Pages Second Extent of SMPs . . . . . . . . . . . . . . . . . . Maps object-relative extent number within T1 to table space-relative page number Double Indirect Entries Figure 3. DMS table spaces 22 Administration Guide: Performance
indirectly, maps out all extents in the object. Each EMP contains an array of entries. Each entry maps an object-relative extent number to a table space-relative page number where the object extent is located. Direct EMP The last EMP page in the first EMP extent contains indirect entries. Indirer t entries directly map object-relative addresses to table space-relative addres EMP entries map to EMP pages which then map to object pages. The last 16 entries in the last EMP page in the first EMP extent contain double-indirect entries The extents from the logical table-space address map are striped in ound-robin order across the containers associated with the table space Related concepts: DMS device considerations"on page 307 Disk-storage performance factors"on page 15 DMS table spaces"on page 20 Tables and indexes The following sections discuss management of both standard and MDC tables. Table and index management for standard tables DB2 provides two kinds of tables Standard tables, in which the highest logical structure under the table is the row Multi-dimensional clustering(MDC) tables, in which the highest logical structure under the table is the block, which is an extent-sized set of consecutive pages Understanding how tables and indexes are organized can help you understand how you might tune their use. This section de organization of standard tables and indexes Chapter 2. Architecture and processes 23
indirectly, maps out all extents in the object. Each EMP contains an array of entries. Each entry maps an object-relative extent number to a table space-relative page number where the object extent is located. Direct EMP entries directly map object-relative addresses to table space-relative addresses. The last EMP page in the first EMP extent contains indirect entries. Indirect EMP entries map to EMP pages which then map to object pages. The last 16 entries in the last EMP page in the first EMP extent contain double-indirect entries. The extents from the logical table-space address map are striped in round-robin order across the containers associated with the table space. Related concepts: v “DMS device considerations” on page 307 v “Disk-storage performance factors” on page 15 v “DMS table spaces” on page 20 Tables and indexes The following sections discuss management of both standard and MDC tables, and indexes on these tables. Table and index management for standard tables DB2® provides two kinds of tables: v Standard tables, in which the highest logical structure under the table is the row v Multi-dimensional clustering (MDC) tables, in which the highest logical structure under the table is the block, which is an extent-sized set of consecutive pages Understanding how tables and indexes are organized can help you understand how you might tune their use. This section describes the logical organization of standard tables and indexes. Chapter 2. Architecture and processes 23
Logical Physic Logical view of index table view table view KIRID 4022 ARID SRID 4023 RID (record ID)= Page 4023, Slot 2 Data page format ge Header 38001|3400 876 Legend reserved for system records user records Figure 4. Logical table, record, and index structure for standard tables Logically, table data is organized as a list of data pages. These data pages are logically grouped together based on the extent size of the table space. For exte iple, if the extent size is four, pages zero to three are part of the first tent, pages four to seven are part of the second extent, and so The number of records contained within each data page can vary based on the size of the data page and the size of the records. A maximum of 255 records can fit on one page. Most pages contain only user records. However, a small number of pages include special internal records, that are used by DB2 to manage the table. For example, in a standard table there is a Free Space 24 Administration Guide: Performance
Logically, table data is organized as a list of data pages. These data pages are logically grouped together based on the extent size of the table space. For example, if the extent size is four, pages zero to three are part of the first extent, pages four to seven are part of the second extent, and so on. The number of records contained within each data page can vary based on the size of the data page and the size of the records. A maximum of 255 records can fit on one page. Most pages contain only user records. However, a small number of pages include special internal records, that are used by DB2 to manage the table. For example, in a standard table there is a Free Space Logical Logical index view of table view Physical table view ... ... ... ... 0 4020 4021 4022 4023 252 1 2 3 4 876 500 ... ... Data page format Page Header 3800 -1 3400 Record 2 Record 1 Legend user records reserved for system records FSCR A C K S K RID K RID 4023,2 C RID RID RID RID ... RID (record ID) = Page 4023, Slot 2 Figure 4. Logical table, record, and index structure for standard tables 24 Administration Guide: Performance