Chris,
Thanks for the detailed instructions. I'll see what I can do with them.
Dean Eshleman
Software Development Architect
Everence
1110 North Main Street
PO Box 483
Goshen, IN 46527
(800) 348-7468 ext. 3528
(574) 533-9515 ext. 3528
[Join our Talent Network]<
http://www.everence.com/careers>
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Hiebert, Chris
Sent: Tuesday, November 16, 2021 10:56 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Loading history into a temporal table
This email originated outside of Everence. Do not click links or attachments unless you recognize the sender.
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<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Dean Eshleman
Sent: Monday, November 15, 2021 8:26 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto: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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l<
https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l<
https://archive.midrange.com/midrange-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com<
https://amazon.midrange.com>
Confidentiality Notice: This information is intended only for the individual or entity named. If you are not the intended recipient, do not use or disclose this information. If you received this e-mail in error, please delete or otherwise destroy it and contact us at (800) 348-7468 so we can take steps to avoid such transmission errors in the future. Thank you.
midrange.com
