This article illustrates how Erik, a DBA at a fictitious company, JK Enterprises, uses IBM Optim Database Administrator to apply changes to the production database in response to requests he receives from his team members. Without Optim Database Administrator, Erik would have to manually write scripts to migrate and test the changes in the test system, which could be both time consuming and error prone. Optim Database Administrator can help Erik perform these complex changes in little time with little effort.
This article uses IBM Optim Database Administrator Version 2.2.3. If you want to follow along with Erik, you need to have Optim Database Administrator installed on your machine. (Download Optim Database Administrator V2.2.3.)
Note: This scenario uses the GSDB database to simulate JK Enterprises's production database. You need to also use the sample database named GSDB. Follow these steps to create the database:
- Get the zip file from the Download section of this article, and extract the GSDB_Database.sql file from it.
- Open a DB2 command window.
- Navigate to the location where you saved the GSDB_Database.sql file.
db2 -td~ -f GSDB_Database.sql
To follow along with the scenario, you need to also create a "test"
database. To do so, use the following command in the db2 Command Line
- db2 CREATE DATABASE GSDBDEV USING CODESET UTF-8 TERRITORY US
Upon launching Optim Database Administrator, connections to the production and test databases appear automatically in the Administration Explorer, as shown in Figure 1. When using these connections for the first time, you are required to enter your user name and password information in order to connect to the databases.
Figure 1. Connections in Administration Explorer
Erik's database change request (scenario overview)
Erik, a DBA at JK Enterprises, receives a request to add mobile numbers to all the customers of the company. In order to accomplish this, he first makes a copy of the production system to a test system where he can make and test his changes. After he tests the changes, he propagates the changes back to the production database. The following steps show how Erik can easily accomplish these tasks using Optim Database Administrator.
Create a schema in the test database
To accomplish the task of making the proposed change request to the production system, Erik first needs to set up a test environment in which he can make his changes without risk of impacting the production system. The change request focuses around a single schema in the production database. To mirror this in the test database, Erik decides to create a schema named CHANGES in the test database GSDBDEV to hold the change he will be making.
To create a schema named CHANGES, perform the following steps:
- Locate the Schemas folder under the GSDBDEV database in the Administration Data Explorer.
- Right-click on the Schemas folder, and choose the option
Figure 2. Create schema in test database
- Select Data Object Editor as the editor to use for the change.
(Note: Don't worry if you don't see this dialog. Just proceed to the
next step. The dialog does not pop-up when the chanage mangement
capability is disabled.)
Figure 3. Data Object Editor is selected as the editor to use
CHANGESin the Name field.
- Click on Preview DDLto show the DDL to be executed.
- Click on Run DDL to create the new schema.
Figure 4. Creating schema CHANGES in Data Object Editor
The CHANGES schema now shows when the Schemas folder is selected, as shown in Figure 5:
Figure 5. CHANGES schema in test database
Create a copy of CUST table in the test database
With the new schema created in the test GSDBDEV database, Erik uses the new copy and paste capability in Optim Database Administrator to make a copy of the CUST table from the production GSDB database into the test GSDBDEV database. The copy and paste action does the following:
- Automatically creates a change management script if one is needed
- Copies the database objects from the production system into the change management editor for manipulating
- Optionally sets up the necessary data preservation commands that copy data associated with the database objects from the production system into the test database
To copy the CUST table from GSDB to GSDBDEV, perform the following steps:
- Navigate to the Tables folder under database GSDB.
- Find the CUST table in the list of tables.
- Right-click on the CUST table, and choose the Copy
Figure 6. Copy action on CUST table
- Navigate back to the GSDBDEV database, select the Schemas folder, and select the CHANGES schema from the list of schemas.
- Right-click on the CHANGES schema, and select the Paste option.
Figure 7. Paste option on CHANGES schema
- Select Paste by using Change Management in the pop-up dialog, and
then click on Next.
Figure 8. Paste Options dialog
- Select Copy database objects and data, then click on
Figure 9. Copy options
Note: It's not always required, or even allowed, in some companies to copy data from production to a test system. If you just want to copy the data objects, select the Copy database objects only option.
Add MOBILEPHONE to CUST table using Change Management Script Editor
Once the paste operation completes, a new change management script editor named GSDBDEV.changexml is displayed within Optim Database Administrator (see Figure 10). This editor contains the CUST table that was pasted into the CHANGES schema.
Figure 10. GSDBDEV.changexml with CUST table
Let's now modify the CUST table by adding an additional column MOBILEPHONE.
To add the MOBILEPHONE column to the CUST table, perform the following steps:
- Select the CHANGES.CUST object in the Change Management Editor's "Objects to be Changed" list, as shown in Figure 11.
- Select the Columns tab in the properties section on the right
side of the editor.
Note: The Columns tab shows you all of the columns currently associated with this table. You can manipulate the columns using this tab.
- Click on the new column icon (a diamond with a plus sign) to create a new column:
- Name the column by changing the default name from Column1 to
MOBILEPHONE,and change the type to varchar with length of 32.
Note: In some situations, the UI does not immediately refresh with the new column information. Click on the General tab, then go back to the Columns tab, and the new column information will be displayed.
Figure 11. MOBILEPHONE column added to CUST table
- Click on the Preview Command button to see the generated
commands for the table paste operation and for adding the new column.
Figure 12. Generated DDL in commands section
While viewing the DDL, if you notice changes that should be made before running the script (such as table space container paths that should be changed), you can perform these changes in the editor, or by modifying the Data Project Explorer model object. Additionally, the Data Options button lets you customize how the data is exported/imported (important especially when dealing with XML data) and what types of additional commands are generated along with the DDL (such as Runstats). If a dialog is presented stating there are data preservation errors, choose the Data Options button, and change the import option to LOAD.
Figure 13. Change import option to LOAD
- Click on the Run button to run the commands. The changes are
now applied to the GSDBDEV database.
Optional: To validate successful execution of the generated commands, look in the Messages Section of the change management script editor.
Erik is now at a point where he can test the changes made to the test GSDBDEV database. Since a new column is being added to the table, all applications that may be impacted by this change should be tested carefully.
Migrate the changes from the test database (GSDBDEV) back to the production database (GSDB)
For the purposes of this article, let's assume Erik has done the necessary testing and feels the changes can be incorporated back into the production GSDB database.
To help manage the changes from the GSDBDEV database into the production GSDB database, Erik creates a new change management script to hold his changes.
To create a new change management script on the GSDB database, perform the following steps:
- Right-click on the Change Management Scripts folder under GSDB, and
select New Database Change.
Figure 14. Create new change management script
A new change management script editor is displayed, as shown in Figure 15. You'll use this editor in the next section.
Figure 15. Change management script editor on GSDB database
Migrate CUST changes to production using the "Compare and Migrate" wizard
To propagate the specific changes from the test GSDBDEV database into the production GSDB database, Erik uses the compare and migrate capability in Optim Database Administrator. The compare and migrate capability allows Erik to visually compare his test database against his production database. He is able to selectively choose which changes/differences between the two and reconcile them as he sees fit. In this case, he will locate the changes made to the CUST table and merge them into the production database.
- Select Change Management > Compare and Migrate
Objects from the top menu bar.
Figure 16. Compare and Migrate Objects menu
- Select the GSDBDEV test database connection, which has the
changes you want to migrate, then click on Next.
Figure 17. GSDBDEV database connection is selected
- Select the Schemas folder, and then select the CHANGES schema as the source schema.
- Click on the Add button to add the schema
CHANGES to the list of objects to compare and migrate, and then
click on Next.
Figure 18. Choose the CHANGES schema as the source
- In the Masks tab, select SCHEMA as the Database
Object, CHANGES as In Mask, and
GOSALESCT as Out Mask, and then click on Add
Figure 19. Masking CHANGES schema to GOSALESCT
- In the Masks tab, select SCHEMA as the Database Object, CHANGES as In Mask, and GOSALESCT as Out Mask, and then click on Add Mask.
- Add objects to the Ignores tab to reduce the number of changes
displayed in the compare wizard:
- Click on the Ignores tab from the Ignore Database Objects drop-down menu.
- Select BPNAME, then click on Add Ignore.
- Repeat the same steps for TABLESPACES, CONTAINER, and AUTHORIZATION.
- After you have selected BPNAME, TABLESPACES, CONTAINER, and AUTHORIZATIONS as database objects to ignore, click on Next.
Figure 20. Add Ignores
- Locate the MOBILEPHONE column under the CUST table in the comparison
editor, click on the Copy from Left to Right icon, then click
on Finish. This migrates the MOBILEPHONE column to the CUST
table in the production database (GSDB).
Figure 21. Locate MOBILEPHONE under CUST table
Figure 21b. MOBILEPHONE column migrated to GSDB database
- The MOBILEPHONE column is now displayed in the object list view of the
change management script editor.
Figure 22. MOBILEPHONE column is shown in the CUST table
- Click on the Preview Commands link to verify changes in the
Figure 23. Generated commands for migrated table and column
- Click on Run to deploy the changes to the GSDB production database.
Once the changes are deployed, you can view your newly deployed changes in the Administration Explorer Explorer by looking at the columm properties of the CUST table.
Figure 24. MOBILEPHONE column in the GSDB production database
Eric has now addressed the request provided by his company. By using Optim Database Administrator, he was easily able to accomplish this task with little manual effort. Optim Database Administrator handles complex situations and contingencies by providing easy-to-use wizards that walk you through every step of the process.
|GSDB sample database for this article||GSDB_database.zip||33KB|
- "IBM Data Studio software: The big picture" (developerWorks, December 2008): Understand how Data Studio Administrator fits in with the rest of the Data Studio portfolio.
- "Understand IBM Data Studio, Version 2 software packaging" (developerWorks, February 2009): Understand Data Studio packaging, including the free administrative capabilities available in the Data Studio Administrator download.
- "What's new and exciting in IBM Optim Database Administrator 2.1" (developerWorks, December 2008): Find out all the new capabilities in Optim Database Administrator.
- "Oops! Restoring your database with Optim Database Administrator" (developerWorks, April 2009): Follow the story of a typical DBA on a not so typical day as he puts the point-in-time recovery capability of Optim Database Administrator 2.1 to the test.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Technology bookstore: Browse for books on these and other technical topics.
Get products and technologies
- Data Studio Administrator: Download the free trial version of Data Studio Administrator for DB2 for Linux, UNIX, and Windows.
- DB2 Express-C: Now you can use DB2 for free. Download DB2 Express-C, a free version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy application.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Optim Team blog: Find advice, hints and tips, and lessons learned from a team of Optim experts and their experiences working with customers around the world.
- Data Studio Community space: Contribute and learn from the central hub of information and conversation about Data Studio and related products and technologies.
- Participate in developerWorks blogs and get involved in the developerWorks community.