Topic
  • 4 replies
  • Latest Post - ‏2012-07-24T20:58:59Z by jacques.lamarre@irs.gov
jacques.lamarre@irs.gov
3 Posts

Pinned topic DB2 v10 z/OS Temporal Table syntax 'ADD VERSIONING USE HISTORY TABLE' claus

‏2012-07-17T22:05:17Z |
OK, I give up. I'm getting the following error:

"QUERY MESSAGES:
DB2 auxiliary table cannot be referenced directly."

on executing:

ALTER TABLE owner.tablename
ADD VERSIONING USE HISTORY TABLE owner.HIST_tablename;

Any thoughts?

PS: My creates, and tablespaces are correct and yes we are using v10 DB2 z/OS.

Could this be related to our DB2 v10 setup?

Thanks beforehand, Jacques
Updated on 2012-07-24T20:58:59Z at 2012-07-24T20:58:59Z by jacques.lamarre@irs.gov
  • Steve_Chen
    Steve_Chen
    2 Posts

    Re: DB2 v10 z/OS Temporal Table syntax 'ADD VERSIONING USE HISTORY TABLE' claus

    ‏2012-07-23T23:23:32Z  
    The auxiliary table is only used for LOB. The history table is a normal table. Could you show the DDL statements in more detail?
  • jacques.lamarre@irs.gov
    3 Posts

    Re: DB2 v10 z/OS Temporal Table syntax 'ADD VERSIONING USE HISTORY TABLE' claus

    ‏2012-07-24T01:08:31Z  
    The auxiliary table is only used for LOB. The history table is a normal table. Could you show the DDL statements in more detail?
    Here's the flow which is righ out of examples:

    drop table bdz99.policy_info
    drop table bdz99.hist_policy_info
    CREATE TABLE bdz99.policy_info
    (
    policy_id CHAR(10) NOT NULL,
    coverage INT NOT NULL,
    sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
    sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
    create_id TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME(sys_start,sys_end)
    )IN SANDBOX.xxxxxTBL;

    CREATE TABLE bdz99.hist_policy_info LIKE
    bdz99.policy_info IN SANDBOX.xxxxxHIS;
    --or

    ALTER TABLE bdz99.policy_info
    ADD VERSIONING USE HISTORY TABLE bdz99.hist_policy_info

    ***note*** we finally discovered that both SANDBOX.xxxxxTBL and SANDBOX.xxxxxHIS have to be unique for each base and history table ie there is a 1-1 between table and table space. Don't know why but it seems to work now if we make sure each table space supports only one table (both base and history)

    Any thoughs appreciated.

    Thanks
  • Steve_Chen
    Steve_Chen
    2 Posts

    Re: DB2 v10 z/OS Temporal Table syntax 'ADD VERSIONING USE HISTORY TABLE' claus

    ‏2012-07-24T19:06:35Z  
    Yes, for system period temporal table, a table space can contain only one table. It is designed this way to make it easier to maintain data integrity. Both base table and history table must be recovered to the same point in time. The base and history table (and LOB and XML if exists) can only be recovered separately if VERIFYSET NO is specified for RECOVER utility.
  • jacques.lamarre@irs.gov
    3 Posts

    Re: DB2 v10 z/OS Temporal Table syntax 'ADD VERSIONING USE HISTORY TABLE' claus

    ‏2012-07-24T20:58:59Z  
    Thanks very much Steve_Chen!!!