DB2 Databases

At the top of the DB2 object hierarchy is the DB2 database. Physically, a DB2 database is nothing more than a defined grouping of DB2 objects. A database contains no data, but acts as a high-level identifier for tracking other DB2 objects. The START and STOP commands can be issued at the database level, thereby affecting all objects grouped under that database.

Each DB2 database also has a DBD , or database descriptor , associated with it. A DBD is an internal structure used by DB2 to identify and manipulate DB2 objects defined to a database. DBDs are stored in the DB2 Directory and are loaded into the EDM Pool whenever an object in the associated database is accessed.

Logically, a database should be used to group like tables. You can do this for all tables in an application system or for tables in a logical subsystem of a larger application. It makes sense to combine tables with similar functions and uses in a single database, because doing so simplifies DB2 security and the starting and stopping of the application table spaces and indexes.

One database per logical application system (or subsystem) is a good starting point. As a general rule, though, limiting the number of objects defined to a database is smart, because this limits the size of the DBD. As the number of table spaces defined to a single database approaches 40 to 50, consider creating another database for subsequent database objects. More table spaces than this usually increases the difficulty of monitoring and administration.

When DDL is issued to CREATE , ALTER , and DROP objects in an existing database, the DBD for the affected database will be modified. For DB2 to modify the DBD, a lock must be taken. A DBD lock will cause contention , which can result in the failure of the DDL execution. If the DDL is submitted when there is little or no activity, however, application users might be locked out while the DDL is being executed. An X lock will be taken on the DBD while the DDL executes. For very active databases, there might not be a dormant window in which a lock of this kind can be taken. This can cause undue stress on the system when new objects must be added ”a good reason to limit the number of objects defined to a single database.

An additional consideration is the size of the DBD. A DBD contains a mapping of the table spaces, tables, and indexes defined to a database. When a request for data is made, the DBD is loaded into an area of main storage called the EDM pool . The DBD should be small enough that it does not cause problems with EDM pool storage. Problems generally will not occur if your databases are not outrageously large and your EDM pool is well-defined . As of DB2 V6, loading DBDs into the EDM pool is less troublesome because the DBD can be loaded in 32K chunks and contiguous EDM pool pages are not required.

DB2 V8 separates the EDM pool into separate storage areas as follows :

The main EDM pool for managing CTs and PTs in use, SKCTs and SKPTs for the most frequently used applications, and cache blocks for your plans that have caches.

The EDM DBD cache for the DBDs in use and DBDs referred to by the SKCTs and SKPTs for the most frequently used applications.

The EDM statement cache for the skeletons of the most frequently used dynamic SQL statements, if your system has enabled the dynamic statement cache.

Separating DBDs from other EDM pool structures removes the possibility of inefficient DBDs causing problems for other plans and packages.

However, if you CREATE and DROP a lot of objects in a single database without running MODIFY RECOVERY or REPAIR to "clean up" the DBD, it can become too large to manage effectively in the EDM pool.

For a further discussion of DBDs and their effect on the EDM pool, see Chapters 22, "The Table-Based Infrastructure of DB2," and 28, "Tuning DB2's Components."

Furthermore, whenever a change is made to the DBD, DB2 must log the before and after image of the DBD. Such changes therefore tend to overburden log processing as the DBD's size increases.

Database Guidelines

The following guidelines apply to the creation and management of DB2 databases. By following these rules of thumb you can help to assure that you are creating efficient DB2 databases.

Specify Database Parameters

Specify a storage group and buffer pool for every database that you create. If you do not define a STOGROUP , the default DB2 storage group, SYSDEFLT , is assigned to the database. This is undesirable because the volumes assigned to SYSDEFLT become unmanageable if too many DB2 data sets are defined to them.

If you do not specify the BUFFERPOOL parameter, BP0 is used for the table spaces created in the database. As of DB2 V6, the INDEXBP parameter should be coded to specify the default buffer pool to use for indexes created in the database. If you do not specify INDEXBP on the CREATE DATABASE statement, the index buffer pool will default as defined on the installation panel DSNTIP1 . The default for user indexes on the DSNTIP1 panel is BP0 .

Depending on shop standards, defaults can be desirable, but explicitly coding the buffer pool is highly advisable in order to avoid confusion. This is especially so because it is a good rule of thumb to avoid placing user data in BP0 . In-depth buffer pool guidelines can be found in Chapter 28.

NOTE

Actually, a good standard rule of thumb is to explicitly code every pertinent parameter for every DB2 statement. DB2's default values are rarely the best choice, and even when they are, the precision of explicitly coded parameters is preferable for debugging and tuning situations.

Avoid Use of DSNDB04

The default DB2 database is DSNDB04 . DSNDB04 is created during installation and is used when a database is not explicitly stated in a table space CREATE statement, or when a database and table space combination is not explicitly stated in a table CREATE statement. I recommend that you never use DSNDB04 . Objects created in DSNDB04 are hard to maintain and track. To limit the use of DSNDB04 , grant its use only to SYSADM s.

An additional caveat regarding use of the default database ”the REPAIR DROP DATABASE statement cannot be used on DSNDB04 .

CAUTION

Some organizations choose to use DSNDB04 for QMF users to create objects. Even this use is discouraged. It is better to create a specific database for each QMF user needing to create objects. These databases can then be used, managed, and maintained more effectively without affecting other users.

Be Aware of the Impact of Drops on DBDs

When an object is dropped, the related entry in the DBD is marked as logically deleted, but not physically deleted. Certain types of changes, such as removing a column, reordering columns , or changing a data type, necessitate dropping and re-creating tables. Each time the table is dropped and re-created, the DBD will grow. Very large DBDs can result in -904 SQLCODE s specifying the unavailable resource as the EDM Pool (resource 0600 ).

To reduce the size of the DBD, you must follow these steps:

1. REORG the table spaces for tables that have been dropped and re-created. The log RBA recorded in SYSCOPY for this REORG will indicate to DB2 that the dropped tables are no longer in the table space.
   
   
2. Run MODIFY RECOVERY to remove the old image copy information for the dropped table. The preferred method with the least amount of down time is to run MODIFY RECOVERY DELETE AGE(*) . This will shrink your DBD and delete all old SYSCOPY and SYSLGRNX information.
   
   
3. Run an image copy for each table space to ensure recoverability.