Skip to main content
Question

Convert DDS physical table to temporal table best practice using Aldon

  • July 28, 2021
  • 1 reply
  • 1 view
R
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

    1 reply

    Dale Asher
    Forum|alt.badge.img+4
    • Dale Asher
    • Inspiring
    • August 9, 2021
    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...
    ------------------------------
    Level 3 support for Rocket DevOps, Plugins and Portals...
    Terms & ConditionsAccessibility statement