We were working on something like this just this last week.
Our Table contains an Identity Column "GENERATED ALWAYS AS IDENTITY (CYCLE)".
We use a procedure to add the Temporal Columns.
For the Update, we needed to create a view over the Table that does not include the Temporal Columns.
The INSERT works with the Temporal Table because you can use OVERRIDING USER VALUE, and it updates all generated columns properly.
DCL-DS MYTABLEDS EXTNAME('MYTABLE') INZ QUALIFIED END-DS;
EXEC SQL
INSERT INTO MYTABLE
OVERRIDING USER VALUE
VALUES (:MYTABLEDS);
On the update we need to use Overriding System Value in order for the Identity Column to not get a new value on every update.
DCL-DS MYTABLEVIEWDS EXTNAME('MYTABLEVIEW') INZ QUALIFIED END-DS;
// Set value for Temporal Field "generated always as (current CLIENT_PROGRAMID)" using the program name from the PSDS
Exec sql CALL SYSPROC.WLM_SET_CLIENT_INFO( CLIENT_PROGRAMID => :PGMNAM);
// Load the update ds from the original table ds.
EVAL-CORR MYTABLEVIEWDS = MYTABLEDS;
// update table
EXEC SQL
UPDATE MYTABLEVIEW
OVERRIDING SYSTEM VALUE
SET ROW = :MYTABLEVIEWDS
WHERE RCDID = :MYTABLEVIEWDS.RCDID;
For the initial load and conversion of the data we needed to stop versioning.
ALTER TABLE LIB/MYTABLE DROP VERSIONING;
Then you can generate the data into another table defined like the temporal table.
This allows the table to get the same columns, just without the generated always aspect.
Create LIB/mytablecvt like lib/MYTABLE;
Then we can use a CPYF to move the data to the new temporal table.
CL: CPYF FROMFILE(lib/mytablecvt) TOFILE(lib/MYTABLE) MBROPT(*REPLACE) ;
After that we restart versioning.
ALTER TABLE LIB/MYTABLE ADD VERSIONING USE HISTORY TABLE LIB/MYTABLEHS ON DELETE ADD EXTRA ROW;
You do have to make sure that you setup the values for the Temporal Columns correctly. For example you can't have any future timestamps in the Start Period. And it's useful to know that the system uses '9999-12-30 00:00:00.000000000000' as the End Period timestamp.
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: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jimmy Sansi
Sent: Thursday, October 21, 2021 9:56 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Temporal Tables and SQL0109
I'm experimenting with temporal tables. No problem setting that up. For the program I've defined an external data structure using extname for the temporal table. The trouble with this is how to do the
I'm experimenting with temporal tables. No problem setting that up.
For the program I've defined an external data structure using extname
for the temporal table. The trouble with this is how to do the SQL
insert/update statement with generated columns using only the data
structure. In the past I've made it work on tables with generated
identity column by using OVERRIDING USER VALUE in the insert/update. But
the compiler is giving me an SQL0109 Position 1 OVERRIDING clause not
allowed error.
I know the "workaround" but in this instance I'd prefer to use the data
structure instead of specifying all the columns and then DEFAULT for the
generated fields.
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/rpg400-l__;!!O6xM9Yim9Yk!urW2OToCu7YFjongWPbGIR9_lnJsRnfuwVIkucknEsfQptYi5nPE7J393aVHMqKx8JAeJg$<
https://urldefense.com/v3/__https:/lists.midrange.com/mailman/listinfo/rpg400-l__;!!O6xM9Yim9Yk!urW2OToCu7YFjongWPbGIR9_lnJsRnfuwVIkucknEsfQptYi5nPE7J393aVHMqKx8JAeJg$>
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://urldefense.com/v3/__https://archive.midrange.com/rpg400-l__;!!O6xM9Yim9Yk!urW2OToCu7YFjongWPbGIR9_lnJsRnfuwVIkucknEsfQptYi5nPE7J393aVHMqIWP8V7QQ$<
https://urldefense.com/v3/__https:/archive.midrange.com/rpg400-l__;!!O6xM9Yim9Yk!urW2OToCu7YFjongWPbGIR9_lnJsRnfuwVIkucknEsfQptYi5nPE7J393aVHMqIWP8V7QQ$> .
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://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!urW2OToCu7YFjongWPbGIR9_lnJsRnfuwVIkucknEsfQptYi5nPE7J393aVHMqIKU-8ahQ$<
https://urldefense.com/v3/__https:/amazon.midrange.com__;!!O6xM9Yim9Yk!urW2OToCu7YFjongWPbGIR9_lnJsRnfuwVIkucknEsfQptYi5nPE7J393aVHMqIKU-8ahQ$>
midrange.com
