Database reorganization has always been about improving database performance and reclaiming free space. However, as Rocket Software software engineer Ka-Chun Ng explained in a recent SHARE presentation, the new capabilities of database management software have changed the “how” and “why” when it comes to reorganization strategy.
Performance is still the big-picture goal, said Ng, but new technologies empower administrators to manage their databases in a more strategic and nuanced way, which improves the efficiency of database management and enables benefits like high availability.
To put this all into context, Ng painted a picture of the way weekly database reorganizations are typically run: administrators would schedule a reorg to happen over the weekend, ensuring that when business picked back up on Monday morning, application performance would be in peak shape. Free space would be reclaimed, deleted and pseudo-deleted records would be removed, and other functional improvements to the database would be completed.
Over the past decade or so, advancements in database management technology have meant that administrators need not stick to this routine. Ng points to the release of IBM Db2 version 9 and version 10 for z/OS, which introduced capabilities that essentially allow administrators to avoid doing the heavy lifting themselves.
“Basically, the message is that they want to better handle [organization] in the engine itself,” said Ng, citing features like the index leaf page list prefetch and the asymmetrical index leaf page split as examples of tools that help administrators balance workloads and maintain satisfactory performance. When it comes to addressing holes in an index or tablespace, such as deleted index keys or expired XML documents, Ng added that “Db2 has a built-in system agent to go through and clean up some of those holes to free up space.”
Ultimately, the need for routine weekend maintenance is significantly reduced, said Ng. Still, many administrators maintain the practice. “The majority of us are still running reorgs routinely because, for better or worse, we don’t know if it’s necessary,” he said. That might be perfectly fine for a small tablespace, but things become far less efficient the larger the project. “When you have large objects, and tablespaces with thousands of partitions, and terabytes of data, you can’t just run those and expect them to finish without some kind of tuning.”
Fortunately, technology is helping administrators make smarter decisions on when they should run a reorg. It’s not just tools like IBM Db2 that are getting smarter, though it has made significant strides since version 9. Improvements in hardware, including the presence of SSD storage and technologies like FlashCopy, help to reduce complexity, reorganization frequency and over-processing costs, said Ng.
He also mentioned shadow technology, which allows administrators to remap data in a shadow tablespace before applying changes to the original tablespace. Doing this allows administrators to think differently about how and why they might want to reorganize their database – if, indeed, a reorg is the best approach for their current situation.
“Reorg has become a really powerful tool to remap your data into different containers,” Ng said. “You can transfer the data as you move it, or change the shape of your container. It’s a powerful utility to materialize schema changes.”
Ultimately, technology is moving database management toward a future where planned, strategic reorganizations are set up to rebalance data in order to improve performance, availability, and resource consumption. Administrators will be able to deliver better value to their companies through this type of strategic work, and Ng said he believes their role will become even more valued as hardware and software capabilities continue to improve.
Watch Ka-Chun Ng’s full presentation “Db2 for zOS REORG utility – Past, Present, and Future” to get the full story on the promise and potential for database management.