Topic
1 reply Latest Post - ‏2012-03-07T00:46:27Z by MatthiasNicola
jacques.lamarre@irs.gov
1 Post
ACCEPTED ANSWER

Pinned topic Positioning db2 v9.x z/os database for v10 temporal data management

‏2012-03-06T18:17:44Z |
re versions with system time:

Looks like there is new sysntax in v10. Example from white paper:

sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
trans_start TIMESTAMP(12) GENERATED ALWAYS
AS TRANSACTION START ID IMPLICITLY HIDDEN,
PERIOD SYSTEM_TIME (sys_start, sys_end)

And

ALTER TABLE xxxx ADD VERSIONING USE HISTORY TABLE xxxx_history;

Any suggestions on how to stage a small v9.x db2 z/os database?

Should I just add the following to each table?

sys_start TIMESTAMP NOT NULL,
sys_end TIMESTAMP NOT NULL,
trans_start TIMESTAMP NOT NULL,
SYSTEM_TIME TIMESTAMP NOT NULL

and execute

CREATE TABLE xxxx_history like xxxx;

I'm no sure this would be the best idea short of waiting for v10.

Thanks,
Jacques
jacques.lamarre@irs.gov
Updated on 2012-03-07T00:46:27Z at 2012-03-07T00:46:27Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    7 Posts
    ACCEPTED ANSWER

    Re: Positioning db2 v9.x z/os database for v10 temporal data management

    ‏2012-03-07T00:46:27Z  in response to jacques.lamarre@irs.gov
    Hi Jacques,

    it depends on what you want to achieve. If you want to create a table in DB2 9 for z/OS that is easy to alter into a system-period temporal table in DB2 10, then you're on the right track.

    Define your V9 table with the three timestamp columns:

    sys_start TIMESTAMP NOT NULL,
    sys_end TIMESTAMP NOT NULL,
    trans_start TIMESTAMP NOT NULL,

    Do not define a column called "SYSTEM_TIME".

    When you move to V10 you can issue ALTER TABLE statements to alter the column definitions (e.g. to GENERATED ALWAYS AS ROW BEGIN, etc.) and an ALTER TABLE statement to add the SYSTEM_TIME period declaration.

    For your V9 table you would also define a history table:
    CREATE TABLE xxxx_history like xxxx;

    In V9 you would then also need triggers that:
    (a) set the timestamp columns on insert, update, and delete
    (b) insert the before image of updated or deleted rows into the history table, again with proper timestamps.

    When you move to V10, you no longer need such triggers. DB2 generates such history for you automatically.

    In V10 you can query across current and history data with the new "FOR SYSTEM_TIME" clause in SELECT statements. This clause is not available in V9, so writing temporal queries in V9 is a lot more tedious. So, you really can't use any of the temporal enhancements until you have a DB2 v10 installation to play with.

    Does this help?

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML