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 Generate DDL icon.
When it generates correctly, 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 commands. Notice that the commands were 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 Generate undo commands and Save
data check boxes are 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 commands that are displayed.
- Click Advanced Options and select the maintenance
commands that you want generated.
- Select the deployment option and then click Finish.
Tip: You can also schedule the generated commands to run at
a specific time with the Job Manager. To create
and schedule a job with 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.