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:
- Define the changes to make.
- Use a change plan to contain the database object changes.
- Identify objects that will be impacted by the changes that you
are making.
- Generate the commands to apply the changes to your
database from the change plan.
- 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.
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 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.
- 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.
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
- If a change plan toolbar is displayed in the Object
List, close the currently active change plan. On the change
plan toolbar, click the
(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
- 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 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
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.
- Change the data type of the UNIT_COST column in the CUST_PRICE
table.
- 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.
- 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
- Assess the objects that might be affected by the objects
that you changed.
- In the change plan toolbar, click the linked number in the
(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
- Right-click the row for the change to the CUST_PRICE table and
then click Analyze Impact.
- 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.
- When you are satisfied with the changes that are included
in the change plan, review and deploy the change plan.
- On the change plan toolbar, click the
(Review and deploy changes)
icon to open the Review and Deploy dialog box.Figure 6. Example of the Review and Deploy dialog
- 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.
- 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.
- Click Advanced Options, and ensure that
the Generate undo commands check box is selected.
Select the maintenance commands that you want generated.
- 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.
- 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.