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:
- Create a connection to the database.
- Specify the database object that you want to change .
- Identify objects that will be impacted by the change that you
want to make.
- Have a change management script to construct, contain, and track
the changes created.
- Generate commands to apply the changes to your database.
- Preserve your data by using unload and reload commands.
- Create maintenance commands such as flush package cache, RUNSTATS,
REBIND, and REORG.
- Deploy the changes to the database.
- 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:
- 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 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, specify the details, and click Finish.
- Find the table to change.
- In the Administration Explorer view, expand
the contents of the GSDB database.
- Click the Tables folder.
- 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.
- 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.
- Change the data type of the UNIT_COST column in the CUST_PRICE
table.
- In the Objects to be Changed list in the Change
Management Script Editor, click CUST_PRICE.
- 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.
- 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.
- 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.
- Assess the objects that might be affected by changing the CUST_PRICE
table.
- 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.
- 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.
- 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.
- Review the commands that were generated.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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 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.
- 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.