So at this link in IBM's DB2 9.1 z/os manual, which is about table spaces:
IBM Information Management Software for z/OS Solutions Information Center

I find this information:

A table space can consist of a number of VSAM data sets. Data sets are VSAM linear data sets (LDSs). Table spaces are divided into equal-sized units, called pages, which are written to or read from disk in one operation. You can specify page sizes (4 KB, 8 KB, 16 KB, or 32 KB in size) for the data; the default page size is 4 KB. As a general rule, you should have only one table in each database. If you must have more than one table in each database, keep no more than 20 tables in that database.

I am questioning the sentence "you should have only one table in each database". This can't be true! For most databases it would be considered nonsense, after all one basic definition of a database is that it consists of a group of interrelated tables. Did they mean to say, "You should have only one table in each _tablespace_ .. up to a recommended maximum of 20 tables per _tablespace_"?

Does anyone have any recommendations on how many tablespaces are recommended within a DB2 z/os version 9 database? Our current database has about 110 tablespaces and about 130 tables. We will be trying to restructure our tablespaces so there is only one table per tablespace. But is it a bad idea to have nearly 150 tablespaces in our database? The total data size is under 3 GB.

Thanks for your advice!

For DB2 z/OS it is usually recommended to have 1 table per tablespace.

The quote above about one table per database is probably a typo. However a DB2 z/OS database is completely different than a DB2 LUW database. In DB2 for z/OS, a "subsystem" has one DB2 system catalog, and multiple databases (the catalog itself consists of multiple databases). In DB2 LUW, the catalog is at the database level.

But one table per database is obviously not correct.

ngarris, Marcus is correct. It is a typo. The sentence should (probably) be:

"As a general rule, you should have only one table in each table space . If you must have more than one table in each table space, keep no more than 20 tables (or table spaces if they are 1 to 1) in that database."

My rule is to always have one table per table space and only 10 table spaces per database. (10 is a goal, I could have a few more or less and I definitely do not go over 20.)

The reason for one table per table space has to do with a Load Replace. When you do a Load Replace for a table that is in a table space with other tables, the first thing it does is to delete all rows from the table space (repeat, the table space). That means all the other tables in that table space will have 0 rows after the load of the one table is complete. There are ways to deal with this but it is more trouble than it is worth.

The reason for 10 tables / table spaces has to do with DDL manipulation. When you process DDL, a lock on the DataBase Descriptor (DBD) is required. This means that nothing else can be using any object in the database when you are trying to process your DDL and get the lock on the DBD. The fewer objects, the less chance of contention. Also, it is a good idea to keep the DBD size down so it doesn't take as long to access the information it contains.

I know that the OP is talking about DB2 z/OS, but I'd just like to reiterate: there is no such rule for DB2 LUW. There, you use different tablespaces if you want to use different buffer pools and/or different page sizes. Different buffer pools make sense if the access behavior for data in tables varies, e.g. random access on some tables vs. table scans on other tables.

Stealth DBA: Can you say why you try to limit the number of tablespaces in your DB2 z/os database to 10?

Anyone else care to share their rules of thumb?

We have over 100 tablespaces (about 130 tables). The problem is that there are about 4 or 5 master tables to which practically every other table in the database is related. Even though we currently don't have any referential integrity, I would like to add it in the future.

ngarris, there is nothing magical about the number 10. It just so happens, that most of the places I have dealt with have multiple 'subject areas'. Theses are semi-independent sets of tables (there may be some overlap on code tables). Because of this, we don't mix subject area tables together in the same database. I came up with 10 because this allows using 10 databases (with numbers 0 to 9 in the name) with 10 tables per database for a total of 100 tables (using numbers 00-99 in the name) per subject area.

With only (up to) 10 table spaces per database, it keeps the DBD size down (quicker to scan thorough), and there are fewer objects to 'restrict' from the users (thinking more of a development environment here rather than production) while you are doing a quick DDL change. Or, to look at it another way, less chance a user will jump in an get a lock on an object in the database (even a Select will do this) and tie up the DBD.

As with most things in DB2, you situation may vary. If they can be 'grouped' together by some common theme/use/process, it may help defined the various databases you want to create. Just try to stay at or below the 20 that is recommended and keep 1 table per table space.

Thanks Stealth DBA, that's a big help.

As for staying at or below 20 tablespaces per database, do you have a reference for that? (The reference I cited has a typo but probably indicates no more than 20 tables per tablespace which is a different thing entirely ...)

ngarris, the reference contained a couple of typos. It looks like Table Space was replaced by Database in two locations. That would make the recommendation, 1 table per table space and a maximum of 20 table spaces. If you have more than 1 table in a tablespace, the limit should be a maximum of 20 tables (in less than 20 table spaces).

I would never have 20 tables in one table space. I wouldn't have more than one but a case could be made to put a few small, seldom changed code tables in one table space. Just remember if you do a Load Replace on 1 of those tables, ALL of them will have their rows deleted.

A quick search didn't find the reference but if you look up Database Descriptor, there are recommendations to keep its size down. This is main reason for the 20 object limit recommendation.

PS Just for those who missed stolzes' caution earlier, this is pertaining to DB2 z/OS.