Skip to main content
What would be the best practice to convert existing DDS physical table to a temporal table using Aldon?

The following columns need to be added to the physical table:

ADD COLUMN TEMPORAL_ROW_START TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS ROW BEGIN
ADD COLUMN TEMPORAL_ROW_END TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS ROW END
ADD COLUMN TEMPORAL_ROW_TRANS TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS TRANSACTION START ID
ADD COLUMN TEMPORAL_audit_user VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER)
ADD COLUMN TEMPORAL_audit_op CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION)
ADD PERIOD SYSTEM_TIME(TEMPORAL_ROW_START, TEMPORAL_ROW_END

Also, a history table needs to created and associated with the physical table:

ADD VERSIONING USE HISTORY TABLE libray.tablename_history ON DELETE ADD EXTRA ROW
What would be the best practice to convert existing DDS physical table to a temporal table using Aldon?

The following columns need to be added to the physical table:

ADD COLUMN TEMPORAL_ROW_START TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS ROW BEGIN
ADD COLUMN TEMPORAL_ROW_END TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS ROW END
ADD COLUMN TEMPORAL_ROW_TRANS TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS TRANSACTION START ID
ADD COLUMN TEMPORAL_audit_user VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER)
ADD COLUMN TEMPORAL_audit_op CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION)
ADD PERIOD SYSTEM_TIME(TEMPORAL_ROW_START, TEMPORAL_ROW_END

Also, a history table needs to created and associated with the physical table:

ADD VERSIONING USE HISTORY TABLE libray.tablename_history ON DELETE ADD EXTRA ROW
Ramiro, I think you've posted this question in two different threads?  Posting my last comments/summary from the other posting ("How to change physical file (DDS) to become a temporal table using Aldon?").  
Best, 
d
~~
...detail from our L3 for LMi below.  Pls run it through support if this doesn't get you past the finish line.
Best, 
d
~~

Linda 7/29 via slack...

...minimum LMi 8.5 and V7R3. 
first add history *TABLE.  
then checkout and convert *FILE to *TABLE with option 16.  
then option 16 again to convert *TABLE to temporal table...  
there are other details but seems they lend themselves more to a support call...  
things like moving source from QDDSSRC to QSQLSRC (if they choose to do so), change member source type from PF to SQL, etc...



------------------------------
Dale
L3 Support for the enterprise bits, portals and plugins...
Rocket|Aldon
Somewhere on the Oregon coast...
------------------------------
Terms & ConditionsAccessibility statement