Availability, Resiliency, and Scalability

Last time, we looked at Application Support features for Db2v13. Today, let’s have a look at Availability, Resiliency, and Scalability and Performance.

It is also common for each new version of Db2 to deliver functionality that improves availability, resiliency, and scalability – all of which are core requirements of efficient database and application processing. Reducing outages and delivering continuous, seamless 24/7 functionality is the holy grail for all DBMSes, and Db2 13 offers some nice features to help achieve this goal. 

One of the most anticipated capabilities delivered by Db2 13 is the ability to convert tablespaces from partition-by-growth (PBG) to partition-by-range (PBR) without taking an outage. Until Db2 13 there has been no easy method of achieving this. Universal tablespaces were introduced way back in Db2 9 for z/OS, so over time, as data volume and requirements change, many organizations find themselves with tablespaces originally defined as PBG that would be easier to manage as PBR. Db2 13 introduced the ALTER PARTITIONING TO PARTITION BY clause that makes it simple to convert a table’s partitioning scheme. For example:

ALTER TABLE CSM.TAB01 ALTER PARTITIONING TO
PARTITION BY RANGE (ACCTNO) 
( PARTITION 1 ENDING AT (99), 
  PARTITION 2 ENDING AT (199), 
  PARTITION 3 ENDING AT (299), 
  PARTITION 4 ENDING AT (MAXVALUE));

Issuing such an ALTER to change from PBG to PBR results in a pending change that requires a REORG to materialize. And the REORG can be performed online, meaning that no outage is required to make the change.

There are many other availability and scalability improvements in Db2 13 including: the afore-mentioned doubling of DSMAX, the ability to delete an active log data set from the bootstrap data set (BSDS) while Db2 is running, improvements to Real Time Stats maximum values (to support larger workloads), increased size of Db2 Directory tablespaces, and Extended Common Service Area (ECSA) constraint relief. 

Better utilization of above-the-bar storage greatly improves the resiliency of Db2 13. Moving things like storage for Accounting and Statistics traces from constrained storage areas allows for more seamless Db2 operation because fewer insufficient storage conditions will be reached.

IBM is also on the path to delivering statement level package invalidation instead of invalidating an entire package whenever a change is made that currently causes an entire package to be invalidated. This is something that is not immediately available in Db2 13, but the architecture is being changed and functionality will be delivered over time. You can see some of the initial preparations to support this capability with new catalog tables (SYSPACKSTMTCOPY and SYSPACKSTMTDEP) as well as a new column (VALID) in SYSPACKSTMT. Although this capability is not yet completely “there” yet, it is exciting to think about a more available future where changes that invalidate packages no longer invalidate the entire package, only the statements impacted by the change. This is one of the bigger challenges for DBAs attempting to manage continuous availability for their Db2 environment.

Performance 

Of course, performance improvements are one of the first things that most DBAs look for in a new version of Db2. And Db2 13 offers up some nice, new performance gains.

According to the IBM Db2 13 for z/OS Performance Topics redbook, the high-level performance expectation of Db2 13 is equivalent to or better than Db2 12 for equivalent workloads. Of course, individual results will vary and this statement should be viewed only as a very high-level guideline. The redbook goes on to indicate that (everything being equivalent from V12 to V13) you should expect OLTP workloads to be equivalent or up to a 5% CPU reduction. For a relatively complex set of queries performance should be roughly equivalent. For UPDATE intensive batch processing you can expect performance to vary from equivalent to up to a 10% CPU reduction. And your IBM Db2 utility workloads should vary from equivalent to up to a 60% CPU reduction for online REORG.

Let’s take a look at a few of the performance improvements, starting with Fast Traversal Blocks (FTBs), which are improved in Db2 13. FTBs are used by Db2 to keep portions of indexes in memory for more rapid retrieval, which can significantly improve query performance. Initially in Db2 12, FTBs were only supported for unique indexes with a key no longer than 64 bytes. Db2 12 FL508 eliminated some restrictions, but Db2 13 delivers even greater functionality. Now, Db2 13 supports both unique and non-unique indexes; the key length can be up to 128 bytes for unique indexes and up to 120 bytes for non-unique.

And when it comes to better index performance in Db2 13, do not forget about the index lookaside improvement discussed earlier in the AI section!

Additional performance improvements delivered in Db2 13 include:

• INSERT enhancements for partition-by-growth tablespaces
• SORTL improvements, as mentioned earlier in the IBM zSynergy discussion
• CPU reduction with PBR relative page number (RPN) tablespaces in a Data Sharing environment 
• Improved index reorganization
• Group buffer pool residency time enhancements

Next time, we’ll look at how Db2v13 improves on Security and Simplification and Serviceability.

Previously:

• Db2 13 for z/OS Function Levels
• AI and Db2
• Application Support