Scenario: Basic change management

As a database administrator you might be asked to make database changes to make information more comprehensible and easier to access, to improve performance, or to accommodate application changes.

In this scenario, you have been asked to change the data type of the UNIT_COST column in the CUST_PRICE table. This table is in the GOSALESCT schema of the GSDB database.

You will use Optim™ Database Administrator to complete the following steps:

  1. Create a connection to the database.
  2. Specify the database object that you want to change .
  3. Identify objects that will be impacted by the change that you want to make.
  4. Have a change management script to construct, contain, and track the changes created.
  5. Generate commands to apply the changes to your database.
  6. Preserve your data by using unload and reload commands.
  7. Create maintenance commands such as flush package cache, RUNSTATS, REBIND, and REORG.
  8. Deploy the changes to the database.
  9. Create a Summary of Changes report and a Deployment report.

Requirement: This scenario is based on sample data that is provided in the GSDB database. To actually do the steps in this scenario, you can download the database and set it up on your system using the directions at http://publib.boulder.ibm.com/infocenter/idm/docv3/topic/com.ibm.sampledata.go.doc/topics/download.html.

Complete the following steps to make the requested change to the GSDB database:

  1. Ensure that a connection exists in the Administration Explorer for the GSDB database.
    1. If a connection does not exist, on the Administration Explorer toolbar, click the drop-down button for New and click New Connection Profile. In the wizard that opens, complete the details, and click Finish.
    2. If a connection needs to be connected, right-click the database in the Administration Explorer, and click Connect. In the wizard that opens, specify the details, and click Finish.
  2. Find the table to change.
    1. In the Administration Explorer view, expand the contents of the GSDB database.
    2. Click the Tables folder.
    3. In the Object List, find the CUST_PRICE table. To find the table more easily, enter CUST in the Name Like field. Only tables that have a name that start with the characters CUST are displayed.
  3. Right-click the row for the CUST_PRICE table, and click Alter. A new change management script is automatically created, and the change management script is displayed in the Change Management Script Editor.
  4. Change the data type of the UNIT_COST column in the CUST_PRICE table.
    1. In the Objects to be Changed list in the Change Management Script Editor, click CUST_PRICE.
    2. In the Properties view, click the Columns tab to display the column information.
      Tip: You can double-click on the title of the view, Properties, to maximize the view. Double-clicking again, minimizes the view.
    3. For the UNIT_COST column, double-click the data type field, and use the drop-down box to change the data type from DECIMAL(19,2) to INTEGER.
    4. In the Working with Objects section of the Change Management Script Editor, click File > Save from the main menu or press Ctrl+S to save the changes to the change command script.
  5. Assess the objects that might be affected by changing the CUST_PRICE table.
    1. In the Objects to be Changed list, click CUST_PRICE. Any other objects that are affected by changing the CUST_PRICE table are displayed in the Impacted Objects list.
    2. Include impacted objects in the change management script. Use the Alter and Drop buttons to move the impacted objects to the Objects to be Changed list. If you use the Alter button to move an impacted object, you can click the object and make any necessary changes to the object in the Properties view.
  6. Click Preview Commands to generate the change commands to apply to the GSDB database.

    A dialog is displayed that indicates that an error exists in the commands. Click No. In this scenario, you will fix the errors later. The focus of the Change Management Script Editor shifts to the Commands section.

  7. Review the commands that were generated.
  8. Optional: Click View Change Report to review the Summary of Changes report. (You might need to scroll to the right in the Commands section of the Change Command Script Editor to see the View Change Report button.) The report opens in the editor area. Click the links in the navigation frame on the left side of the report to review the various sections of the report. Then, close the report.

    Optim Database Administrator automatically generates this report that describes the changes that you want to make to the database, the impact from those changes, and the actions that need to be taken due to the changes that you have modeled. This report is stored in HTML files in the Other Files folder in the GSDB project.

  9. Click Data Options to specify your data preservation options and to correct the error that was detected in the generated change commands.

    Optim Database Administrator automatically determines whether your changes require data preservation commands or DB2 maintenance commands and provides the Customize Data Preservation wizard to allow you to change the generated change commands.

    In this scenario, you changed the data type of a column from DECIMAL to INTEGER, which requires the CUST_PRICE table to be dropped and then re-created. Therefore, you need UNLOAD and RELOAD commands to preserve your data.

    1. On the Specify Unload and Reload File Information page, specify a data file location for the UNLOAD and RELOAD commands. You must specify the full path. Accept the default methods that are used to unload and reload data. Click Next.
    2. On the Unload and Reload Information page, the Problems section indicates that a data type mismatch exists between the UNIT_COST column in the Unload and Reload tables. Click Auto Cast to resolve the mismatch. Alternatively, you can select the row in the list of problems, right-click it, and click Auto Cast. Click Next.
    3. On the DB2 Maintenance Commands page, you can specify which maintenance commands to generate. Click Finish.

      When you choose to rebind packages, the schema for which the packages should be rebound are selected by default on the Select Schema page.

    The commands are regenerated and are displayed in the Commands section of the editor.

  10. In the Commands section of the Change Management Script Editor, click Run to deploy your changes to the database. The Run Change Commands wizard starts.
  11. Optim Database Administrator checks to ensure that the current base model and the database catalog are the same. Click Next if the check is successful.

    If the check is not successful, you must refresh the base model and regenerate your change commands.

  12. Review the displayed commands, and click Finish to apply the changes to the GSDB database. The Messages section displays status about the commands as they are running and records the final state of running the change script. You can also use the SQL Results view to see the status of each command.
    Tip: If a command does not run successfully, you can click the message number or SQL code that is displayed in the Messages section to get more details. The link for the message or SQL code opens an information center with the detailed information.
  13. After the deployment process is completed, a Deployment report is automatically generated. This report is stored in the Other Files folder in the same project as your change management script. For more information about reports, see Reports.

Your database catalog is now updated and includes the changes that you specified.


Feedback