Member affinity clustering

For applications that do heavy sequential insert processing from multiple members, the contention on the space map or for the data pages at the end of the table can be considerable.

The MEMBER CLUSTER option of CREATE TABLESPACE causes Db2 to manage space for inserts on a member-by-member basis instead of by using one centralized space map. Table spaces defined with MEMBER CLUSTER have the following characteristics:
  • Data that is inserted by the SQL INSERT statement is not clustered by the implicit clustering index (the first index) or the explicit clustering index.
  • Db2 chooses where to locate the data in such a way that avoids lock and latch contention. In general, it tries to insert data in a place that is covered by the locally cached space map page. If it cannot find space there, it continues to search through space map pages until it can find a place for which the space map page is available. As a result, space in a data set might not be fully used. However, when the data set reaches the maximum number of extents, lock contention might increase, and Db2 does use the entire space.
  • Start of changeFor a UTS table space, the space map page has 10 segments, regardless of the page size. The number of data pages that are allocated to each Db2 member varies, depending on SEGSIZE. A large SEGSIZE value causes more data pages to be covered by the space map page, which in turn causes the table space to grow in a multiple-member data sharing environment. If SEGSIZE is small, more space map pages are required.

    For a partitioned (non-UTS) table space, each space map covers 199 data pages. Because there are more space map pages and some might be partially used, table spaces that are defined with MEMBER CLUSTER can use more disk space.

    End of change
  • To reduce the overhead of reacquiring page P-locks, a page P-lock is held longer for MEMBER CLUSTER table spaces.

The downside to using MEMBER CLUSTER is that data is not inserted in clustering order. If you have a query application that performs best when data is in clustering order, you should run the REORG utility on the table space before starting the query application.