You are asked to promote changes that were made to a very large
development database that contains a large number of tables to the formal
test database. As the following figure shows, this task requires you to determine
the differences between the development system and test system, and to change
only those objects that are different.
Figure 1. The process of promoting changes from one database
environment to another
Specifically, the EMPLOYEE table of the development system has been updated
with a new column, OFFICENO, and the DIVISION column has been removed. You
must identify these changes and apply them to the test database.
The following steps show you how you can use
DB2® Change Management Expert to:
- Create a project to manage the resources for migrating the changes
- Create models of the development and test databases, including creating
a base model of the test database for fallback purposes
- Identify the differences between the development and test database models
and move the changes that are identified in the development database to the
test database
- Create the DDL to apply the changes to the test database
- Finally, deploy the changes to the test database
Create a project by opening the Data Project Explorer view,
right-clicking in the view, and clicking . In the New Project wizard,
select Data Design Project and follow the steps throughout
the wizard. 
- Create a database connection for the development database by opening the
Database Explorer view, right-clicking in the view, and clicking New
Connection. The New Connection wizard starts. Enter the information
about the development database, including the user ID and password. Click Test
Connection to verify that the connection has been created successfully,
and click Finish.
- Create a model of the development database. Models are local representations
of a database at a particular point in time, and the development database
will serve as the source for the changes.
- Click . The New
Physical Data Model wizard starts. Complete the steps in the wizard
to create the model.
- In the Destination folder field, enter the name
of the project that you created in step 1. In the File name field,
type Development. Indicate the type and version of the development
database by selecting a type from the Database menu
and a version from the Version menu. Select Create
from reverse engineering, and click Next.
- Select Database as the source, and click Next.
- Select Use an existing connection, select the connection
that you created in Step 2, and click Next.
- Enter the user ID and password for the development database, and click Next.
- Select the schemas that you want to include in the model, and click Finish.
A physical model of the development database is created.
- Create a deployment script to handle the changes to the test database.
When you create a deployment script, the Deployment Script wizard creates
base and target models of the test database. These models are representations
of what the test database will look like before and after the changes, respectively.
In a later step, you will change the target model of the test database and
then generate the actual DDL commands that are necessary to implement the
changes.
- In the Data Project Explorer view, right-click the project and click .
The Deployment Script wizard starts.
- Enter a name for the script in the Script name field,
and click Next.
- Select Use an existing connection, select the connection
to the test database, and click Next.
- Select the appropriate schemas, and click Next.
- Enter test_base in the Base Model Name field
and test_target in the Target Model Name field.
Click Finish.
A deployment script and two models of the test database are created.
You can use the base model version of the test database for fallback purposes,
for historical analysis, or for auditing. The Deployment Script Editor is
also displayed.
- Synchronize the structure of the test database model with the development
database model by finding the differences between the models and changing
the target test database model.
- On the Data Models tab of the Deployment Script
Editor, click Add.
- In the Source Models selection dialog, select the development database
model, and click OK . The model is displayed in the
Define Source Models field.
- Select the model, and click Migrate. The Comparison
Editor is displayed with the development database model on the left and the
test database target model on the right.
Tip: Double-click on
the Comparison Editor tab to maximize it. Another double-click will minimize
the window.
- Use the down arrow in the toolbar to tab through the differences between
the two models.
In this scenario, the Comparison Editor shows that the EMPLOYEE
table of the development database has been updated with a new column, OFFICENO,
and the DIVISION column has been removed.
- Move or apply the changes to the target test database model by clicking
the right arrow when a difference is selected. For example, you can select
OFFICENO and click the right arrow to add the column to the test database
model.
Tip: Apply the changes in order.
- Click to save your changes. Close the Comparison Editor.
- Verify the changes that you are making to the test database by comparing
the base model and target model of the test database. The base model represents
the test database before changes, and the target model represents the database
after the changes. From the Deployment Script Editor, click Compare
Base and Target Models to display the Comparison Editor and view
the differences between the models.
Tip: You can also compare
your models by using options outside of the Deployment Script Editor. From
the Data Project Explorer view, you can select two models, right-click, and
click .
- Generate the change commands that will implement your changes. From the Change
Commands tab of the Deployment Script Editor, click Generate
Change Commands. The Generate Change Commands wizard
starts. Complete the steps in the wizard:
- On the DDL Generation Options page, verify that the
correct changes will be made. Ensure that Enable Undo is
checked so that commands to undo your changes, if necessary, are generated.
- On the Specify File Information page, specify a data
file location for the export and import commands. You must specify the full
path. Specify your data file format and accept the default export and import
file names.
- On the Columns tab of the Customize Import Commands page,
check Map Columns and drag and drop the columns from
the import table to map the relationship between the export columns and the
import columns.
- On the Mapping Tables page, there is a mapping entry
that identifies the EMPLOYEE table as the table from which you will export
data and then later into which you will import data. Ensure that the Customize
field is set to Yes so that you can later customize
the mapping of the columns for the import command. You can also override the
default Load and Unload providers here.
- On the Customize Export Commands page, click Next to
continue. You do not need to customize the export command.
- On the Customize Import Commands page, verify the
mapping of the export columns to the import columns and correct the column
mappings to adjust for the DIVISION column being dropped from the middle of
the table.
- On the DB2 Maintenance Command page, check whether
you want to generate commands to preserve the authorization on objects, to
rebind packages, to generate runstats, or to flush the package cache. No options
are selected by default.
If you choose to rebind packages, the schema for
which the packages should be rebound are selected by default on the Select
Schema page.
- Click Finish to generate your change commands.
You can view the change commands on the Change Commands page of the Deployment
Script Editor.
- Deploy your changes to the DB2 test database catalog. Click Deploy
Changes to the Target Database.
When you deploy the changes, DB2 Change Management Expert will check to see
if the test database has changed since you created the base model of the test
database. If the database has changed, you need to refresh the base model
and regenerate the change commands.