Unraveling the Mysteries of Insert Algorithm 2
EricRadzinski 060000F2X2 Comments (3) Visits (7291)
By Kalpana Shyam.
Insert algorithm 2 (IAG2) was introduced in Db2 12 to increase throughput of INSERT statements and to reduce logging in specific conditions.
The purpose of this blog post is to help you understand IAG2 in the simplest terms possible and to provide you with information that will help you get the greatest benefits from using it. This blog post provides overview information to help you get started and more detailed information to help ensure your ongoing success with IAG2 in a production environment. This information will be useful for anyone who wants to optimize IAG2 or troubleshoot IAG2 issues. In some cases, you might find the information to be more detailed than necessary. The intent is to provide specific references to help you find additional useful information in the IBM
What is IAG2?
Db2 12 for z/OS introduced IAG2 to boost INSERT throughput for unclustered INSERTs. IAG2 is available only for use with universal table spaces that have been defined with the MEMBER CLUSTER option. Improved INSERT performance is achieved by predetermining space in batches when Db2 processes a high volume of INSERT statements, which reduces the bottlenecks seen during space search when insert algorithm 1 (IAG1) is used. Better INSERT performance results in increased throughput from your applications.
A side benefit of IAG2 is that it reduces the number of log records generated during INSERTs. The log record savings is achieved by the bulk formatting of data pages.
IAG2 is self-correcting when possible. If Db2 is not able to take advantage of IAG2 during a workload due to temporary resource constraints, Db2 will fall back to using IAG1. Db2 will then monitor the resources and the INSERT activity and begin using IAG2 when the resource constraints are resolved. Db2 re-enables IAG2 when possible, but there can be times when further investigation will be required. You will see the greatest benefits of IAG2 when there aren’t any bottlenecks outside Db2. Some of the known bottlenecks outside Db2 are transmission delays, slow disks, and possible delays in IRLM or the Coupling Facility.
When is IAG2 useful?
The performance improvements provided by IAG2 are most visible when there are multiple agents inserting to the same table concurrently and when each of these agents is doing multiple INSERTs in the same commit scope. A single agent, by itself, will not see improvement because there is no bottleneck during space search for a single agent.
Additionally, IAG2 is most useful in situations where space search and false leads on the data pages in the table space are the biggest constraining factors to INSERT performance. The benefits of IAG2 might not be apparent when there are multiple indexes on the table because IAG2 optimizes data row insert. Inserting keys to many indexes for one data row insert will negate overall INSERT performance by IAG2. IAG2 is designed for high volume and highly concurrent INSERT activity. When IAG2 is in effect, bottlenecks might shift from space search and false leads to other areas such as Index Leaf Page Latch, Log Write, format write during data set extend, and other such areas.
When is IAG2 not useful?
IAG2 is not intended to be a replacement for IAG1. There is no advantage of using IAG2 when there is a single thread doing all the INSERTs because there is no resource contention. In fact, when IAG2 is used in low volume INSERT situations, you can experience unnecessary increases in space usage in anticipation of heavy INSERT activity by Db2.
There is also no advantage of using IAG2 when factors other than space search and false leads are the primary constraints to INSERT throughput (for example, index maintenance).
What's required to use IAG2?
IAG2 becomes available when the following conditions are met:
How to monitor INSERT performance
This section describes some general and some specific ways to gain insights into IAG2 performance.
Monitoring outside of Db2
Comparing IAG1 throughput to IAG2 throughput is an effective way to determine the level of benefit provided by IAG2.
To determine whether IAG2 is effective, establish a baseline of existing throughput in terms how much time it takes to insert a certain number of rows when IAG2 is turned off. Then, make the same measurement after turning on IAG2. You can toggle between the two insert algorithms in several ways. Although it’s possible to take the baseline measurement in Db2 11, you would be changing two variables: Db2 12 and IAG2. Alternatively, you could change the DEFA
When you take performance measurements, be sure to eliminate as much noise as possible. For example, exclude the first INSERT statement because it includes startup costs of IAG2. Also, you might already have historical data that you can use as baseline.
When IAG2 is not delivering the performance that you’re expecting, you can use IFCID trace records to determine why.
Because IFCID 002 is always available, looking at it is a quick way to see IAG2 activity before drilling down deeper. If QISTINPA is non-zero, at least some INSERTs are using IAG2. If QISTINPD is non-zero, IAG2 is being used, but there were times when IAG2 had to be disabled. If QISTINPD is unusually high, the situation requires further investigation.
Similarly, QXRWSINSRTDALG1 and QXRWSINSRTDALG1 provide a very high-level indication of IAG2 usage. The two counts provided by QXRWSINSRTDALG1 and QXRWSINSRTDALG2 are also reflected in IFCIDs 003 and 148 at the thread level.
Monitoring Db2 messages
When the results of your IFCID monitoring shows many instances of the IAG2 pipe being disabled or long wait times, examine the console log for messages that can offer further clues.
Monitoring Db2 log records
In addition to regular space map update and data page update log records, Db2 writes diagnostic log records to keep track of certain events for troubleshooting. The following log records are useful in monitoring IAG2.
Data Manager log records show space map page updates and data page updates. Log records can be critical for troubleshooting Data Manager problems. Collect these for a specific DBID and PSID for a specified duration using the DATAONLY(YES) option of the DSN1LOGP standalone utility. If the volume is not an issue, you can omit DATAONLY(YES) so that page set checkpoint log records can be studied for Buffer Manager activity regarding all pages. An existing RMID 14 (Data management) change in data page log record has new fields that indicates which insert algorithm was used to insert that row and other information. The log record is:
LGOPBLRHSTYPE: Change in data page log record has a new field called LGBALGLU that indicates which insert algorithm level was used. A second new field in this log record is LGBALGSP, which shows which subpipe the chosen data page came from. This information is useful when you have a question about a specific row, such as why it didn’t use an old page, or why it reused a page that was already in use.
Another existing RMID 14 change is the following new field in the space map page log record:
LGOPFNP LRHSUBTYPE: Format of space map page or update of space map page log record has new bits:
The following new RMID 14 log record subtypes are relevant to IAG2:
The log record contents are described in DSNDQJ00.
Monitoring Db2 page content
The content of pages is significant in troubleshooting IAG2 problems. IBM Support can help troubleshoot IAG2 problems by looking at the contents of space map pages and data pages from the Db2 Data Manager perspective. When you report an IAG2 problem to IBM Support, you might be asked to send image copies or asked to use DSN1PRNT to print the contents of a space map and data page as formatted or unformatted. Sometimes only space map pages are required. Use the NODATPGS option to generate space map pages only, which produces a much smaller output. When you need to print data pages, omit the NODATPGS option.
This section provides some scenarios for troubleshooting IAG2 problems. Not every scenario is covered, but the following ones provide some general guidance for using the available serviceability tools and techniques.
Before you begin to troubleshooting an IAG2 problem, be aware of the level of code that is installed. It is recommended that you apply relevant maintenance as it becomes available. Insert algorithm 2 APARs are generally tagged with "IAG2." Some of the key modules whose PTF levels can be checked in the MEPL are: DSNISRTI, DSNISFPI, DSNISGRT, DSNISGSP, DSNIASFP, DSNISFAS, DSNISFRC, and DSNISFRO.
Scenario 1: Users are reporting that an INSERT application is running very slowly, and you suspect that IAG2 is the cause.
As a DBA, you have the following tools available to you.
Scenario 2: You’re seeing many DSNI055I messages. What's going on?
Because DSNI055I is issued only by IAG2, you already know that IAG2 is being used. However, why is it getting disabled?
Scenario 3: You’re noticing extraordinary space usage. Why?
IAG2 is designed for very high volume insert activity. If a table is using IAG2 and is not very active, then IAG2 might cause an unnecessary increase in space usage in anticipation of heavy INSERT activity by Db2.
Check the volume of your INSERT workload. Are rows being inserted frequently? Are a relatively high volume of rows being inserted? If the answer to either of these questions is no, you should probably be using IAG1 for this table.
Scenario 4: You see message DSNI055I with reason-code = C and error-reasoncode = 00000000. What does this mean?
Reason-code C is issued when Db2 waited too long for a resource. In most cases, the associated error-reason-code is documented in the “Db2 reason codes” topic, which you can consult for further information. Rarely, an error-reason-code of all zeros can be issued, which typically occurs when IAG2 times out waiting for a resource such as a page p-lock. Such a resource contention can occur during any heavy workload in data sharing mode. In this case, contact IBM Support for further analysis. Provide the complete DSNI055I message including the internal trace information.
Many other scenarios can be helpful in understanding IAG2 issues, such as determining the cause of a bottleneck or whether the size of a buffer pool is large enough. However, scenarios such as these require general Db2 performance troubleshooting knowledge, which is outside the scope of this blog.
Controlling the use of IAG2
As mentioned previously, in some situations IAG1 is the more appropriate insert algorithm. There are a couple of methods for controlling whether Db2 uses IAG2 or IAG1 (of course, IAG1 will always be used on non-universal table spaces that don't use the MEMBER CLUSTER option):
DEFAULT INSERT ALGORITHM subsystem parameter guidelines
It is strongly recommended that all data sharing members use the same DEFA
Although Db2 supports IAG1 and IAG2 running in parallel in the same data sharing member and across data sharing members, the concurrent use affects overall INSERT performance adversely. There are times when IAG1 and IAG2 must run in parallel, such as when one member disables IAG2, but another member is still using IAG2, or in the same member when one transaction enables IAG2, but there is an older transaction still using IAG1. Such conditions are temporary, and the insert algorithm is expected to synchronize quickly.
Although IAG2 is the default in Db2 12, there are many nuances that you need to understand to use it effectively and to evaluate its benefit. We hope this article will help you to evaluate, monitor, and troubleshoot IAG2.
If you're interested in reading about some recent significant enhancements to IAG2, check out this
Kalpana Shyam is a Software Developer for Db2 for z/OS at the IBM Silicon Valley Lab.
Always get the latest news about Db2 for z/OS from the IBM lab! How to subscribe
Follow us on Twit