RE: Loading history into a temporal table -- MIDRANGE-L

Rob,

Thanks for the information. I suppose I will need to write a program to load the history correctly.

Enjoy your trip to Goshen tonight.

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 Rob Berendt
Sent: Tuesday, November 16, 2021 7:38 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.

You could follow the steps to set up a temporal table. If followed carefully then you should be able to get that column created fine to be able to feed into it.
One careful step is to either not activate temporal table before you migrate your data, or turn it off for the migration. IOW wait until after the migration before you run
ALTER TABLE rco ADD VERSIONING USE HISTORY TABLE rco_history;
There are some examples on how to turn off temporal support if you search Run SQL Scripts for temporal. Edit, Examples, Search from examples.
The reason you need to do that is the history table will not allow you to add rows until you turn it off.
DSPFD Command Input
File . . . . . . . . . . . . . . . . . . . : FILE RCO_HIST
Allow read operation . . . . . . . . . . . : Yes
Allow write operation . . . . . . . . . . . : No
Allow update operation . . . . . . . . . . : ALWUPD *NO
Allow delete operation . . . . . . . . . . : ALWDLT *YES

Ps: I'll be in Goshen tonight to watch my granddaughter play violin.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com<http://www.dekko.com>

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

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

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

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>

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.
--
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>
--
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>