If you want to move data from one ClearQuest database to another, this article can help. You'll learn how to use the Import/Export tool and the Move function in order to accomplish successfully the complicated, often frustrating task of data migration.

Forrest Xia, Shanghai Globalization Lab, IBM, Software Group

Forrest Xia works at IBM's Shanghai Globalization Lab on GB18030 compliance testing. His main interest is Linux and the interaction between Linux and IBM products. He also spends a great deal of time on J2EE study and software engineering with Rational tools. Forrest can be reached at xiaming@cn.ibm.com.



17 June 2005

As an IBM® Rational® ClearQuest® user, you may need to move data from one ClearQuest database to another, which will allow you to optimize and better maintain your overall change management system. ClearQuest does provide some useful tools to help you achieve your data migration targets, but the process is inherently complicated. If you don't perform the task properly, data migration can cause problems in your system and frustrate both you and your team.

This article provides insight into two general examples of moving data within ClearQuest, illustrating how to use the provided utilities in order to move user data successfully.

Introduction

In ClearQuest, there are two ways to implement data migration. One is to use the Im

port/Export tool, and the other is to use the Move function (in the ClearQuest Maintenance tool and ClearQuest Designer).

The former method is suitable when your goal is data centralization. For example, say you have many defect records stored across several different databases, all of which have the same schema. To be able to manage them in a single, central location, you must first create a new database, and then use the ClearQuest Import/Export tool to finish the process.

Note: When you use the Import/Export tool to migrate data, you need to be aware of one issue: if there are reference dependencies in your source database that you wish to maintain, you need to pay attention to the import order. A good tip is to import reference records (such as IBM® Rational® Adminstrator Project, or RAProject, records) first, and then import your anticipant records (such as Defect).

The latter method is more suitable if you want to upgrade your back-end database. For example, let's say that you have been using Microsoft® Access as your ClearQuest back-end relational database management system (RDBMS). As the scale of your database needs increases day by day, you realize it's time to change the system to improve the performance, and you select DB2. In this scenario, you would use the Move function in the ClearQuest Maintenance tool and ClearQuest Designer to implement your data migration.


General Steps

I. Use the ClearQuest Import/Export tool

As introduced previously, ClearQuest provides a pair of tools, Export and Import, that allow you to move records between different user databases. To move data successfully, follow these steps:

  1. Determine what records you want to move, and then determine what reference these records have.
  2. Export records and their references from the source user database.
  3. Import reference records into your target user database first, then import your intended records.
  4. After importing your records, ensure that there are no errors. If an error has occurred, refer to the import log to resolve the problem. If possible, avoid re-importing records.
  5. Finish data migration and announce to your team that the new user database is ready for use.

Example 1: Using the ClearQuest Import/ Export tool

In the following example you will learn how to use the Export/Import tool to combine defect records from two different user databases into one central user database.

Assume that you have two different ClearQuest databases called def1 and def2, and that they both have the same defect record type. Your plan is to move the defect data from these two user databases into an aggregated user database called def3, allowing you to centrally manage all of your defect data. After reviewing the data you plan to move, you find that there are some records with references to the RAProject in both of these databases.

Given this scenario, here are the steps you would follow to perform your migration:

First, you want to export the records from databases def1 and def2, respectively.

  1. Start the ClearQuest Export Tool. In Step 1, select a connection (for instance, GBDefects as shown in Figure 1), and then click Next.
Selecting a Schema Repository
Figure 1: Selecting a Schema Repository
  1. In Step 2 of the wizard, depicted in Figure 2, enter your admin account and password, select database def1, and click Next.
Selecting a database
Figure 2: Selecting a database
  1. In Step 3, select the record type that you wish to export, then click Next. In this example, shown in Figure 3, the Defect record type has been selected.
Selecting a record type
Figure 3: Selecting a record type
  1. In Step 4, select the fields you will export, and then click Next. Generally, you will export all fields, as illustrated in Figure 4.
Selecting fields to export
Figure 4: Selecting fields to export
  1. In Step 5, specify the path for exported records (c:\temp\def1\... in Figure 5), and then click Next.
