Db2 for z/OS and its ecosystem

Db2 13 for z/OS improves insert performance for partition-by-growth (PBG) table spaces by introducing a new cross-partition search algorithm. 

PBG table spaces were designed to solve the 64G size limitation of segmented table spaces. Db2 manages PBG table spaces automatically as data grows, by adding new partitions whenever more space is needed. However, many customers now face performance side effects for PBG table spaces that were converted from legacy table space types years ago and have since grown too large.

Although the flexibility of growing table size based on demand offers advantages for scalability, it also presents challenges for maintaining sufficient performance when inserting or accessing data across multiple partitions, especially for applications that run in high concurrency and data sharing environments.

Retry logic for obtaining partition locks

One factor that affects PBG searches across multiple partitions is obtaining the required partition locks. The Db2 locking algorithm requires transactions to acquire partition locks before accessing any data within a partition. To increase the concurrency of transactions inserted into the PBG table space, the partition lock is acquired conditionally when a table space contains more than one partition.

If the conditional partition lock is acquired successfully, the partition is searched. Otherwise, the partition is skipped, and the next partition is evaluated. The same operation continues to the next available partition until the insert operation either successfully obtains a partition lock or it finishes searching all partitions. If no partition lock can be obtained, the insert application receives SQL return code -904 (resource not available) after all existing partitions are searched for available space.

To address this issue and increase the success rate of insert transactions, Db2 13 introduces new retry logic. Another attempt is made to obtain a partition lock conditionally after the first attempt fails. An insert transaction fails only after two unsuccessful attempts to obtain a partition lock.

Note that other conditions can impact the success rate. Applications can still receive SQLCODE -913 or -904, depending on the data distribution and locking contention during the retry process. Also, the performance impact from the retry process can vary depending on the number of partitions retried, the availability of space, and the effectiveness of space search within each partition.

In-memory caching of partition-full conditions

To avoid every concurrent thread searching through the same full partitions, Db2 13 caches the partition full condition in the memory. So, Db2 can skip obtaining conditional partition lock or even searching into the full partitions within the table space. This change is specifically beneficial for insert applications that use a random key access pattern, where a clustering index leads to the partition that does have room for the row to be inserted. 

Descending search order

Db2 13 now uses descending order for cross-partition searches, so the search starts with the original target partition and progresses down to the first partition. When this situation occurs, Db2 uses internal tracked non-empty partitions along with available real time statistics (RTS) information to determine the next best target partition to search. However, the internal tracking information is kept in memory and is updated when an insert traverses through existing partitions since the last physical open of the page set. Therefore, each data sharing member can have different tracking information based on its own workload activities. 

More in-memory data for run-time search algorithm tuning

Db2 13 also uses more in-memory data for the internal search algorithm tuning during runtime. The in-memory data is tracked at each data sharing member and not communicated through all data sharing members within the LPAR. Therefore, workload balance between data sharing members is important for taking advantage of this improvement.

What if problems persist?

Since applications use many different insert behaviors, performance side effects are likely to persist on some large PBG table spaces, even with the many improvements in Db2 13.

In these situations, you are likely to achieve even better performance—and simplified management—by converting problematic large PBG table spaces to partition-by-range (PBR) tables spaces based on limit key values. Stay tuned to this blog for an upcoming entry about the new PBG to PBR conversion capability in Db2 13.  

 For more information, see section 4.3 in IBM Rebooks: IBM Db2 13 for z/OS and More.