By Kalpana Shyam with Eric Radzinski.
Insert algorithm 2 (IAG2) was introduced in Db2 12 to provide significantly faster throughput for unclustered INSERTs. It is available only for universal table space objects that use the MEMBER CLUSTER option. If you're not already familiar with IAG2, be sure to read the About Insert Algorithm 2 section below to understand how and where it can be applied.
APAR PH02052 improves IAG2 usability by allowing Db2 to re-enable IAG2 automatically whenever possible. This is a significant enhancement because prior to PH02052, when IAG2 became disabled due to a temporary condition, you needed to manually stop and restart the table space object in order to re-enable the insert algorithm, even when the situation that caused the failure had been resolved. PH02052 eliminates the disruption and overhead associated with manually stopping and restarting the table space object by attempting to re-enable IAG2 automatically at pre-defined intervals.
Some of the reasons that IAG2 becomes disabled might require manual intervention. For example, you might need to manually issue ALTER TABLESPACE DSSIZE to resolve an out-of-space condition or you might need to manually resolve a lock escalation. Other reasons are typically resolved automatically as the workload progresses, for example, when real storage balloons and then shrinks or when a transaction that requested an SQL LOCK TABLE or initiated a lock escalation releases the locks or ends. In these situations, IAG2 will be re-enabled automatically when the condition is resolved. Issues that can't be diagnosed by evaluating the DSNI055I message or through other standard troubleshooting methods will need to be reported to IBM Support.
APAR PH02052 also includes code that alleviates problems that occur when both insert algorithm 1 (IAG1) and IAG2 are running concurrently. When IAG2 becomes disabled, it's possible for some threads to be using IAG1 and some threads to be using IAG2 in the same member or in different members at the same time. This APAR prevents data page contention between concurrent IAG1 and IAG2 transactions and also prevents ABENDs such as DSNIBHUN:500C and DSNKINSL:5033 when IAG1 and IAG2 run concurrently on the same table space.
And PH02052 provides some usability and serviceability enhancements in the form of:
- Updates to message DSNI055I, which now provides more detailed explanations of the situations that can cause IAG2 to become disabled, as well as more details about potential actions that you can take in situations that might require user intervention
- A new message, DSNI087I, which lets you know when IAG2 has been automatically re-enabled after a failure
- Two new IFCID 2 system level counters that can help you optimize IAG2 for your environment
- Enhanced diagnostic log records to indicate the successful re-enablement of IAG2
- Non-critical space map updates under s-latch to improve performance
About Insert Algorithm 2
If you're not already familiar with IAG2, it becomes available with Db2 12 when activate either function level 500 or 501 (you must activate one of these function levels before you can activate 502 or higher). At this boundary, IAG2 becomes the default for universal table spaces that are defined with the MEMBER CLUSTER attribute (you can reverse this default behavior through the DEFAULT_INSERT_ALGORITHM subsystem parameter).
As mentioned earlier, it's used only with universal table spaces that use the MEMBER CLUSTER option. It provides the potential to significantly increase throughput of unclustered INSERT statements, reduce logging due to batch formatting of data pages, and reduce class 2 elapsed time and class 2 CPU time due to space search optimization. INSERT throughput improvements are likely to be seen in situations where both of the following conditions are true:
- There is heavy concurrent INSERT activity from many concurrent threads. IAG2 won't improve INSERT throughput where there is no concurrency.
- The primary constraints on INSERT performance are space search and false leads on the data pages in the table space. In situations where the primary constraint is something other than space search and false leads (for example, index maintenance), IAG2 won't improve INSERT throughput.
Because each thread is pre-assigned a different data page to insert data into, the delays associated with data page and space map contention are reduced.
Note that IAG2 is not intended to be a replacement for IAG1. In situations where a small number of batch jobs insert multiple rows serially, IAG2 doesn't provide a significant performance advantage because there is little space contention; therefore, pre-assigning data pages provides little benefit. Insert operations on non-universal table spaces and universal table spaces that do not use the MEMBER CLUSTER option will always use IAG1.
Although IAG2 can provide significant performance benefits when dealing with concurrent high-volume inserts, some situations, such as when IAG1 and IAG2 are operating at the same time in the same member or across members, can be potentially complicated and could slow down INSERT performance. Therefore, we highly recommend that you test IAG2 thoroughly before using it in your production environment. During your testing, if you find that IAG2 is being disabled and re-enabled multiple times in a short period, we recommend that you diagnose and completely resolve the reason for frequent disablement before planning to use IAG2 in production. If you're not able to determine why IAG2 is being disabled, use IAG1 until you're able to do so. Lastly, we recommend that the DEFAULT_INSERT_ALGORITHM subsystem parameter be set to the same value in all data sharing members.
While you are testing IAG2, if you experience excessive and unexpected increases in space usage, evaluate your INSERT workload to determine if the INSERT workload is relatively low. If it is low, you can explicitly disable IAG2 for this table space by using the ALTER TABLESPACE statement with the INSERT ALGORITHM level set to 1.
You can read more about IAG2 in Knowledge Center. Also, stay tuned for an upcoming blog that will explain how to effectively monitor IAG2 performance and troubleshoot common performance issues.
Kalpana Shyam is a Software Developer for Db2 for z/OS, and Eric Radzinski is an Information Developer for Db2 for z/OS.
Always get the latest news about Db2 for z/OS from the IBM lab! How to subscribe
Follow us on Twitter: @DB2zLabNews