Specifying the record path
Figure 5: Specifying the record path
  1. In the final step, specify some export conditions (such as the number of records in every export file), and a field delimiter (such as a comma). Once you've added your conditions, click Finish.
Specifying export conditions
Figure 6: Specifying export conditions

Now it's time to export the reference records (of your defect records). In this example, you need to export RAProject. To do this, we again invoke the ClearQuest Export Tool and repeat the first two steps.

  1. In Step 3 of the ClearQuest Export Tool, under Select a record type, select RAProject (as shown in Figure 7) and then finish the remaining steps of the export tool in the same manner as you did for the defect records.
Figure 7: Preparing to export reference records

You can now export the records of database def2 just as you did for database def1, including all relevant reference records. The wizard steps in which you specify the database name are repeated for clarity in Figures 8 and 9, following.

Selecting def2 as the database from which you will export records
Figure 8: Selecting def2 as the database from which you will export records
Specifying the path for the exported def2 records
Figure 9: Specifying the path for the exported def2 records

Now it's time to import all the records from def1 and def2 into a new database called def3. Before importing the defect records, however, you need to import the correct reference records.

  1. Start the Import tool, select the destination database (CentralDB in Figure 10), and input your admin account/password to logon (Figure 11).
Selecting the destination database
Figure 10: Selecting the destination database
Log on to the new database
Figure 11: Log on to the new database
  1. Import RAProject records first. In step 1 of the Import Tool, depicted in Figure 12, specify the record type RAProject and click Next.
Preparing to import reference records
Figure 12: Preparing to import reference records
  1. In Step 2, specify the path for record data and discarded data as shown in Figure 13, and then click Next. For more details on discarded data, refer to your ClearQuest documentation.
Specifying import paths
Figure 13: Specifying import paths
  1. In Step 3 of the tool, specify the field that contains a unique identifier for importing history or attachment data, then click Next. For the RAProject records in this example, illustrated in Figure 14, the Name field was used as the unique identifier.
Specifying the unique identifier
Figure 14: Specifying the unique identifier
  1. In Step 4, specify the proper field mappings for the Record data between the destination and the source, as shown in Figure 15, and then click Next.
Mapping the import file fields
Figure 15: Mapping the import file fields
  1. When you are mapping the History data in Step 4 of the import wizard, specify the source field to which the unique identifier is mapped. In the example depicted in Figure 16, the record type RAProject is mapped to display_name.
Mapping fields for history data
Figure 16: Mapping fields for history data
  1. When you are mapping the attachment data (Figure 17), leave the values unchanged and click Next.
Mapping attachment data
Figure 17: Mapping attachment data
  1. Click the Import New button and verify that there are no errors. If you do experience an error, ClearQuest will save the non-imported records in discarded files, so you can refer to the log to see why the error occurred, and then go back through the steps using those discarded files to resolve the problem.
Importing the reference records
Figure 18: Importing the reference records

After successfully importing the reference records, you can start to import the rest of your defect records.

  1. Start the ClearQuest Import tool and specify the record type (in this case, shown in Figure 19, Defect), and click Next.
Preparing to import your defect records
Figure 19: Preparing to import your defect records
  1. In Step 2 of the Import tool, specify the paths to your record data, history data, and attachment data (as shown in Figure 20), and click Next.
Specifying your import paths
Figure 20: Specifying your import paths

Figure 20: Specifying your import paths

Note: Because defect records have state values, you will need to specify the field that represents each of those states. More importantly, you need to specify a unique identifier for each record type's history data and attachment data. For defect records, there is a field called old_id that is specifically designed for data migration, as shown in Step 3 of the import tool.

  1. In Step 3 (Figure 21), select the field name that specifies the unique identifier for each record, and then click Next.
Specifying the unique identifier
Figure 21: Specifying the unique identifier
  1. For Record Data in Step 4 of the import tool, map the old_id destination field to the id source field (as shown in Figure 22). This is a key step that will determine whether or not you can import history data and attachment data. After ensuring that this is mapped correctly, click Next.
