Temple Associates has designed a large number of DB2 databases at both the physical and logical level. The logical database designed is usually taken to third normal form using the client's proprietary data analysis standards (entity and attribute determination). Once the logical design has been completed the physical database is then constructed from the logical model.

When the DB2 physical database is designed several things must be considered. For instance for a physical OS/390 DB2 database the following questions will need to be answered:

• How will the tables be physically ordered (clustered)?

• How will the primary keys be generated?

• What additional indexes will be needed to ensure optimum performance?

• Which tables will need to be physically partitioned?

• Are indexes needed on all foreign keys?

• Which tablespaces and indexes will need their freespace and freepages tuning?

• Which segment size is appropriate for the segmented tablespaces?

• Is any de-normalisation needed for performance reasons?

• Which tablespaces could benefit from data compression?

• Are there any 'hotspots' in the database and how can these be alleviated?

• Is there a requirement for DB2 Triggers or Stored Procedures?

Sometimes a third party product such as Erwin can be used to control the forward and reverse engineering from the logical to the physical model. If a third party product is not available then another method must be used to control the production of the physical DDL (data definition language).

The physical database cannot be designed in isolation. Databases themselves do not perform, on-line transactions, batch programs and ad-hoc SQL queries perform and these must be taken into account when designing the physical database. Increasingly clients are moving towards 24 x 7 database availability that brings its own problems and challenges.