Introduction
Each new release of DB2 brings changes to the utilities suite, and new release DB2 11 is no exception. The importance of these changes can have a big impact on the work of a DBA. The changes and new options should be well understood and choices have to be made for your environment. The purpose of this article is not to provide a full and detailed list of those improvements, nor to provide full syntax. The idea is to introduce some of those features.
The REORG Utility
DB2 11 brings a few new features/behaviours for the REORG utility. Maybe the most eye-catching new feature with the new REORG utility is the “automated mapping table” feature. This means that, should this be used in your shop, DB2 will take care of the allocation and removal of the mapping table during a SHRLEVEL CHANGE REORGanization. This doesn’t mean that you cannot specify your own mapping table anymore; you now have the choice to let DB2 handle it. Should you choose to keep on working with your own mapping table, you should be aware of the new layout of the mapping table in DB2 11 due to the change in LRSN length. When a reference is made to an invalid mapping table (non-existing or wrong format) DB2 will allocate the necessary mapping table. After completing the REORG, DB2 will do all needed clean up and drop this created mapping table.
Another improvement in the REORG utility allows cleaning up empty partitions in partition by growth table spaces. Deleting empty partitions of PBG table spaces is a system wide choice and cannot be specified at a REORG statement. It requires a DSNZPARM change. Unless the DSNZPARM parameter REORG_DROP_PBG_PARTS is set to enable, DB2’s default won’t delete physical datasets after REORG, similar to previous versions of DB2.
A personal favourite of mine, is the improved drain and SWITCH –phase processing during an online REORG. It might not sound very impressive, but it will significantly reduce the SWITCH phase of partition level REORGs and thus improving availability. Starting in DB2 11, DB2 will no longer allow new claims on target partitions while preparing to switch. Should this not be enough, DB2 now allows a new option on the REORG statement that causes a momentary drain on all partitions prior to the SWITCH phase rather than partition by partition. The enhancement will make it easier for the REORG to break in. Finally, the SWITCH phase itself has been re-architected to cut the elapsed time significantly.
Many other performance features include a smarter secondary index rebuild and the possibility to REORG the data without re-clustering, along with new options to better control partition parallelism during LISTDEF processing.
For those users that are still using the INDREFLIMIT and the OFFPOSLIMIT keywords instead of real time statistics (RTS), now might be the time to start looking at RTS as many of the earlier conditional parameters will we deprecated in DB2 11.
The RUNSTATS Utility
Every new release of DB2 relies on more accurate statistics to make the best possible access path decisions. Gathering these more detailed statistics could, in some cases, be a relatively costly endeavour, especially if distribution statistics were needed. DB2 10 brought relief by making some RUNSTATS gathering ZIIP-eligible. DB2 11 brings further relief as the gathering of distribution statistics as well as inline statistics become ZIIP offload-able. These last (inline statistics) also become more powerful allowing more options and reducing the need to run separate statistics.
The most important improvement to the RUNSTATS utility however is the ability of the DB2 11 optimizer to indicate which statistics it is missing. This information can be used to modify the statistics profile for the table and hence automatically gather the statistics required by the optimizer.
A nice to have improvement is the ability to reset the existing statistics during a RUNSTATS utility, rather than manually trying to delete those statistics when they are no longer needed or accurate.
The LOAD Utility
In DB2 11 the LOAD utility can exploit parallelism where none was possible before due to a single input dataset or single target page set. For LOAD SHRLEVEL CHANGE, parallel insert processing can now be performed. For LOAD SHRLEVEL NONE, internal parallelism is exploited. Both have the potential to significantly reduce elapsed time.
Users that quite often use the very practical Cross loader functionality will be happy to learn that it now supports XML in addition to LOB columns.
Utility Related Commands
Although technically not utility improvements, some DB2 commands have been altered or added to provide us with more and better information.
One enhancement is to include the job name in the –DISPLAY UTILITY command output. Although not world shocking, it can be immensely practical.
Similarly practical is a command to externalize the real time statistics, rather than stopping and starting an object or running a RUNSTATS utility.
Conclusion
DB2 11 brings some practical and nice to have improvements as well as highly important availability and performance solutions. This article gave an overview of some of the new possibilities of the DB2 11 utility suite. Let it be a start to fully exploiting DB2 11 once installed.