DB2 LUW Version 11.1.1.1 Preview Features
It's inevitable that you see a couple of DB2 Fix Packs every year and place the readme document in a drawer somewhere for perusal later when you are not busy. While the Fix Packs may contain fixes (well that is what they are primarily meant for, right?), there are sometimes a few enhancements buried in the fine print.
Two new items that should be of interest to both developers and DBAs were released in a preview mode in Fix Pack 1. Basically, this means you can start testing the features but they haven't been made generally available. This gives you a chance to try them out and be ready for the day that they are made official. The usual caveats surround these features since they shouldn't be used in production until they are made official.
Online Crash Recovery or Asynchronous UNDO
DB2 crash recovery is anything but online for most systems! If you run DB2 pureScale, you really don't need to worry about crash recovery since a failure in a member node does not affect users on other members, nor does it block workloads while recovery is taking place. However, the situation is much different with a traditional DB2 system where everyone is locked out during crash recovery
(Figure 1).
Figure 1: Current DB2 Crash Recovery Process
If a database is interrupted unexpectedly before all transactions are committed, or before the changes associated with committed transactions are fully written to disk, the database is left in an inconsistent state. When the database is restarted, these transactions are replayed during a process called crash recovery.
DB2 has two phases that it goes through during crash recovery:
The REDO phase ensures that all activities that had occurred prior to the crash are replayed in the database. This step ensures that any log records for committed transactions are written to the appropriate data pages and the database is updated as if the failure never occurred.
The UNDO pass rolls back all uncommitted transactions that were in flight at the time of the crash. Once this is complete, the database will be in a consistent state.
The database is only accessible after the full completion of crash recovery. This was not ideal, since a very large transaction could require a lengthy UNDO phase, leaving the database entirely inaccessible until it completed. In addition, the amount of time it takes for the system to accept connections is dependent on the amount and type of workloads that were running at the time of the failure. If a large batch job was running it could take an extended time for the recovery to complete.
The new Online Crash Recovery allows DB2 to start accepting connections and workloads much earlier when performing a crash recovery. With the introduction of asynchronous UNDO, the behavior of the recovery process changes so that connections can be made to the database after the REDO phase is complete as shown in Figure 2.
Figure 2: New DB2 Crash Recovery Process
With this new enhancement DB2 will allow connections to the database after the REDO phase has completed and while the UNDO phase executes. Tables that have uncommitted activity to be undone will be locked, but access to all other tables will be allowed. During the UNDO phase, DB2 will:
- Acquire exclusive locks on all tables or table partitions with in-flight operations immediately after the REDO phase
- Allow new connections to the database as early as possible in the UNDO phase so:
- Access to unlocked tables/partitions can proceed normally
- UR access to locked tables/partitions is allowed
- Process the UNDO phase asynchronously, releasing table locks as UNDO progresses
This change will be of most benefit in systems where there is a lot of batch and ELT processing where many inserts, deletes, and updates may occur between commit statements. Recovery of these large batch jobs will take some time to undo, so this new feature will allow applications to connect and access other objects while the UNDO phase is executing. The other benefit of this new design is that as recovery is completed on a table during the UNDO phase, it immediately becomes available to applications.
To enable the Asynchronous Online Crash Recovery Technical Preview, you must assign a value of YES to the undocumented registry variable DB2_ASYNC_UNDO:
db2set DB2_ASYNC_UNDO=YES
Restarting the instance is not necessary since the registry variable value active at the start of crash recovery is used. Once enabled, the following administrative notification message is written during crash recovery when the aysnchronous portion of the UNDO phase begins and database connections are allowed:
ADM1505I Crash recovery has completed synchronous processing
For more details on this feature refer to the technote at:
http://www-01.ibm.com/support/docview.wss?uid=swg21994342.
Give this new feature a try in your test environment. Since it is in preview mode it shouldn't be used in your production environment. Note that the registry setting and name of this feature may change before it becomes generally available, so make sure you read the README when the next Fix Pack is released!
Multi-Core Insert Parallelism
DB2 already has exceptional multi-core scalability for queries on SMP boxes and the ability to partition queries across MPP clusters for added performance. Parallelism is currently available today on columnar and row-based tables for queries, including the SELECT component of INSERT from sub-select. However, the insert operation is restricted to using a single-thread against the table (Figure 3):
Figure 3: Current INSERT INTO table parallelism
DB2 now provides a preview feature that enables parallel inserts into columnar tables so that the SELECT and the INSERT are parallelized (Figure 4):
Figure 4: New INSERT INTO table parallelism
This parallelism capability is enabled with two registry variables, DB2_REDUCED_OPTIMIZATION and DB2_EXTENDED_OPTIMIZATION.
db2set DB2_REDUCED_OPTIMIZATION=ENABLE_RTABLE_INS –immediate
db2set DB2_EXTENDED_OPTIMIZATION=CDE_PAR_IUD –immediate
You may want to keep a copy of the previous settings in the event you want to restore the previous values. This behavior may be enabled by default in the future so you may not need to set it in future fixpaks.
Once enabled, the degree of parallelism is automatically determined based on data volume (i.e., number of rows) so that INSERT parallelism will be determined by the quantity of rows being selected. The actual DEGREE of parallelism chosen by the optimizer is shown in the RETURN operator of the EXPLAIN_ARGUMENT table.
Early lab performance measurements show significant performance improvements (Figure 5).
Figure 5: Parallel INSERT performance results
Fineprint: These performance measurements and projections are using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multiprogramming in the user's job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve results like those stated here.
The expected benefits of this feature should be a significant reduction in the duration of ETL batch or ingest jobs that insert large quantities of rows.
For more details on this feature refer to the technote at:
http://www-01.ibm.com/support/docview.wss?uid=swg27049357.
Summary
DB2 LUW V11.1.1.1 introduces two features in a preview mode that will make DB2 recovery faster and improve the insert performance into columnar tables. Preview mode gives you an opportunity to try these features out in a test or QA environment before they become generally available in a future Fix Pack. Both features should prove to be useful for many DB2 customers!
Special thanks to Roger Sanders and Matt Huras for sharing much of their material in the creation of this article.