Scenario: Changing in place

Show Me
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:

  1. Create a Data Design project
  2. Identify which database objects you want to change
  3. Create a deployment script to specify the change
  4. Implement those changes on a model
  5. Identify which views, functions, procedures, and triggers will be rendered inoperative by the change and which ones will still be operative after the change
  6. Use the deployment script to generate SQL to apply the changes
  7. Preserve the data by using export and import commands
  8. 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:

  1. 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.
  2. 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.
    1. From the main menu bar, click File > New > Deployment Script. The Deployment Script wizard starts.
    2. 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.
    3. Name the deployment script inplace_deploy.
    4. Select Use Existing Connection and select the target database, which is the test database.
    5. Select the schemas that you want the models to have.
    6. Optional: Specify names for the base and target models. Name the base model TEST_BASE, and name the target model TEST_TARGET.
    7. 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.
      Start of change
      Figure 1. The Overview page of the Deployment Script Editor for inplace_deploy.deployxml
      View of the deployment script editor and resources for this scenario
      End of change
  3. Add the new columns to the EMPLOYEE table in TEST_TARGET.
    1. 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.

    2. 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 Main menu > Edit > Find/Replace to search an open model.
    3. Click New in the Properties view. Name your column STREET.
    4. Repeat the process to create the other three columns, CITY, STATE, and ZIPCODE.
    5. In the Properties view, select the ADDRESS column and click the Delete icon. DB2 Change Management Expert deletes the ADDRESS column.
  4. Add the new index, NAME_INDEX, to the MANAGERS table.
    1. Find and select the MANAGERS table. Right-click the table and click New > Index. The Properties view displays the information about the new index.
    2. On the General tab in the Properties view, rename the index to NAME_INDEX.
    3. On the Details tab, define the columns in and the attributes for the index.
  5. Save the target model.
  6. 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.
  7. 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))!
    
    1. 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.
    2. 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.
    3. 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.
    4. 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.
    5. 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.
    6. 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.

    7. Click Finish to generate your change commands. You can view the change commands on the Change Commands page of the Deployment Script Editor.
  8. 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.

Start of changeThe Last Action field displays Deployed.End of change

Related tasks
Copying databases using a deployment script


Feedback

Copyright IBM Corporation 2006, 2007. All Rights Reserved.