The DB2 docs for DB2/z v10 have the following snippet in the tablespaces section:
As a general rule, you should have only one table in each table space.
But it doesn't actually provide any rationale for this.
We have some tables storing historical time-based information along the following lines (greatly reduced in complexity but should be enough to illustrate):
Table HOURLY_CPU_USAGE:
RecDate date
RecTime time
Node char(32)
MaxCpuUsage float
primary key (RecDate, RecTime, Node)
Table DAILY_CPU_USAGE:
RecDate date
Node char(32)
MaxCpuUsage float
primary key (RecDate, Node)
Table MONTHLY_CPU_USAGE:
RecDate date
Node char(32)
MaxCpuUsage float
primary key (RecDate, Node)
(the daily table has all the hourly records rolled up into a single day, and the monthly table does the same with the daily data, rolling it up into the row with date YYYY-MM-01).
Now it seems to me that this tables are all very similar in purpose and I'm not certain why we'd want to keep them in separate tablespaces.
Discount for now the possibility of combining them into a single table, that's a suggestion I've made but there are complications preventing it.
What is the rationale behind the "one table per tablespace" guideline? What are the exceptions, if any? I'm assuming they're may be exceptions since it seems very much a guideline rather than a hard-and-fast rule.
Just a wild guess... but maybe IBM recommend not more than one table per table space because many db/2 utilities operate at the level of the table space. If you put multiple tables into one table space then utilities operate on all of the tables as a unit.
For example, backup and restore work at the table space level. You cannot backup/restore individual tables within the same table space. They are all backed up or restored as a unit. I believe the same sort of thing applies to other utilities and probably for many tuning parameters as well.