This database design combines various features of the DB2 database system to facilitate roll-in and roll-out of data with continuous update requirements.
This design is for applications with the following characteristics:
- Continuous updates occur all day long (which prevents performing ALTER/Add to attach a partition).
- Queries frequently access a certain day.
- Table partitioning on day results in too many partitions (for example, 365 days times 3 years).
- Roll-out occurs weekly or monthly (typically on a reporting boundary).
 | Best Practice
Recommended database design:
To facilitate the roll-in of data, specify a single-dimension MDC on day (see the section "Features of MDC that benefit roll-in and roll-out of data").
To facilitate the roll-out of data, specify a table partition range per week or month. This provides the same time dimension as MDC but at a coarser scale.
Applications with long running reports might not be able to drain queries for the execution of the DB2 LOAD utility. The best practice in this case is to use the LOAD utility to rapidly load data into staging tables. Then populate the primary tables using an insert with a sub-select.
|
