Db2 for z/OS and its ecosystem

 View Only

Insert Algorithm 2 update for Db2 13 for z/OS

By Frances Villafuerte posted 21 days ago

  

By  Frances Villafuerte, Shirley Zhou, and Paul McWilliams.

Insert throughput and efficiency are among the most critical attributes of OLTP applications in high demand-business environments, and insert algorithm 2 (IAG2)  was introduced in Db2 12 to improve the performance of concurrent insert applications. IAG2 is effective for reducing the page-level contentions from tables in concurrent inserts within a Db2 subsystem. However, due to quality concerns, a red alert was issued in July 2020, which recommended the disablement of IAG2 until further notice. 

The quality concerns were related to an automatic switch from IAG2 to the original insert algorithm (IAG1) for a given table space when various situations prevent IAG2 from being used. Since then, the Db2 development team has delivered a set of APARs to address any reported issues associated with IAG2. For a list of these APARs, see the IBM.DB2.IAG2 fix category.

In addition to the functional resolutions, the feature has been enhanced based on the feedback from early adaptors, especially with APAR PH62108 (which is available in Db2 13). This APAR adds the significant improvements by reducing the situations were Db2 switches from IAG2 to IAG1 and adding the new capability to switch to IAG1 only for a specific insert process, instead of disabling IAG2 for the entire object. With this enhancements, the subsequent insert transactions in the same member can resume using IAG2 immediately and it reduces the impact of IAG2 disablement.   

Recommendations

The general recommendation is to set the DEFAULT_INSERT_ALGORITHM subsystem parameter to 1, so that Db2 continues to use the original (IAG1) insert algorithm by default for most objects. However, with the latest maintenance listed under the IBM.DB2.IAG2 fix category applied, you also can now enable IAG2 at object level for specific as appropriate for specific database objects.

For more information about the befits of IAG2 and when to use it, see Insert algorithm 2 in the Db2 product documentation.


#Db2forz/OS
0 comments
23 views

Permalink