Is anyone successfully using System Time temporal to solve concurrent user query and batch update against the same set of tables, when standard DB2 database locking is not an option for the update cycle?
eg: a batch update cycle does hundreds of Insert/Update statements that run across a few hours. The change log becomes too large & requires many COMMIT statements to be issued along the way. Only at end-of-cycle is the data again consistent & suitable for user query.
Temporal could theoretically be used here (performance permitting).
The batch cycle could first replace user query views of the tables to be updated. This replacement would position the data AS OF the cycle start time.
The batch cycle could lastly replace the same user query views, this time removing the temporal clause.
Thoughts & experience feedback would be welcomed.
Pinned topic Snapshot for Concurrent Query & Batch Update
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-05-05T01:06:47Z at 2012-05-05T01:06:47Z by Steve_Chen
Steve_Chen 060000XBJ53 Posts
Re: Snapshot for Concurrent Query & Batch Update2012-05-05T01:06:47ZThis is the accepted answer. This is the accepted answer.You are right that temporal feature could provide a consistent snapshot. But all tables need to be converted to system period temporal tables, and all queries need to be converted to FOR SYSTEM_TIME AS OF timestamp_before_batch_starts. The converted queries have performance overhead caused by Union All of referenced tables to their associated history tables. One possible alternative is to crone a separate DB2 subsystem using FlashCopy before batch program starts, and to route readers to the croned DB2. Obviously, it takes a lot of setup and planning too.