Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2012
    Posts
    15

    Unanswered: Data History Management in Partitioned Tables

    Hi,

    I want to create partitioned tables in DB2. Currently i have table in which history is maintained by a date column for each record. No updations occurs in the table just insertions with current date in date column.

    I want to create partitioned table in which the latest record against PK should be maintained in Partition 1 and rest history should be moved to Partition 2.

    For Instance tables is like
    Customer_Id (PK), Customer_Name, Customer_Address, Insert_Date(PK)

    Any ideas on this.

    Thanks,
    Asif

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by syedasiftanveer View Post
    and rest history should be moved to Partition 2.
    That is not possible with partitioned tables. The good-old "union-all" view sound the best solution when you want to move data to a history table.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    Jun 2012
    Posts
    15
    Can you please elaborate...How can i do this with UnionAll

    Thanks,
    Asif

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    create view allhistory as 
    select ...
    from old_data where insert_date < '2013-01-01'
    union all
    select ...
    from current_data where insert_date >= '2013-01-01'
    The WHERE clauses in the view definition will help the DB2 query rewrite to avoid reading the table that's not needed for the query. For example, if your actual query is
    Code:
    select ... from allhistory where insert_date >= current_date - 7 days
    the old_data table will not be read at all.

    Of course, you will need to ensure that you indeed have no data in old_table with insert_date older than '2012-12-31'.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    With version of DB2 you have? DB2 V10 z/OS has bi-temporal tables, so you could have current data in one table and all history in another table.

    Cheers, Bill

  6. #6
    Join Date
    Jun 2012
    Posts
    15
    I am using DB2 9

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by syedasiftanveer View Post
    I am using DB2 9
    That is not enough. You can have db2-luw (linux/unix/windows) or db2 on z (a.k.a. os/390 a.k.a. MVS). So when you mention the server O.S. as well we will get a pretty good impression
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    here is an example. I assume you do only INSERTS & SELECTS on the view:
    Code:
    connect to sample;
    drop trigger databel_access_insert 		 ;
    drop view    databel_access		 	 ;
    drop table   databel_curr  		 	 ;
    drop table   databel_hist  		 	 ;
    --
    create table databel_curr
    		 ( id int not null generated always as identity primary key
    		 , whatever_data01 varchar(32)
    		 , whatever_data02 varchar(64)
    		 , creation_ts timestamp not null default current_timestamp
    		 )
    ---          in curr_data_tablespace
            ;
    
    create table databel_hist
    		 ( id int not null primary key
    		 , whatever_data01 varchar(32)
    		 , whatever_data02 varchar(64)
    		 , creation_ts timestamp not null
    		 )
    ---          in hist_data_tablespace
            ;
    create view databel_access as  ( select * from databel_curr
      		 union all       select * from databel_hist )
    		 ;
    create trigger databel_access_insert instead of insert on databel_access
    referencing new as nw for each row 
    insert into databel_curr  (     whatever_data01 ,    whatever_data02 ) 
                values        (  nw.whatever_data01 , nw.whatever_data02)
     ;
    with del_curr as  ( select * from old table  
      ( delete from databel_curr  where date(creation_ts ) < current_date - 30 days ) )
    select count(*) as moved2histy from final table 
       ( insert into databel_hist  select * from del_curr )
     ;
    Thanks to Tonkuma http://www.dbforums.com/db2/1661551-...t-working.html
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Tags for this Thread

Posting Permissions

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