To Kal (original poster):
Just thought I'd throw my 2 cents' worth on this issue. I
worked on apps that had several different designs for logging
tables that were only rarely queried on an ad hoc basis.
One used a table similar to what you've described, with eight
parts, one per day of the week and an extra part for special
use. We designed this well before rotating partitions were
available. We designated each part to be assigned to a day of
the week: 1 for Sunday, 2 for Monday....7 for Saturday.
The application used a DAYOFWEEK function, I believe, to get the
numerical value for the day of the week, and that was assigned to a
column and the row was then inserted into the correct part.
We did not have a need to keep an entire week of data in the table,
only 3-5 days. So we used batch jobs at night that determined
the day of the week, then conditionally executed steps that
unloaded the prior day's data to a flat file (saved for a long time
and could be used for ad hoc research) and then did a load replace
dummy on the next day's part. The job ran after midnight, so
if the job ran on a Tuesday, for example, it would unload Monday's
data and clear out Wednesday's part. The online application
was accessing (writing to) the current day's part, in this example
An important part of this design is that we created no indexes
on these tables. There was no need for indexes for inserting
rows (no need to check for uniqueness), and we didn't want to
impact performance of our heavy inserts by having indexes that
would support rare ad hoc queries. The ad hoc query
performance was not a high priority.
Another design we used was to have a separate table for each day
of the week...same table name, but different qualifier. This
makes coding the application a little more tricky because it has to
supply the correct table qualifier. We used similar batch
jobs to unload the prior day's data and clear out the next day's
The above two design examples do not meet your requirements for
keeping the data in DB2 for 7 days, but I thought you might want to
consider these designs as options.
Yet a third design we used was for apps that needed to retain
the logging data for several months (but less than one year), using
the concept of "disposable" tables. We created either monthly
or weekly tables; the monthly tables had 5 parts (one per
week) (or were non-partitioned if the volume of data was low
enough) and the weekly tables had 31 parts (one per day). The
tables actually had the month (or month & week) as part of the
table name, and the application had to determine the current month
(or month and week) and dynamically create the the table name prior
to executing SQL against the table. These were web apps that
used dynamic SQL anyway, so that part was not difficult.
We then had a series of batch jobs to maintain the tables.
Mid-month, we ran jobs to create the next month's tables (we'd read
in template JCL and DDL and write out JCL and DDL with the correct
month and/or week, then execute the generated JCL, which executed
the generated DDL). The DDL would drop the table before
recreating it (meaning drop last year's January table prior to
creating this year's, for example). We also had jobs that
would unload the data and archive it at the end of each month, and
we'd run Reorgs on a weekly basis to compress the data (no good way
of copying the compression dictionaries since we were starting out
fresh each month or week). We were able to keep the data for
12 months in DB2 (longer in our unloaded archives), so the data was
available for research and ad hoc queries; some parts of the
application also needed to read prior month's data. There was
no need for any type of purging utility as we simply dropped the
tables when they were no longer needed, that's why we considered
In this design, if the online application was going to read the
data, we would create necessary index(es), always as DPSI. If
not, we would not create any indexes. Again, no need for indexes
for data that is write-only, if there is no need to check
uniqueness, and this really improves insert performance.
I hope this provides some food for thought.