Scenario: Migrating changes to a development database

As a database administrator you often need to create a development database that is a subset of the production database. By working in the development database, you avoid endangering the production database with untested code.

Many times, database administrators create the development database by backing up the production database and restoring it to the development server. However, you do not typically need an entire production image or all of its data.

In this scenario, you have been asked to migrate just two tables from the GSDB database to the GSDBDEV database. Optim™ Database Administrator has copy-and-paste and drag-and-drop features that let you move database objects from one data server to another. These features are to be used when you know the exact changes that you want to make. In this scenario, you will use the copy-and-paste feature to make these changes.

Requirement: This scenario is based on sample data that is provided in the GSDB database. To actually do the steps in this scenario, you must complete these two tasks:

Complete the following steps to migrate the CUST_CUSTOMER and CUST_CRDT_CARD tables to the GSDBDEV database:

  1. Ensure that connections exist in the Administration Explorer for the GSDB database and the GSDBDEV database.
    • 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.
    • If a connection needs to be connected, right-click the database in the Administration Explorer, and click Connect. In the wizard that opens, complete the details, and click Finish.
  2. Copy and paste the CUST_CUSTOMER and CUST_CRDT_CARD tables from the production database to the development database.
    1. In the Administration Explorer, drill down to the GSDB database, expand its contents, and click the Tables folder. The Object List is displayed.
    2. In the Object List, click the Name column to sort the table names. You can drag the edge of the Name column to the right to increase the width of the column so that you can see more of the table name. Alternatively, you can use the Name Like field to display only those tables that have names that start with the characters CUST.
    3. Click CUST_CRDT_CARD, press and hold the Ctrl key, and click CUST_CUSTOMER. Then, right-click and click Copy in the context-senstive menu that is displayed.
    4. In the Administration Explorer, find the GSDBDEV database. Right-click the GSDBDEV database, and click Paste.

      The Paste Database Object wizard starts.

  3. Complete the information in Paste Database Object wizard.
    1. Ensure that Paste by using change management is selected, and click Next.
    2. Click Copy objects and data, ensure that Copy required database objects is not checked, and click Finish.

      By default, the tables will be created in the GOSALESCT schema, which is the schema in which they reside in the GSDB database. The generated change commands will include a command to create the GOSALESCT schema.

      Tip: If you had wanted to create the tables in a different schema, you could have used the Data Object Editor to create a schema in the GSDB_DEV database and then copied the tables to that schema.

    The change management script is displayed in the Change Management Script Editor. The GOSALESCT.CUST_CUSTOMER and GOSALESCT.CUST_CRDT_CARD tables are listed as objects to be changed.

    When you create a change management script, a Data Design project is automatically created to hold the change management script, models, and other scripts that are related to the change if a project does not already exist. (You can view the Data Design project in the Data Project Explorer.)

  4. In the Objects to be Changed list, click GOSALESCT.CUST_CUSTOMER. Then, in the Properties view, which by default is located below the Change Management Script Editor, click the Table Spaces tab, and change the table space so that the table will be created in USERSPACE1.

    Repeat the same step for the GOSALESCT.CUST_CRDT_CARD table.

    After the changes for the table spaces are made, 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. Click Preview Commands to generate the change commands. The focus of the Change Management Script Editor shifts to the Commands section.
  6. Review the generated change commands.
  7. 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 Management 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 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 GSDBDEV project.

  8. Click Data Options to start the Data Preservation wizard. Unload and reload commands were generated because the data from the tables in the GSDB database are also being copied to the GSDBDEV database. The Data Preservation wizard guides you through the process of customizing the unload and reload commands and specifying which database maintenance commands are generated.
    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, and click Next.
    2. On the Unload and Reload Information page, complete the following steps:
      1. Click CUST_CRDT_CARD and CUST_CUSTOMER to review the commands that will be used to unload and reload the data.
      2. Verify that the reload method for the CUST_CRDT_CARD is IMPORT. If the reload method is not IMPORT, click CUST_CRDT_CARD, and, in the Reload drop-down, select IMPORT.
      3. Repeat the previous step to verify that the reload method for the CUST_CUSTOMER table is IMPORT, too.
    3. On the DB2 Maintenance Commands page, modify which maintenance commands will be generated. Choose not to generate Runstats commands, and 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.

  9. Review the regenerated commands.
  10. Click Run to deploy the changes to the DB2 test database catalog. The Deploy 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 change commands and click Finish to deploy the changes to the actual database catalog.

    Use the Messages section of the Change Management Script Editor and the SQL Results view to monitor the results of the running the commands.

    Remember: If the change commands do not deploy successfully, you can take one of the following two actions:
    • Click Undo to back out the commands that completed before the error occurred.
    • Resolve the issue that caused the error and click Restart to restart the change management script from the command that failed. You can edit the commands, as necessary, in the Change Commands dialog.
    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 reports are stored in the Other Files folder in the same project as your change management script. For more information about reports, see Reports.

Your GSDBDEV database catalog is now updated and includes the two new tables in the GOSALESCT schema.


Feedback