Optimize your application performance with Partial Rebalances for Db2 data


I’m writing about partial rebalances in Db2 today because they are an effective tool to optimize your applications. Partitioned tablespaces are important because they allow parallel processing of different parts of the data, facilitating many users retrieving data at once. I like to think of it like a bank with many ATMs. Instead of standing in a single file line for a bank teller, you can go to any ATM and wait behind just one person – or if you’re lucky, drive right up! The ATMs are all accessing the same money, just more efficiently. Rebalancing partitioned tablespaces is one way to keep those partitioned tablespaces functioning properly and ensuring that you have top application performance.

Db2 applications built around partitioned tablespaces are optimized based on the data being relatively evenly distributed between partitions. This is done through significant database and application design work. All of the benefits of using a partitioned tablespace such as parallel access and increased data performance are maximized with even distribution in most applications. But, sometimes you have a case where data is skewed into just a small subset of partitions – for example, your application was designed long ago to be partitioned by account number. Imagine that your business decides to acquire another company – which can be pretty frequent these days – and that other company uses account numbers that all start with “4”. Suddenly, the range of partitions that takes on that data are much larger than all the others. In another everyday scenario, applications often cause data to be skewed to a small subset of partitions that can result in increased times to retrieve the data. This has an impact on application performance and end-user response.

When end-user response is slow, it can mean real dollars lost. If a bank issues a credit check and the response times out, they will try another vendor. If a shopper’s phone times out trying to make a purchase, they find another website that works. What if they don’t ever come back to yours? They may decide that your competitor’s website is sleeker, faster, more reliable. You have then lost a customer.

BMC’s NGT Reorg has a REBALANCE feature that redistributes the tablespace rows among partitions as evenly as possible, optimizing application access. This makes it easier for the application to access the data and become lightning fast. The REBALANCE feature was enhanced in January 2018 in response to clients around the world who requested a “partial rebalance” to smooth a subset of partitions since there are many applications that cause the data to grow in just three or four of the partitions you are managing (out of 100). The other 90+ do not require this treatment, or any maintenance, and doing it partially drives big benefits for the elapsed time and CPU time required to move the data. NGT Reorg reads the data in the subset of partitions, determines how many rows should be in each partition, calculates the new limit keys, moves the data to the correct partitions, and updates the limit keys. All with the built-in benefits of Next Generation Technology automation and the ability to intelligently restart.

In addition, now that BMC has adopted a continuous delivery model (rolling product delivery), we were able to respond to customer requests for this innovation and deliver this feature in record time. Five months from requirement to delivery. I’m excited about how this delivery model allows us to provide new features to our customers in a more flexible manner and look forward to the opportunities ahead.

To learn more about NGT High Speed Utilities, see our Data Management Solutions page.

These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

Share This Post

Anne Hoelscher

Anne Hoelscher

Anne Hoelscher, R&D Product Line Manager for Db2 Utilities at BMC, has 30 years’ experience in Db2 starting with Db2 v1.2. Anne has held various roles from Product Developer, QA Manager, and IBM Vendor Liaison, to Executive Vice President at CDB Software. Anne joined BMC through the CDB acquisition in 2015 and has led the Development group for Db2 ever since. Anne holds a BS in Mathematics from the University of Dallas.