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:
- 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.
- Copy and paste the CUST_CUSTOMER and CUST_CRDT_CARD tables from
the production database to the development database.
- In the Administration Explorer, drill down
to the GSDB database, expand its contents, and click the Tables folder.
The Object List is displayed.
- 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.
- 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.
- In the Administration Explorer, find the
GSDBDEV database. Right-click the GSDBDEV database, and click Paste.
The Paste
Database Object wizard starts.
- Complete the information in Paste Database Object wizard.
- Ensure that Paste by using change management is
selected, and click Next.
- 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.)
- 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 from the main menu or press Ctrl+S to
save the changes to the change command script.
- Click Preview Commands to generate the
change commands. The focus of the Change Management Script
Editor shifts to the Commands section.
- Review the generated change commands.
- 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.
- 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.
- 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.
- On the Unload and Reload Information page, complete the following
steps:
- Click CUST_CRDT_CARD and CUST_CUSTOMER to
review the commands that will be used to unload and reload the data.
- 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.
- Repeat the previous step to verify that the reload method for
the CUST_CUSTOMER table is IMPORT, too.
- 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.
- Review the regenerated commands.
- Click Run to deploy the changes to the
DB2 test database catalog. The Deploy Change Commands wizard
starts.
- 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.
- 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.
- 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.