Scenario: Making basic database object changes with a change plan

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.

Before you begin

This scenario is based on sample data that is provided in the GSDB database. To do the steps in this scenario, you can download the database and install it on your system by following the instructions in GSDB sample database.

In this scenario, you are 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.

In this scenario, you will:

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.
    • If a connection does not exist, on the Administration Explorer toolbar, click the drop-down button for New and click New Connection to a Database. In the wizard that opens, complete the details and then click Finish.
    • 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 then 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.
      Tip: To find the table more easily, enter CUST in the Tables Name Search field. Only tables that have a name that start with the characters CUST are displayed.
    Figure 1. Example of selecting the Tables folder in the Administration Explorer and using the Tables Name Search field in the Object List to find the CUST_PRICE table
    A graphic that shows selecting the Tables folder in the Administration Explorer and using the Tables Name Search field in the object list to find the CUST_PRICE table
  3. If a change plan toolbar is displayed in the Object List, close the currently active change plan. On the change plan toolbar, click the Icon image that closes the change plan. (Close change plan) icon.
    Tip: A change plan toolbar is displayed only if another change plan is open for the GSDB database. You do not want to add your changes to this change plan. You want to use a new change plan for the your changes to the CUST_PRICE table.
    Figure 2. Example of the Object List with a change plan toolbar that shows that GSDB Canada rollout is the active change plan
    A graphics that shows an example of the Object List with a change plan toolbar that shows that GSDB Canada rollout is the active change plan
  4. Right-click the row for the CUST_PRICE table, and click Alter. A new change plan is automatically created with a default name and becomes the active change plan in the change plan toolbar. Any changes that you make to the CUST_PRICE table are added to this change plan while this change plan is active.

    The icon in the Object List for the CUST_PRICE table is changed to a Alter object icon (Alter object) icon to indicate that the table is being altered.

    Figure 3. Example of an automatically created change plan becoming the active change plan in the change plan toolbar
    Graphic of the a new change plan being created and being the active change plan in the change plan toolbar for the change to the CUST_PRICE table
    Tip: You can change the name of the change plan to make the name more meaningful. You can also create a change plan manually instead of having one created automatically. When you create a change plan manually, the plan is initially empty. You need to make the change plan active and then add your changes to the change plan.
  5. Change the data type of the UNIT_COST column in the CUST_PRICE table.
    1. 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 restores the view to its original size.
    2. For the UNIT_COST column, double-click the data type field and then use the drop-down box to change the data type from DECIMAL(19,2) to INTEGER.
      Figure 4. Example of changing the data type of the UNIT_COST column for the CUST_PRICE table in the Properties view
      Graphic of an example of changing the data type of the UNIT_COST column for the CUST_PRICE table in the Properties view
  6. Assess the objects that might be affected by the objects that you changed.
    1. In the change plan toolbar, click the linked number in the Icon image that shows the number of changes in the change plan(Show the plan changes) icon. The changes for the change plan are listed in the Object List. The Impacted Objects column shows the number of objects that might be affected by changing the CUST_PRICE table.
      Figure 5. Example that shows one change is currently in the change plan and the change that impacts 13 objects
      Graphic that shows that one change is currently in the change plan and the change impacts 13 objects
    2. Right-click the row for the change to the CUST_PRICE table and then click Analyze Impact.
    3. Use either the model diagram or model reports view that is displayed to assess the objects that are impacted by the object that you are changing.

      When possible, IBM® Data Studio automatically generates commands to alter the impacted objects. In some cases, commands for the impacted objects cannot be automatically generated, and you must manually alter the impacted object. For objects that you have to alter manually, you can find the impacted object in the Object List, right-click the object, and click Alter. Then, change the object's attributes in the Properties view.

  7. When you are satisfied with the changes that are included in the change plan, review and deploy the change plan.
    1. On the change plan toolbar, click the Review and deploy changes (Review and deploy changes) icon to open the Review and Deploy dialog box.
      Figure 6. Example of the Review and Deploy dialog
      Graphic that shows an example of the Review and Deploy dialog box
    2. Review the generated DDL. Notice that DDL was automatically generated to alter the impacted objects. You can click Cancel to return to the Object List to make any further changes.
    3. Ensure that the Save data check box is selected. The CUST_PRICE table must be dropped and re-created to change the data type of the UNIT_COST column from DECIMAL to INTEGER. Therefore, you want to preserve the data.

      If you change the location of where the data is saved, click Refresh DDL to update the displayed DDL.

    4. Click Advanced Options, and ensure that the Generate undo commands check box is selected. Select the maintenance commands that you want generated.
    5. Click Run, and then click Finish.
    Tip: To schedule the command script to run at a specific time, you can click Schedule to start the Job Manager and schedule the job. To use the Job Manager, your IBM Data Studio client must be configured to access the Data Studio web console.
  8. Check the results of running the commands against the database catalog in the SQL Results view.

Your database catalog is now updated and includes the changes that you specified. If you decide that you want to back out the change that you made to the database, you can run the undo command script that was generated.


Feedback