May 05, 2009

Controlling Implicit Database Creation

DB2 V9 delivered new features that make it much easier to port applications to DB2 for z/OS. One of these features is the implicit creation of a database for the create table statement when an explicit database name isn't given.

By allowing implicitly created databases, IBM is going against the industry norm, which is that the database and table space must follow specific naming standards. However, IBM has been listening to application vendors who've told them how difficult it is to port their applications to DB2 for z/OS given these rules.

The DDL to create tables for products developed on the distributed platform doesn't contain an explicit database and table space name. In most cases the DBA installing the product on z/OS must edit this DDL before the install.

The name of the implicitly defined database is DSNxxxxx (where xxxxx is a numeric value). The value of xxxxx can be between 1 and 60,000, which gives you the name DSN00001 through DSN60000.  To help maintain the numeric value, IBM uses a user-defined sequence object, SYSIBM.DSNSEQ_IMPLICITDB. When a create table statement is invoked, a database name is built based on the next available sequence.

In accomodating application vendors, IBM has frustrated DBAs. DBAs really like having control of DB2 object creation, so they're unhappy with this new feature. And, as more customers upgrade to DB2 9, the requests to provide a way to control the number of implicit databases have increased. 

In response, IBM just released APAR PK62178. This enhances implicit database management in DB2 9. There are two primary changes. First, the default maximum number of implicit databases is reduced from 60,000 to 10,000. The other enhancement is to allow for the MAXVALUE parameter of the sequence to be changed using the ALTER SEQUENCE statement.

For example, to restrict the number of implicit databases to 1 to simulate the old DSNDB04 default database, set MAXVALUE to 1.

                                    MAXVALUE 1 ;

APAR: PK62178