Insert algorithm 2
Insert algorithm 2 (IAG2) can increase throughput of INSERT statements, reduce logging, and reduce class 2 elapsed time and class 2 CPU time in certain conditions.
The types of workloads that benefit most from IAG2 include traditional transactional workloads that insert millions of rows concurrently into one or more Db2® tables, and new types of workloads that involve millions of data points (for example, Internet of Things (IoT) applications such as weather-related applications that collect a huge volume of data simultaneously from different locations). Workloads that consist of multiple batch jobs that insert data simultaneously against the same set of tables will experience the biggest performance improvements.
- Enables a huge volume of data to be inserted quickly, which makes that data available for immediate consumption by ad hoc queries
- Eliminates data page contention that can occur when the same location to insert a row is given to multiple agents
- Eliminates space map contention that can typically occur when multiple agents are inserting at the end of tables or partitions
IAG2 reduces space contention for workloads that insert a high volume of data by
preassigning data pages. IAG2 accumulates page numbers of data pages that have enough space for
inserting new rows in an area called insert algorithm 2 page pipe in memory. Db2 adds new page numbers to the page pipe as
needed. When a row needs to be inserted, Db2 selects a page number from the page pipe, which guarantees that adequate space is available for
the new row.
When is IAG2 used?
IAG2 can be enabled for a specific table space by specifying the INSERT ALGORITHM 2 clause of the CREATE TABLESPACE or ALTER TABLESPACE statement. The DEFAULT_INSERT_ALGORITHM subsystem parameter controls the enablement of IAG2 at the subsystem level.
IAG2 is used only with universal table spaces that use the MEMBER CLUSTER option. Insert operations on non-universal table spaces and universal table spaces that do not use the MEMBER CLUSTER option use insert algorithm 1 (IAG1). IAG2 also supports LOAD SHRLEVEL CHANGE for universal table spaces that use MEMBER CLUSTER.
In certain conditions, IAG2 is not used even if it is explicitly set and all required conditions are met. For example, if a table or table space is locked or if a lock escalation occurs, IAG2 is not used, and the application falls back to using IAG1.
In situations where IAG2 becomes disabled and an insert operation falls back to using IAG1, message DSNI055I is issued. This message and its accompanying reason codes provide detailed information about the conditions that caused the INSERT statement to fall back to using IAG1.
Use cases that yield the greatest performance benefits
Although IAG2 can improve the performance of concurrent high-volume inserts, some situations provide greater benefits than others.
For example, the most significant performance advantage can be seen when a large number of jobs insert data concurrently into the same table or same set of tables. 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 eliminated.
However, IAG2 does not provide a significant performance advantage when a small number of batch jobs insert multiple rows serially. In this situation, rows are being inserted one at a time, which means that usable space within the table space is being consumed only one row at a time. Because there is no space contention, pre-assigning data pages provides little benefit.
- To determine whether the performance of your workloads can be improved by IAG2, evaluate accounting report class 3 suspensions. If you see a long wait in page latch contentions or global contentions that originate from data page or space map pages, IAG2 might alleviate these delays.
- In most situations, the use of IAG2 should not significantly increase space usage. However, when you use IAG2 with tables that don't experience a high volume of INSERT activity, space usage can increase in anticipation of heavy INSERT activity by Db2. Because IAG2 is used only for table spaces that have been defined with the MEMBER CLUSTER option, evaluate the need for this option on tables that have low INSERT activity. Removing this option will force the use of IAG1, thereby avoiding increased space usage. Alternatively, If the MEMBER CLUSTER option is required, issue an ALTER TABLESPACE statement and change the INSERT ALGORITHM option to 1 to avoid space increase.
Factors that can affect insert performance
Although IAG2 eliminates data page and space map contention in optimal conditions, other factors such as index contention and log write wait can also negatively affect the performance of concurrent inserts. IAG2 has no mitigating effect on these problems.
Additionally, insert performance can be affected by general performance issues such as network bottlenecks and issues with buffer pools, the coupling facility, and the Db2 log.
What happens if IAG2 becomes disabled?
If IAG2 becomes disabled, Db2 issues message DSNI055I and an accompanying return code that indicates the reason for the failure. If the failure was caused by a temporary situation, such as a lock escalation or a page pipe storage issue, or an out-of-space condition that might require manual intervention, Db2 automatically attempts to re-enable IAG2 at predefined intervals while it waits for the failure condition to be resolved. The first attempt to re-enable IAG2 occurs 75 seconds after the initial failure. Subsequent attempts will continue at increasing intervals up to once per hour.
If IAG2 becomes disabled due to a situation that must be corrected manually, such as when the maximum amount of space is reached, Db2 might not automatically attempt to re-enable IAG2. Examine lower-level services messages to determine the cause of the failure. In these types of situations, IAG2 will be re-enabled at the first insert after additional space has been added.
When IAG2 is successfully re-enabled automatically without manual intervention, message DSNI087I is issued. For situations that require you to add space, DSNI087I is not necessarily issued.