IBM Data Server DB2 - Manual (Page 262)

248 DB2 Deployment Guide
Comparing two database configurations
In our sample Java application, both the source and target database
configurations are contained in Java classes. We assume that we do not have
access to the source database when we run our Java application on the target
environment. To simplify our application, the source configuration is hardcoded.
The target configuration is retrieved dynamically.
The main approach is a table to table comparison. All database objects that are
directly related to a specific table are handled along with the table. These are
objects like primary key constraints, check constraints, and triggers. The
database objects that can span several tables, such as views, function, and
stored procedures, are handled independently of the tables. They are compared
item by item comparison, for instance, comparing the list of stored procedures on
the source database with the list of stored procedures from the target database.
This item-by-item comparison produces three lists:
A list of new items to be created
A list of obsolete items to be deleted
A list of changed items
The lists with new and obsolete items are easy to handle, while the list of
changed items require more effort. If the list contains stored procedures, we just
drop the old implementations and create the new ones. If the list contains tables,
we use what we call a
dependency map to describe the objects such as views and
stored procedures that are related to the table. We use the dependency map to
determine the database objects that have to be deleted before we can change
the table.
Building the dependency map
We build the dependency map by querying the DB2 metadata. Example 5-22
shows how we retrieve the view, function, and stored procedure dependencies
for a specific table,
itso.department .
Example 5-22 Query to retrieve view dependencies for the itso.department table
// View dependencies
SELECT tabname FROM syscat.tabdep WHERE dtype = 'V' AND tabschema = 'ITSO' AND
bname = 'DEPARTMENT'
// Function dependencies
SELECT
r.routinename
FROM
syscat.routines r, syscat.routinedep d
WHERE
r.specificname = d.routinename