Why does DB2 suggest one table per tablespace?

Question

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.

Answer 1

These days the main reason for maintaining one table per table space is an administrative one. Most DB2 utilities work at the table space level. For example if you perform a LOAD REPLACE on a table space for a specific table then all the other tables will be end up empty as the first thing the LOAD REPLACE does is to delete all rows.

So "why wouldn't you keep one table per table space?". I think it's reasonable and even desirable to include multiple tables in a single table space when the table are related to the extent that one is useless without the other. Eg. CustomerTable + NextCustomerIDTable.

Another consideration is the type of table space. Depending on the type of table space you have created there could be performance implications with creating multiple tables in a single table space. If you are not using segmented table spaces a table space scan will read all pages in the table space including the pages from other tables. See "Table space scan" topic here: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.ve%2Fdvnhlpcn_tablescan.htm