Why does DB2 offer one table for a table space?

Question

DB2 documents for DB2 / z v10 have the following snippet in the table space section:

Generally, you should have only one table in each table space.

But in reality this does not give any reason for this.

We have several tables storing historical information based on time in the following lines (significantly 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 collapsed in one day, and the monthly table does the same with the daily data, inserting it into the line with the date YYYY-MM-01 ).

Now it seems to me that these tables are very similar in purpose, and I'm not sure why we want to save them in separate table spaces.

The discount for now is the possibility of combining them into one table, this is an offer I made, but there are complications that prevent this.

What is the point of justifying “one table per tablespace”? What are the exceptions, if any? I suggest that they may be exceptions, as this seems to be a guide rather than a strict rule.

Answer 1

Just a wild hunch ... but perhaps IBM recommends no more than one table per tablespace, because many db / 2 utilities work at the tablespace level. If you put several tables in one tablespace, the utilities will work with all tables as a whole.

For example, backup and restore work at the table space level. You cannot backup / restore individual tables in the same table space. All of them are backed up or restored as a whole. I believe the same applies to other utilities, and possibly for many settings.

Answer 2

Nowadays, the main reason for saving one table in a table space is administrative. Most DB2 utilities work at the table space level. For example, if you execute LOAD REPLACE in the table space for a particular table, then all other tables will be empty, because the first thing LOAD REPLACE does is delete all rows.

So why don't you leave one table in the table space? ". I consider it reasonable and even desirable to include several tables in one tablespace when the table is connected with the fact that one is useless without the other. For example. CustomerTable + NextCustomerIDTable.

Another consideration is the type of table space. Depending on the type of table space created, performance implications may arise when creating multiple tables in the same table space. If you do not use segmented table spaces, scanning the table space will read all the pages in the table space, including pages from other tables. See the “Tablespace Scanning” section here: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.ve%2Fdvnhlpcn_tablescan.htm

Answer 3

This means that they have changed the text in their documentation.

The link provided in the Question now contains the following information:

The number of tables that you must define in the table space depends on the characteristics of the tables:

If a table can become large in size, it is best to place the table in its own table space. This design simplifies the work of tuning and, in particular, tuning the buffer pool. For smaller tables, it is better to use segmented table spaces with multiple tables. This design helps reduce the number of data sets that are needed to manage backup and recovery, as well as the number of data sets that the database system must open and close during a DB2 operation.

It is better to minimize the number of table spaces in each database for the following reasons:

During the execution of data definition instructions, the database system contains an exclusive lock on the entire database until an operation is performed. An exclusive lock performs the following functions: An exclusive lock prevents the simultaneous execution of data definition statements for tables and indexes in the same database. If the dynamic statement cache is disabled (subsystem parameter CACHEDYN = NO), the database system uses database locks to serialize the execution of data definition statements and dynamic SQL statements that access tables and indexes in the database.

If there are fewer table spaces in the database, the number of table spaces is simultaneously blocked. During the execution of the SWORCH stage of the REORG online operations, the database system receives an exclusive lock on the whole database to serialize the execution of the REORG online operations and instructions for determining the data in the tables and indexes in the database.

If the database has fewer tables, the number of tables is simultaneously blocked. Logging for data definition statements is less when there are fewer table spaces in the database.

Answer 4

This is usually due to the fact that performance parameters are generally better for "single table in table space" configurations. For example, the ability to perform limited scan splitting for specific queries if the table is partitioned (which REQUIRES 1 TB per TS).

(But as a mainframe management specialist, I would say that, right?) :-)