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 ?

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.

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

I think you'll need to create a separate column populated with the value of DAYOFWEEK(DATE) and partition by that column.

Could I attack this a different way ? Just partition by the date, and every 7 days detach the oldest partition?