| The design of DB2 objects also can be tuned for performance. If changes to DB2 tables, columns , keys, or referential constraints are required, however, the application logic usually must be changed also. Retrofitting application code after it has been coded and tested is not simple. Several tuning opportunities do not affect application code. When multiple tablespaces are assigned to a DB2 database, locking of the DBD in the DB2 Directory occurs when DDL ( ALTER , CREATE , or DROP ) is issued for an object in that database. This effectively freezes all access to objects defined to that database. TUNING STRATEGY When a high degree of object alteration, creation, and removal occurs in a DB2 database, avoid placing critical production tables in the tablespaces in that database. If they are already in that database, consider moving them to a separate database. This does not involve any application programming changes, but DB2 utility parameters that access tablespaces (such as DBNAME.TSNAME ) might need to be changed. |
Also, if performance is severely degraded, consider denormalization. Several techniques for denormalizing DB2 tables are discussed in Chapter 5. Be sure to specify proper performance-oriented parameters for all DB2 objects. For an in-depth discussion of these, refer to Chapter 5. A synopsis of these parameters is provided in Table 28.2. Table 28.2. Coding DDL for Performance | DB2 Object | Performance-Oriented DDL Options | | Database | Limit DDL against production databases. | | Tablespace | In general, use segmented tablespaces. Partition tablespaces with very large tables. Partition tablespaces to take advantage of parallelism. Segment tablespaces for mass delete efficiency. Consider simple tablespaces if you need to intermix rows from multiple tables. Specify CLOSE NO . Specify LOCKSIZE PAGE to enforce page-level locking and eliminate lock escalation. Use LOCKSIZE ROW only rarely to enforce row-level locking. Specify LOCKSIZE ANY to let DB2 handle locking. Specify LOCKSIZE TABLESPACE for read-only tables. Specify free space to tune inserts and delay page splits . | | Table | In general, specify one table per tablespace. Do not specify an audit parameter unless it is absolutely necessary for the application. Avoid FIELDPROC s, EDITPROC s, and VALIDPROC s unless they are absolutely necessary for the application ”consider triggers instead. Specify WITH RESTRICT ON DROP to inadvertent drops . Use DB2 referential integrity instead of application referential integrity. Use SEQUENCE objects instead of IDENTITY columns to assign sequential values to a column. Use check constraints and triggers instead of application logic to enforce column data values. | | View | Do not use one view per base table. Use views to enforce security. Use views to enforce join criteria. | | Alias | Use aliases as globally accessible synonyms. | | Index | Create indexes for critical SQL predicates. Index to avoid sorts. Specify CLOSE NO . Specify free space to tune inserts. Cluster the most frequently used index. |
|
