There is a correction in DB2 9 and DB2 10 to how DB2 automatically converts from index based partitioning to table based partitioning using the system parameter IX_TB_PART_CONV_EXCLUDE (introduced by APAR PM45829, see below). The system parameter controls whether all columns are used during the conversion or only significant columns are used. The cover contains a nice explanation that includes examples……
The IX_TB_PART_CONV_EXCLUDE keyword on the DSN6SPRM macro is introduced by this APAR. This keyword is also online changeable via the DB2 –SET SYSPARM command. Valid values for this system parameter are YES and NO, with the default set to NO.
Taking the default or setting IX_TB_PART_CONV_EXCLUDE to NO maintains the previous automatic conversion process. Setting it to YES enables the new conversion process that only uses significant columns.
The APAR cover states that this keyword will soon be documented in the DB2 10 Installation and Migration Guide (GC19-2974). Watch here for notification of when the product documentation get updated.
The following APAR is marked as a HIPER so you should consider adding this APAR to your list of critical APARs that need to be dealt with.
APAR PM45829 closed way back on October 24, 2011 and applies to both DB2 9 and DB2 10. The PTFs that supersede this APAR are UK73183 (DB2 10) and UK73184 (DB2 9). The PTFs are available now.
To enable the functionality delivered by this APAR, you do need to add this keyword to your installation job DSNTIJUZ, assemble it, and create a new load module. As mentioned earlier, you can complete the activation using the DB2 command –SET SYSPARM. You should also consider updating your DSNTIDxx member for future DB2 upgrades.
If you have modified the DB2 installation CLIST, you may also have to go back and redo those modification because this APAR does update the CLIST member DSNTINST.
But we’re not done yet. With this APAR you get a bonus fix.
APAR PM45829 delivers a second fix associated with IN list processing. Back in July, I covered an APAR HIPER that delivered an additional DSNZPARM keyword to help manage INLIST processing, INLIST_PTC. That keyword allowed YES, to turn it on or enable in the optimizer enhancement, and NO (the default) to disable the optimization change. It turns out that somehow YES is always ignored and no matter what you code, you always get the equivalent of specifying NO. That translates to the new functionality is always disabled. APAR PM45829 corrects that small issue thus allowing the use of this optimization enhancement.
To read more about the INLIST_PTC keyword, check out my blog entry from last July 8.
Careful, don’t ignore the second part of this APAR. Even if you are not concerned with how the automatic conversion is taking place, you may still be interested in the optimization improvement for INLIST processing.