![Overview](images/Appl-Nav_01.gif) ![Administration](images/Appl-Nav_02.gif) ![Performance](images/Appl-Nav_03.gif) ![Recovery and Replication](images/Appl-Nav_04.gif) ![Application Management](images/Admn-Nav_05.gif)
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.
![](images/db2mosaic.jpg)
|