Topic
  • 4 replies
  • Latest Post - ‏2013-12-09T07:27:01Z by Bhaskar157
Bhaskar157
Bhaskar157
3 Posts

Pinned topic DB2 Temporal Tables

‏2013-11-21T12:28:33Z |

 

Hi,

I have few questions on DB2 V10 Temporal Table feature.
 
Is it possible to combine multiple table columns(basically joining two tables) and apply the temporal concept on them? 
If so how could be the History table be created?
What is the use of TRANS_START column in System Temporal table ? I observed that SYS_START will also have the same time.  
Is it possible to skip columns from the Base table while creating History table? 
Basically can I select columns of my choice for History table creation? 
How can we control growth of History table as each row when updates or deleted will be inserted History table? 
Does dropping base table in any way effect History table or data?
After History table being created, if we want to add new columns to base table, do we need to take any additional actions apart from ALTER statement?

 

-Bhaskar

  • YiFan Zhong
    YiFan Zhong
    1 Post
    ACCEPTED ANSWER

    Re: DB2 Temporal Tables

    ‏2013-12-06T06:49:58Z  

    Hi Bhaskar,

       I try to answer your questions one by one and based on the version of DB2 for z/OS V10:

    1.  DB2z not support the mutiple tables joined as one table for the base table 

    2. TRANS_START column records the transaction start time it is the same value as SYS_START, but another choice it can be nullable to save the storage.

    3. Not support, history table must keep the same structure with the base table.

    4. Drop base table, if the tablespace is segemented tablespace, the history table will be dropped when you dropped base table, if in the PBR tablespae, you should alter disable the history version, then drop base table/history table one by one.

    5. Alter add new column to STT is supported, you can simply alter add new column to the base table, the new column will be added into history table automatically, but there still are some restrication. such as: generated columns is not supported.

     

    Hope answer your questions.

  • CatherineWu
    CatherineWu
    11 Posts

    Re: DB2 Temporal Tables

    ‏2013-11-25T05:10:15Z  

    Hi Bhaskar,

    Are you looking at DB2 for z/OS or DB2 for Linux, Unix and Windows? Thanks!

  • Bhaskar157
    Bhaskar157
    3 Posts

    Re: DB2 Temporal Tables

    ‏2013-12-03T11:42:17Z  

    Hi Bhaskar,

    Are you looking at DB2 for z/OS or DB2 for Linux, Unix and Windows? Thanks!

    Hi Catherine,

    I am looking at DB2 for z/OS 

    Thanks !

     

  • YiFan Zhong
    YiFan Zhong
    1 Post

    Re: DB2 Temporal Tables

    ‏2013-12-06T06:49:58Z  

    Hi Bhaskar,

       I try to answer your questions one by one and based on the version of DB2 for z/OS V10:

    1.  DB2z not support the mutiple tables joined as one table for the base table 

    2. TRANS_START column records the transaction start time it is the same value as SYS_START, but another choice it can be nullable to save the storage.

    3. Not support, history table must keep the same structure with the base table.

    4. Drop base table, if the tablespace is segemented tablespace, the history table will be dropped when you dropped base table, if in the PBR tablespae, you should alter disable the history version, then drop base table/history table one by one.

    5. Alter add new column to STT is supported, you can simply alter add new column to the base table, the new column will be added into history table automatically, but there still are some restrication. such as: generated columns is not supported.

     

    Hope answer your questions.

  • Bhaskar157
    Bhaskar157
    3 Posts

    Re: DB2 Temporal Tables

    ‏2013-12-09T07:27:01Z  

    Hi Bhaskar,

       I try to answer your questions one by one and based on the version of DB2 for z/OS V10:

    1.  DB2z not support the mutiple tables joined as one table for the base table 

    2. TRANS_START column records the transaction start time it is the same value as SYS_START, but another choice it can be nullable to save the storage.

    3. Not support, history table must keep the same structure with the base table.

    4. Drop base table, if the tablespace is segemented tablespace, the history table will be dropped when you dropped base table, if in the PBR tablespae, you should alter disable the history version, then drop base table/history table one by one.

    5. Alter add new column to STT is supported, you can simply alter add new column to the base table, the new column will be added into history table automatically, but there still are some restrication. such as: generated columns is not supported.

     

    Hope answer your questions.

    Thank you YiFan..

    I did got some answers by myself in the mean while. 

    TRANS_START is the key column which serves as a link between Base and History table. I tried to create the base table with out this column then I got the error below 

     

    DSNT408I SQLCODE = -20490,

    ERROR : A VERSIONING CLAUSE WAS SPECIFIED FOR TABLE SPODILI.TABLE_EMP BUT THE TABLE CANNOT BE  USED AS A SYSTEM PERIOD TEMPORAL TABLE.

    REASON CODE = 2