Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    5

    Unanswered: Use of Partitions

    I have 7 different tables - all same in structure but each one has a check constraint for the day of the week. I have view over all 7 tables, which does a union all over 7 tables. The load process inserts into the "view" and the check constraint directs the data into the correct table as per the check constraint.

    Can this be achieved by using one table that is partitioned 7 times so I can get rid of the view and the need for several tables ?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have DB2 V9.1, then you can use the new range partitioning feature that it has instead of UNION ALL views.

    DB2 V8 does have hash partitioning (DPF) but I don't think that is what you want.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Posts
    5
    Marcus - I have been struggling with the syntax for this since yesterday. I am not sure if I am doing this correctly...

    PARTITION BY RANGE (DATE)
    STARTING DAYOFWEEK(DATE) ENDING DAYOFWEEK(DATE) EVERY 1 DAY

    ..does not work

    i suppose all i want is 7 partitions and with every mondays data going in part 1 etc

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you'll need to create a separate column populated with the value of DAYOFWEEK(DATE) and partition by that column.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2007
    Posts
    5
    Could I attack this a different way ? Just partition by the date, and every 7 days detach the oldest partition?

Posting Permissions

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