IBM DB2 manual Comparing two database configurations, Building the dependency map

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

248DB2 Deployment Guide