• 1 reply
  • Latest Post - ‏2012-05-05T01:06:47Z by Steve_Chen
1 Post

Pinned topic Snapshot for Concurrent Query & Batch Update

‏2012-04-25T23:59:35Z |
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.
Updated on 2012-05-05T01:06:47Z at 2012-05-05T01:06:47Z by Steve_Chen
  • Steve_Chen
    3 Posts

    Re: Snapshot for Concurrent Query & Batch Update

    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.