Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    Unanswered: View based on System Temporal Table

    Hi
    I've got a view based on three tables joined together, one of which (Contract) has SYSTEM_TIME Versioning enabled (view definition included at the bottom of this post). This has allowed me to interogate the view using Time Travel Query:

    select * from contract_details for system_time as of '02/02/2012'

    This works fine until you enable another of the tables within the view for SYSTEM_TIME and then you get

    SQL0727N An error occurred during implicit system action type "3".
    Information returned for the error includes SQLCODE "-203", SQLSTATE "42702"
    and message tokens "SYSTEM_BEGIN". SQLSTATE=56098


    But what can you use to qualify the SYSTEM_TIME? Expressing it as
    below gives an SQL0104.

    select * from contract_details for CONTRACT.system_time as of '02/02/2012' ;

    Does anyone know the correct way to express this query?

    Regards
    Mark Gillis

    CREATE OR REPLACE VIEW Contract_Details
    AS
    SELECT
    SUBSTR(Customer.Name,1,15) as Customer_Name
    ,Contract_Number
    ,Contract_Type
    ,Start_Date
    ,End_Date
    ,Smallint(Days(End_Date) - Days(Start_Date)) as Days
    ,Hours
    ,DATE(system_begin) as Sys_BEGIN_date
    ,TIME(system_begin) as Sys_BEGIN_TIME
    ,DATE(system_end) as Sys_end_date
    ,TIME(system_end) as Sys_end_TIME
    FROM
    Contract inner join
    Customer on Contract.Customer_ID = Customer.Customer_ID
    inner join
    Contract_Type on Contract.Contract_Type_ID = Contract_Type.Contract_Type_ID

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL0727N An error occurred during implicit system action type "3".
    Information returned for the error includes SQLCODE "-203", SQLSTATE "42702"
    and message tokens "SYSTEM_BEGIN". SQLSTATE=56098

    But what can you use to qualify the SYSTEM_TIME? Expressing it as
    below gives an SQL0104.

    select * from contract_details for CONTRACT.system_time as of '02/02/2012' ;
    system_time in your query may not be same as "SYSTEM_BEGIN" in SQL0727N message.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try to execute the "body of the view" outside the view definition, like...
    SELECT
    SUBSTR(Customer.Name,1,15) as Customer_Name
    ,Contract_Number
    ,Contract_Type
    ,Start_Date
    ,End_Date
    ,Smallint(Days(End_Date) - Days(Start_Date)) as Days
    ,Hours
    ,DATE(system_begin) as Sys_BEGIN_date
    ,TIME(system_begin) as Sys_BEGIN_TIME
    ,DATE(system_end) as Sys_end_date
    ,TIME(system_end) as Sys_end_TIME
    FROM
    Contract inner join
    Customer on Contract.Customer_ID = Customer.Customer_ID
    inner join
    Contract_Type on Contract.Contract_Type_ID = Contract_Type.Contract_Type_ID
    ;

    Then see the results.

  4. #4
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13
    You've pointed me in the right direction and I think I've got it now. If you want to use SYSTEM_TIME on two different tables in a query on the tables directly, you have to code it like this

    SELECT
    SUBSTR(Customer.Name,1,15) as Customer_Name
    ,Contract_Number
    ,Contract_Type
    ,Start_Date
    ,End_Date
    ,Smallint(Days(End_Date) - Days(Start_Date)) as Days
    ,Hours
    FROM
    Contract FOR SYSTEM_TIME as of '02/02/2012' inner join
    Customer FOR SYSTEM_TIME as of '02/02/2012' on Contract.Customer_ID = Customer.Customer_ID
    inner join
    Contract_Type on Contract.Contract_Type_ID = Contract_Type.Contract_Type_ID


    The optimizer doesn't get confused as it can see which table goes with which SYSTEM_TIME. When that query (without the FOR SYSTEM_TIME options) is embedded in a view, it can no longer work out which table goes with which SYSTEM_TIME; the only way to make that work is to make sure the ROW BEGIN and ROW END column names are system distinct. So I have changed the definition of the CUSTOMER table to this

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    CUSTOMER_ID SYSIBM INTEGER 4 0 No
    NAME SYSIBM VARCHAR 25 0 No
    ADDRESS SYSIBM VARCHAR 125 0 No
    POSTCODE SYSIBM CHARACTER 8 0 No
    COMMENTS SYSIBM VARCHAR 255 0 Yes
    CUSTOMER_SYSTEM_BEGIN SYSIBM TIMESTAMP 13 12 No
    CUSTOMER_SYSTEM_END SYSIBM TIMESTAMP 13 12 No
    CUSTOMER_TRANS_START SYSIBM TIMESTAMP 13 12 Yes


    and the query against the view now works

    select * from contract_details for system_time as of '02/02/2012'

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkGillis View Post
    ,DATE(system_begin) as Sys_BEGIN_date
    ,TIME(system_begin) as Sys_BEGIN_TIME
    ,DATE(system_end) as Sys_end_date
    ,TIME(system_end) as Sys_end_TIME
    From which of the two temporal tables these columns are?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •