I'm trying to set up some test scripts to demo some of the new V10.1 features. I'm successfully creating a System Temporal Table with versioning and what I'd like to do is to control the dates for the Inserts, updates and deletes that I make. I want to be able to do an insert and generate a System_Begin of 3 days ago, and then do an Update with a System_Begin of 2 days ago, and then a delete with a date of 1 day ago.
I could then construct some queries around specific, predictable time periods to show how the TTQ options would work. I was hoping to use SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - x days
but this causes an SQL20535N when you try and do an INSERT and appears to be there to allow you to control the queries, not the data IUD.
Does anyone have any suggestions for how to control the system dates to allow me to specify the contents of the History file more exactly?
The fundamental concept of system time is that it reflects the time at which transactions are actually executed in the DB2 system. Therefore, the timestamps for the system_time period are always set by DB2. For compliance and audit reasons there shouldn't be way, at least no easy way, to insert a row today and to pretend it was already inserted 3 days ago :-).
For the same reason, there is no concept of performing I/U/D as of a past point in system time. Therefore, I/U/D on a system-period temporal table are blocked when the special register CURRENT TEMPORAL SYSTEM_TIME is set to a non-NULL value. You cannot and should not go back in time to modify the recorded facts retroactively.
If you want to express that certain information is valid in the real world since 3 days ago, then business time would be the correct concept to use. It allows you to set the start and end dates in the past, present, or future.
If you want to make retroactive changes, such as increasing an interest rate by 1% from last month onwards, then business time is again the right choice.
I understand the problems with creating a canned demo of system time when the timestamps are generated on the fly and you want to retrieve specific versions of a row in a subsequent query.
To get your own system start and end times into a system_time period, you can use LOAD with the PERIODOVERRIDE option.
Another option for a demo is to insert rows with your own chosen timestamps before you enable versioning. This is the approach that we took in the sample script sqllib/sample/clp/temporal_stt.db2. You could take a look at that and see if that helps you.
Thanks Matthias. The option you presented in your script (manually INSERT data into a non-temporal table and then convert it to System-Time) has worked perfectly and let me build a bespoke script for my client to demo the feature. Just what we needed