Scenario: Making a simple change to a database

This scenario explains how to make changes to part of a database structure on a development system.

About this task

Specifically, for the EMP table, you want to drop the COMMISSION column and increase the length of the LASTNME column to 45 bytes.
In making these changes, you have the following goals:
  • Ensure that there is a snapshot of the database structure for fallback purposes.
  • For the dropped column, repair any side effects of the change, such as handling inoperative or undefined objects such as packages, views, and triggers.
  • Ensure that data is preserved for the change to the column length.
  • Optimize the database with respect to the changes, such as running RUNSTATS or rebinding where necessary.
  • Capture the changes for auditing purposes.

The following steps show you how you might use Change Management to make these changes and achieve your goals:

Procedure

  1. Generate operations to track the change in Change Management.
    Create a version scope of the human resources database. You want to define a version scope because you want to create a snapshot (or base version) of the database structure after the changes are made. The version scope defines the objects that should be in the base version.
  2. Modify the length of the LASTNME column and drop the COMMISSION column.
    1. Find and select the EMP table.
    2. Issue the ALT command to change the table. If there are any pending changes to the table, specify whether to implement your changes based on the assumption that the pending changes have been performed or that they have not been made and your change should supersede them. In this scenario, assume that there are no pending changes.
    3. Type over the length of the LASTNME column to increase the length to 45.
    4. Issue the D line command to delete the COMMISSION column.
  3. Identify the impact that the changes have. To assess the impact of increasing the length of the LASTNME column and dropping the COMMISSION column:
    1. Type the REL primary command to see the related objects.
    2. Select each related object individually and determine if any changes are required because of the change in length to LASTNME or for COMMISSION being dropped. In this example, assume that a view is impacted by the dropped column.
  4. Repair the side effects for the change. To fix the view:
    1. Issue the A line command to change the view.
    2. In the edit session that is displayed, remove the predicate from the view and save the edit session. The new definition of the view will be included as part of the change.
    3. Issue the CONTINUE command to finalize the changes to the table and the view.
  5. Register the change in the Change Management database.
    Change registration occurs in this scenario because Change Management is enabled and required. To register the change, specify an owner and name for the change.
  6. Analyze the change. To analyze the change:
    1. Go to the Change Management main menu and display the list of changes.
    2. Issue the analyze command for the change.
    3. Submit the batch job that Db2® generates to perform the analyze.
      The batch job produces a report of the changes that will be made and generates a WSL that will make the changes.
  7. Run the change and capture a snapshot of the database structure after the change is complete. To run the change:
    1. Go to the Change Management main menu and display the list of changes.
    2. Issue the run command for the change, specifying that a new base version of the database structure should be created after the changes are made.
    3. Submit the batch job that runs the WSL that applies the changes.