Scenario: Applying changes from one database environment to another

Show Me
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
The DBA examines the Development database to determine how it differs from the Test database and then applies the changes that were found to the Test database.

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:
  1. Create a project to manage the resources for migrating the changes
  2. Create models of the development and test databases, including creating a base model of the test database for fallback purposes
  3. 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
  4. Create the DDL to apply the changes to the test database
  5. Finally, deploy the changes to the test database
  1. Start of changeCreate a project by opening the Data Project Explorer view, right-clicking in the view, and clicking New > Project. In the New Project wizard, select Data Design Project and follow the steps throughout the wizard. End of change
  2. 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.
  3. 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.
    1. Click File > New > Physical Data Model. The New Physical Data Model wizard starts. Complete the steps in the wizard to create the model.
    2. 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.
    3. Select Database as the source, and click Next.
    4. Select Use an existing connection, select the connection that you created in Step 2, and click Next.
    5. Enter the user ID and password for the development database, and click Next.
    6. Select the schemas that you want to include in the model, and click Finish.

    A physical model of the development database is created.

  4. 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.
    1. In the Data Project Explorer view, right-click the project and click New > Deployment Script. The Deployment Script wizard starts.
    2. Enter a name for the script in the Script name field, and click Next.
    3. Select Use an existing connection, select the connection to the test database, and click Next.
    4. Select the appropriate schemas, and click Next.
    5. 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.

  5. 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.
    1. On the Data Models tab of the Deployment Script Editor, click Add.
    2. In the Source Models selection dialog, select the development database model, and click OK . The model is displayed in the Define Source Models field.
    3. 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.
    4. 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.

    5. 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.
    6. Click File > Save all to save your changes. Close the Comparison Editor.
  6. 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 Compare with > Each other.
  7. 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:
    1. 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.
    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 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.
    4. 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.
    5. On the Customize Export Commands page, click Next to continue. You do not need to customize the export command.
    6. 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.
    7. 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.

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


Feedback

Copyright IBM Corporation 2006, 2007. All Rights Reserved.