Suppose that you want to make changes to two tables, EMPLOYEE and
MANAGERS, in the test database. Specifically, you want to make the information
in the EMPLOYEE table more comprehensible and easier to access. At the moment,
the ADDRESS column in the EMPLOYEE table contains the street, city, state,
and zip code where an employee lives. You want to split the information in
the ADDRESS column into four separate columns: STREET, CITY, STATE, and ZIPCODE.
Also, you want to add an index, NAME_INDEX, to the MANAGERS table to help
improve performance.
DB2® Change Management Expert will
help you:
- Create a Data Design project
- Identify which database objects you want to change
- Create a deployment script to specify the change
- Implement those changes on a model
- Identify which views, functions, procedures, and triggers will be rendered
inoperative by the change and which ones will still be operative after the
change
- Use the deployment script to generate SQL to apply the changes
- Preserve the data by using export and import commands
- Deploy the changes to the test database
The following steps show you how to use DB2 Change Management Expert to
change the columns in a table and to add an index to a table:
- Create a database connection for the test database by opening the Database
Explorer view, right-clicking in the view, and clicking New Connection.
The New Connection wizard starts. Enter information about the test database,
including the user ID and password. Select the version of DB2 that you want
to use and specify the JDBC driver class location. Click Test Connection to
verify that the connection has been created successfully, and then click Finish.
Tip: You can also create database connections from the Deployment Script
wizard when you create a deployment script.
- Create a deployment script to handle the changes. Deployment scripts are
sets of changes that you want to deploy to a database at a point in time.
When you create a deployment script, the Deployment Script wizard creates
the base and target models, which are representations of what the test database
will look like before and after the changes, respectively. The Deployment
Script wizard can also automatically create a DB2 Change Management Expert project
to hold the deployment script, models, and other scripts that are related
to the change if a project does not already exist.
- From the main menu bar, click .
The Deployment Script wizard starts.
- Enter change_scenario as the name of the project. If
a project by that name does not already exist, DB2 Change Management Expert will
create one for you.
- Name the deployment script inplace_deploy.
- Select Use Existing Connection and select the target
database, which is the test database.
- Select the schemas that you want the models to have.
- Optional: Specify names for the base and target models. Name the
base model TEST_BASE, and name the target model TEST_TARGET.
- Click Finish. DB2 Change Management Expert creates
the base and target models of the test database and the deployment script.
The Deployment Script Editor is displayed.
The Deployment Script Editor
is your central resource for managing your change. The four main areas on
the Overview page of the deployment script contain the following information
and actions:
- Deployment Information
- Deployment Script Contents
- Verifying
- Deploying
The tabs in the Deployment Script Editor organize the database change
management process into tasks. For example, you can work with the undo commands
that generated to reverse your changes from the Undo tab.

Figure 1. The Overview page
of the Deployment Script Editor for inplace_deploy.deployxml
- Add the new columns to the EMPLOYEE table in TEST_TARGET.
- In the Deployment Script Editor, on the Data Models tab,
click Edit Target Model. The Data Model Editor is displayed
with information about TEST_TARGET.
The Data Model Editor provides you with
a hierarchical view of the objects in a database model. You can use the Data
Model Editor to view and change the objects in a model.
- Double-click the schema in which the EMPLOYEE table is located and select
the EMPLOYEE table. You can view the columns in the EMPLOYEE table by clicking
the Columns tab in the Properties view.
Tip: Instead
of drilling down through schema objects in the Data Model Editor, you can
use Ctrl+f or to
search an open model.
- Click New in the Properties view. Name your column STREET.
- Repeat the process to create the other three columns, CITY, STATE,
and ZIPCODE.
- In the Properties view, select the ADDRESS column and click the Delete icon. DB2 Change Management Expert deletes the ADDRESS
column.
- Add the new index, NAME_INDEX, to the MANAGERS table.
- Find and select the MANAGERS table. Right-click the table and click . The
Properties view displays the information about the new index.
- On the General tab in the Properties view, rename
the index to NAME_INDEX.
- On the Details tab, define the columns in and the
attributes for the index.
- Save the target model.
- Generate the change commands to apply to the target database. In the Deployment
Script Editor, on the Change Commands tab, click Generate
Change Commands. The Generate Change Commands wizard starts.
- Complete the steps in the Generate Change Commands wizard. DB2 Change Management Expert automatically
identifies if your changes require data preservation commands or DB2 maintenance
commands and will provide the appropriate wizard pages.
In this scenario,
you are changing EMPLOYEE from a table with one column for the address information
into a table with four columns for the address information. Therefore, you
need export and import commands to preserve your data, and you need to map
the column translation. For example:
CREATE TABLE EMPLOYEE (NAME CHAR(26), ADDRESS CHAR(67))
is changed to:
CREATE TABLE EMPLOYEE (NAME CHARACTER (26), STREET CHARACTER (30),
CITY CHARACTER (30), STATE CHARACTER (2), ZIPCODE DECIMAL(5,0))!
- On the DDL Generation Options page, verify that the Generate
delta DDL, Enable Undo, Enable
Data Preservation options are checked. Enable Undo generates
the commands that are necessary to undo your changes.
- 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 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. Set the Customize field for the entry to
Yes so that you can later customize the SELECT statement in the export command.
- On the Customize Export Commands page, modify the SELECT statement so
that the query extracts the data for the ADDRESS column in four substrings
with definitions that correspond to the four new columns for STREET, CITY,
STATE, and ZIPCODE.
- On the Customize Import Commands page, verify the mapping of the export
columns to the import columns. The substrings for the ADDRESS field should
map to the four new columns for STREET, CITY, STATE, and ZIPCODE.
- 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.
- In the Deployment Script Editor, click the Overview tab.
Click Deploy Changes to the Target Database. DB2 Change Management Expert deploys the changes
to the database catalog.
Your database catalog is now updated and includes the changes that you
specified.
The Last Action field displays Deployed.