Number of Tables in a Database

Recently joined a new shop. Have been out of the game for a bit - last worked on V7. New shop is V9. The current design is a single database that houses over 200 tables/tablespaces - and growing. The resident DBA is telling me that this is ok in V9 - but my research thus far says otherwise. I still find many references (from reliable sources) that indicate that you should have an upper limit/guideline.

Hi,
Im my shop there are 50 databases for aplications (92 with tools databases) and one of the databases has 199 tables/tablespeces. We run version 8 NFM. So far we don't experience any problems (or I'm not aware about them ;) )
Pawel

If you are referring to a single DBMS (subsystem) hosting 200 TSs/TBs - this is nothing and a single subsystem can grow pretty much till 65271 explicitly created DBs or 60000 implicitly created ones (haven?t found the limitation for the TS in a single DB).

For example if you look at any typical SIEBEL installation it will have 1DB:1TS:1TB:100000000IX J relationship with more than 4500 DBs (+tools & utlils) and it runs pretty well .

If I?m not mistaken a size of one DB directly affects the size of it?s descriptor (DBD) and eventually the memory consumption. Thus if there is no need/logic of having all these TSs/TBs you can happily separate them.

Let's make sure we are using the proper terminology. In DB2 z/OS a "database" is a logical structure; as in CREATE DATABASE. I get into so much trouble when I don't clarify. It is not the DB2 subsystem (or set of started tasks).

200 tablespaces in one database is not out of reason.

I have always used the database to logically group tablespaces. Because one can STOP & START a database, one can take an application or a part of an application offline without affecting anything else.

Only started and accessed databases will have their DBD (database descriptor) loaded in the EDM pool.
Dima is correct in that the DBD grows with the size of the database. DB2 can remove a DBD from the EDM pool if needed in order to load a different one. I think she uses a "least used" algorithm to determine (similar to the BPs). As long as your EDM pool is not being constrained, then you may be OK.

I can imagine that having one database for all application data is almost akin to using SMS controlled DASD - even if you have many STOGROUPS they all point to "*" (which lets SMS decide where it goes) rather than specific disk packs. (Is that too much of a reach?)

By the way, I hope that this one big database you are referring to is one that was created expliciity by your shop. I hope you aren't using DSBDB07 as your one database.

Thanks for the input. My database reference was indeed relating to a "logical structure" as Steve notes - not a subsystem. And, yes, it is explicitly defined.

The current design isn't necessarily presenting a problem - yet. This is an application "in development", and growth/size of the DBD was one of my concerns, as we continue to add more objects. My current role is application DBA - developing a new application for an external client. The subsystem is shared with several legacy applications (some of which we are replacing with the application that we are developing) and the data is sensitive, so I have limited access/knowledge to the subsystem in general. Makes it somewhat difficult to analyze some of these things!

In my old shop, we had a single "application" - but we split the data into multiple databases (again, the logical kind) based on subject area - similar to your scenario, Steve. This single database design is a bit worrysome for me - but splitting it up doesn't exactly sound fun either!

I have seen several generic references from "experts" that had various guidelines - anywhere from 20-50 tables/tablespaces per database. Wanted to get some input from a few everyday Joe's.

An other factor which dictates the number of objects in a database (DBD) is the use of dynamic SQL. Because when a dynamic SQL statement is prepared (no hit in the DSC) there are read locks on the DBD. The same is true for any DDL on the Database.
This means that any DDL can lock out dynamic SQL.
That is also the reason why many Siebel (and SAP) shops have a one tablespace per database policy.

We have many old systems and vendor applications that have hundreds of tables per database. However, for new applications developed in-house, we try to adhere to 99 tables per database for ease of maintenance. For example: application XYZ is new. The database name is XYZ0001D.the tablespace names are XYZ0001S - XYZ0099S. The index names are XYZ0001I - XYZ0099I. If multiple test levels are needed, you can do a change all of XYZ00 to XYZ01 and you're ready to create the next level (XYZ0101D, etc). It's the same for rolling changes into multiple levels. If the database grows beyond 99 tables (which most of them do), you can then increment your database (XYZ0002D -XYZ0099D). Each level has a standard qualifier (XYZ00xxD uses XYZ00) which links them all together as if they are 1 huge database.