Correctly mapping record data
Figure 22: Correctly mapping record data
  1. When performing this step for History Data (depicted in Figure 23), map the old_id destination field to the display_name source field and click Next.
Mapping fields for history data
Figure 23: Mapping fields for history data
  1. When mapping the Attachment data (Figure 24), you should also map the old_id field to the display_name field, and then click Next.
Mapping fields for attachment data
Figure 24: Mapping fields for attachment data
  1. In Step 5 of the Import tool, click the Import New button (shown in Figure 25) and then verify whether or not any errors have occurred. If there are errors, refer to the log and review the process step-by-step to resolve any problems. If you only succeed in importing some of your records, you can refer to the log to determine which records were not imported. Then you can use discarded files as record files to import the remaining records.
Importing your defect records
Figure 25: Importing your defect records

Now repeat these steps to import the def2 database records as you just did for def1, and you have merged the two databases into one central database! You can now query the consolidated database (def3) to verify those records, as shown in Figure 26.

Querying the new database
Figure 26: Querying the new database

II. Using the Move function of the ClearQuest Maintenance tool and ClearQuest Designer

The Move function is a powerful tool, you can use it to migrate your data among different databases without knowing the relationship or references of the records. Follow these steps to migrate your data using this method:

  1. Determine your target back-end RDBMS, and then prepare proper databases (including the Schema Repository database and the User database).
  2. Move the User database with the ClearQuest Designer.
  3. Move the Schema Repository database with the ClearQuest Maintenance Tool.
  4. Complete your data migration.

In the following example, you will learn how to change the ClearQuest back-end RDBMS from Microsoft Access to IBM® DB2® using the Move function.

Example 2: Using the Move function to move your defect and schema repository databases

Let's assume that you have a defect database that uses Microsoft Access as the back-end RDBMS, and you would like to migrate the data from Microsoft Access to IBM DB2. To do this, you must first prepare two DB2 databases: in this example, one is called gbsrdb (for your schema repository), and the other is called bdefdb (for your user defect database).

For more information about how to configure DB2 databases to use with ClearQuest, please refer to the article entitled Configuring ClearQuest to use DB2 as your back-end RDBMS.

Begin by moving your user defect database with ClearQuest Designer.

  1. Start ClearQuest Designer, and click Database > Move User Database as shown in Figure 27.
Preparing to move the user defect database
Figure 27: Preparing to move the user defect database
  1. In the Move User Database dialog (depicted in Figure 28), select the user database you want to move, and click Properties.
Getting ready to specify the move properties
Figure 28: Getting ready to specify the move properties
  1. Under Vendor, select DB2, and then enter the database name, your database admin name, and your password, as shown in Figure 29. Click Move to begin the migration process.
Migrating the user database
Figure 29: Migrating the user database
  1. Check the status of the move in the Moving Database dialog window illustrated in Figure 30 to verify that your move was successful.
Verifying a successful move
Figure 30: Verifying a successful move

Having finished moving your user defect database, it's now time to use the ClearQuest Maintenance Tool to move your Schema Repository database.

  1. Start the ClearQuest Maintenance Tool. Under Existing Connections, highlight the connection you want to move, and then click Schema Repository > Move as shown in Figure 31.
Preparing to migrate the Schema Repository
Figure 31: Preparing to migrate the Schema Repository
  1. Select DB2 under Vendor, enter the name of the schema repository database and your admin information, and then click Finish to start the move process (Figure 32).
Figure 32: Finishing the move process
  1. Again, verify that your move was successful by noting the Status field in the ClearQuest Maintenance Tool - Move window shown in Figure 33.
Another successful move
Figure 33: Another successful move

It's just that simple. You have now upgraded the back-end RDBMS for your ClearQuest implementation from MS Access to IBM DB2.


ClearQuest tools for migrating data

As you can see, you can -- with a little care -- consolidate or upgrade your databases in ClearQuest, using the Import/Export tools or the Move function, respectively. This powerful functionality will help you continue to improve your ability to manage defect and change information.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational
ArticleID=85958
ArticleTitle=Data Migration in ClearQuest
publish-date=06172005