Miscellaneous DDL Guidelines

Miscellaneous DDL Guidelines

This section contains guidelines that are not easily categorized. They provide SQL guidance from an overall perspective of DB2 development.

Avoid Using DDL in an Application Program

Do not issue DDL from an application program. DDL statements should be planned by a database administrator and issued when they cause the least disruption to the production system.

When DROP , ALTER , and CREATE statements are used, DB2 must update its system catalog tables. These statements also place a lock on the database DBD being affected by the DDL. This can affect the overall performance of the DB2 system. When DDL is issued from an application program, DB2 object creation is difficult to control and schedule potentially causing lockout conditions in production systems.

It is okay to use declared temporary tables in your programs, because executing a DECLARE for a temporary table does not require DB2 to access the system catalog.

Plan the Execution of DDL

Because of the potential impact on the application system (such as locking, new functionality, or new access paths), schedule the execution of DDL statements during off-peak hours.

Strive for Relational Purity

Learn and understand the relational model and let your design decisions be influenced by it. Assume that DB2 eventually will support all features of the relational model and plan accordingly . For example, if a procedural method can be used to implement outer joins, favor this method over the implementation of physical tables containing outer join data. This provides for an orderly migration to the features of the relational model as they become available in DB2.

Favor Normalized Tables

Taking all the previous suggestions into account, avoid denormalization unless performance reasons dictate otherwise . Normalized tables, if they perform well, provide the optimal environment and should be favored over tables that are not normalized.

Maintain Standard Libraries

Create standard libraries for BIND parameters, utility JCL, utility parameters, VSAM IDCAMS delete and define parameters for user -defined VSAM table spaces, GRANT and REVOKE DCL, and DDL for all DB2 objects.

To maintain these libraries, ensure that all subsequent alterations to DDL are reflected in the DDL stored in the standard library. For example, if a table is altered to add a new column, be sure that the CREATE DDL table in the standard library is modified to also contain the new column. Because this task is time-consuming and error-prone , you should consider purchasing an add-on product that can generate DDL from the DB2 Catalog. Having such a product negates the need to store and maintain DDL in a standard library. For information on these (and other) types of add-on tools for DB2, consult Part VII.

Adhere to the Proliferation Avoidance Rule

Do not needlessly proliferate DB2 objects. Every DB2 object creation requires additional entries in the DB2 Catalog. Creating needless tables, views, and so on, causes catalog clutter ”extraneous entries strewn about the DB2 Catalog tables. The larger the DB2 Catalog tables become, the less efficient your entire DB2 system will be.

The proliferation avoidance rule is based on common sense. Why create something that is not needed? It just takes up space that could be used for something that you do need.