RE: Loading history into a temporal table

["Hiebert, Chris" <chris.hiebert@xxxxxxxxxxxxxx>]

Create your Temporal Table and History Table.

Then create another conversion table using SQL: "CREATE TABLE ConversionTable LIKE TemporalTable".
This will create a new table all the same columns but without the limits of the temporal columns.

Then turn off versioning on the temporal table.
1.       ALTER TABLE XXX DROP VERSIONING

Populate the History Table with all the "Prior" records.

Populate the conversion table and set all the timestamps appropriately for the desired Current Record.

You can populate the temporal table and set the values of the generated columns by using CPYF.
         CL: CPYF FROMFILE(THELIB/CVT_TABLE) TOFILE(THELIB/TMPORALTBL) MBROPT(*REPLACE)  ;

If you are using Row Begin and Row End timestamps make sure those are appropriately set.
They cannot overlap and there can be no "Future" dated records in the History Table or the Temporal Table.
If you need to see how the system maintains them you just need to add/update/delete a few records in your temporal table.

Once the table are populated restart versioning.
1.       ALTER TABLE XXX ADD VERSIONING USE HISTORY TABLE YYY ON DELETE ADD EXTRA ROW


Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Dean Eshleman
Sent: Monday, November 15, 2021 8:26 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Loading history into a temporal table


Hi,



I'm experimenting with temporal tables and I want to know if I can load existing historical data into the table.  From the documentation on the IBM site, I can't find any instructions on how to do that.  Here are the fields in my existing file.



Account_number (key), cusip (key), as_of_date (key), nbr_of_shares



We receive this data from a third party every business day and we dump it into a file.  As a result, we can have multiple records for a given account_number, cusip where the nbr_of_shares is the same on multiple days.  If I switch this file to utilize a temporal table, the new file would have the following fields:



Account_number (key), cusip (key), nbr_of_shares, row_begin (timestamp), row_end(timestamp), trx_id (timestamp), data_operation (char 1)



So, how can I load my existing historical data into this table?  I would somehow need to specify the row_begin myself rather than letting the system do it.  Has anyone else done this?



Dean Eshleman

Software Development Architect

Everence