Db2 12 offers many performance improvements that can help you
to speed up your applications. The Fast Insert algorithm, also called Insert
Algorithm 2, is one such improvement, but you have to understand what it is and
when it can be helpful.
The general idea behind this alternative Insert algorithm is
to improve performance of Insert-intensive workloads for unclustered data. This
is a common requirement for applications that require an audit table or a
journal of activities. New data that is inserted is appended to the end of the
table with no concern for clustering. A frequent issue with such applications
is when the workload is so high that rows cannot be inserted rapidly enough thereby
increasing the elapsed time.
Most of these types of applications design the journal/audit
table using partitioned table spaces with the MEMBER CLUSTER and APPEND
attributes. This design will direct Db2 to insert all new rows at the end of
the partition. Insert performance should improve because the space search
algorithm can be bypassed for the most part. Nevertheless, some Db2
applications still experienced performance issues even when using this approach.
This bring us to the Db2 12 Fast Insert
algorithm. This new algorithm uses an in-memory structure called an insert pipe
to speed things up. The insert pipe maintains a list of pages that are
available for this member to use for Insert processing for the page set
partition. Each member that opens a page set gets an Insert pipe for that member
to use. A system agent fills up the pipe asynchronously, making sure that pages
are always available for the threads to use for inserting rows.
There is more to the process, but that is the high-level intro
to how it work. Now the question is: when will the new algorithm be used?
The Fast Insert algorithm only works with Universal table
spaces with the MEMBER CLUSTER option; APPEND is not required. The new algorithm
is the default algorithm for this type of table space.
Settings
and options are available to control use of the new algorithm. To set usage of
the algorithm at a system-wide level, use the DEFAULT_INSERT_ALGORITHM subsystem parameter (DSNZPARM). There are three options:
- 0 indicates that the basic Insert algorithm is to be used no matter what (Insert algorithm 2 is disabled for the subsystem);
- 1 indicates that the basic insert algorithm is used unless insert algorithm 2 was specified at the table space level;
- 2 indicates that Insert algorithm 2 is used unless insert algorithm 1 was specified at the table space level.
Which brings us to the DDL options for controlling the insert algorithm
at the individual table space level. To do so, use the INSERT ALGORITHM option on the CREATE TABLESPACE or ALTER TABLESPACE statement. Again, there are
three options:
- 0 indicates that the Insert algorithm to be used is as specified by the DEFAULT_INSERT_ALGORITHM subsystem parameter at the time a row is inserted;
- 1 indicates that the basic Insert algorithm is to be used; and
- 2 indicates that the Fast Insert algorithm is to be used.
Summing
Up
The impact of using the new Fast Insert algorithm will depend
on various factors, including whether the table has indexes or not and the
specific makeup of your workload. If the workload has lock/latch contentions (on
the space map pages and data pages) then the new Insert algorithm will probably
be beneficial.
