General guidelines for running Rational Quality Manager online migration on production

Authors: JingQian, JamesBognar, WilliamChen
Build basis: IBM Rational Quality Manager 4.0.6 and later

IBM Rational Quality Manager (RQM) v4.0.6 introduced a new online migration feature which allows you to migrate large Quality Manager (QM) data to 4.0.6 and newer versions while the old production server is still online. The online Migration is built for LARGE repository data with extremely high volume manual test script states and manual execution scripts. You generally do not have to use online migration for production upgrades in most cases; the traditional offline migration is still the recommended migration path. Please consider each guideline carefully when you must perform online migration for large enterprise RQM server upgrades.

General guidelines for running RQM online migration

• Backup production QM database
• Repo Tools new commands
• When to run an online migration and examples
• Known issues and workarounds
• Gathering logs for troubleshooting
• Q&A

Backup production QM database

Before running the online migration commands, always take a full backup of your production QM database. If there are issues during the online migration, it is much harder to roll back to a clean state. If a rollback is required on production, you might lose some delta data between the last full backup.

Also, you should immediately contact IBM Rational Support when running into issues with online migration. Do not attempt to run any further commands until you are being instructed by IBM Rational Support.

Repo Tools new commands

Repo Tools now supports these new commands and parameters:

-onlineMigrateEstimate --Find the number of states in the database that need to be migrated.

[teamserver.properties=conf/qm/teamserver.properties] --Path to the teamserver.properties file of the currently running server.
[logFile=repotools-qm_onlineMigrate.log] --Path to the log file.

-onlineMigrate --Perform online migration on live server.

[numStatesPerRun=100] --Number of item states to process per iteration.
[priority=<1-100> (default=50)] --Percentage of time online migration should be running.
[noPrompt] --Do not prompt before performing migration.

-onlineMigrateRevert --Revert database changes made from an online migration.

-onlineMigrateStop --Signals the online migration process to finish the current transaction and exit gracefully.  This is the preferred
approach to stopping online migration to make sure database locks are not held in limbo. 

-validateMigration --Validate the qm online migration result. 

[validateLevel=<1-3> (default=1)] --The detailed level to validate, default is 1, 2 means also compare the states count per manuscript,
3 means also compare the steps counts per current state of the manualscript.

For other Repo Tools commands, you can run repotools-qm.bat or repotools-qm.sh to view detailed information.

When to run an online migration and examples

IBM Rational has tested online migration against large databases internally. Please consult the Rational Quality Manager online migration test matrix for more details.

Known issues and workarounds

1. Check if statistics are up to date against these online migration tables:

• REPOSITORY.ITEM_STATES
• REPOSITORY.ITEM_CURRENTS
• REPOSITORY.ITEM_TYPES
• LINKS.AUDITABLE_LINK

If the above tables and statistics are out of date, the online migration could result in poor performance.

2. ORA-01555: snapshot too old: rollback segment number xx with name "systemValue" too small.

The exception is occurring on the following query that's used to calculate the %complete values which involves a table scan against the ITEM_STATES table (which is going to be large).

select count(*), {ITEM_TYPE_DBID}
from {REPOSITORY.ITEM_STATES}
where {ITEM_TYPE_DBID} in (%s)
and {KEY_UUID} not like '-%%'
group by {ITEM_TYPE_DBID}

The problem is fully recoverable and a DBA needs to increase the size of the rollback segment (undo) size, or extending tablespace on the small segment to resolve the issue. Once the DBA has fixed the cause of the ORA-01555 problem, the online migration can be restarted and it should pick up where it left off.

3. Rational Quality Manager online migration fails with ORA-00955 error

Technote 1680939: Rational Quality Manager online migration fails with ORA-00955

4. Failed to add tables to the database. The database has not been modified.

This error can occur when running online migration without noPrompt parameter:

2014-07-27 07:19:47,815 CRJAZ2523I Setting the global server rename state to false and the validation state to false.
2014-07-27 09:30:43,708 Failed to add tables to the database. The database has not been modified.

Adding the noPrompt parameter to the online migration command can resolve the error. Then the addTables command can begin to run as part of the online migration process.

Gathering logs for troubleshooting

When running into issues with the online migration, try to provide the following information for Support and development:

1. Full online migration command syntax - You can get this information from console window when you run any online migration command.

2. onlineMigrateEstimate.log - This log by default resides in the online migration server logs directory. It is called "onlineMigrateEstimate.log" and it contains the number of states in the QM database that need to be migrated.

3. repotools-qm_onlineMigrate.log - The repotools-qm_onlineMigrate.log contains online migration events and related activities. It resides in the online migration server logs directory by default.

4. onlineMigrateStateProcessed.log - This log is specific to the online migration process and can provide more details on the process time and activities. It also contains timestamps of each state migration. It resides in the online migration server logs directory by default.

5. How to enable logging for online migration?

You can enable online migration logging to determine what tables might need to be optimized. For example, the CONTENT_STORAGE table is used heavily during the online migration. To check that other online migration tables statistics are up-to-date, you can turn on both log4j.logger.sqlTxLogger and log4j.appender.file.Threshold to debug mode with the below steps:

1) Stop the online migration via "repotools-qm -onlineMigrateStop"
2) Edit [install dir]\conf\repotools_log4j.properties
3) Add the lines:

# Turn on debugging of all SQL
_log4j.logger.sqlTxLogger=DEBUG_
Also update the following property:
_log4j.appender.file.Threshold=DEBUG_

4) Save and restart the online migration
5) Once the repotools_onlineMigrate log updates with a time estimate again which can take some time, let it run for 30 minutes to capture enough SQL tracing for a full anlaysis.
6) Repeat #2 to stop migration
7) Collect the online migration log(s) (repotools-qm_onlineMigrate) and states processed log(s) (onlineMigrateStatesProcessed) and send to IBM
8) Undo the edits made in #4
9) Restart the online migration

Q&A

1. How accurate is the estimated completion time from the *onlineMigrateEstimate command?*

When you run the onlineMigrateEstimate command, the estimated completion time is the best estimate based on the available data. The fluctuations are unavoidable because of several unknown variables (e.g. database server load, complexity of testcases over time, etc...). During performance testing, we noticed that old statistics on the ITEM_STATES and ITEM_CURRENTS tables resulted in very long run times. You want to ensure the statistics are up-to-date on those tables if you have not already done so.

The /server/OnlineMigrateSettings.cfg file contains the priority of the process. This can be changed while online migration is running. You may want to bump it up to a larger value such as '90' during off peak hours. Beyond that, there's really not much that can be done to improve the time. A lot of effort was put into making online migration perform as efficiently as possible.

If you have some large databases in need of migration, and there is a very large amount of data to be migrated, the fact is that it's chugging along migrating data in the background while the server is online so that the amount of time the server is offline is minimized, all while not noticeably impacting performance.

2. How much can we gain by increasing the prioerity value when running the onlineMigrate command?

The priority is equal to the percentage of time that the online migration runs. You can specify a percentage value between 1 and 100. The primary consideration is how much load the database can handle and how responsive the old server is. For example, if the priority value is 10, the task for online migration runs 10% of the time and is idle the remaining 90%. As an example, if the priority value is 100, the migration process will never sleep. Then you should notice some improvement in estimated completion time. So at priority=50, online migration is running half the time and sleeping half the time.

Increasing the priority to 90 may make the application somewhat slower for users, but it's likely not going to be noticeable. It should be noted that offline migration is IDENTICAL to online migration running at priority=100. It's the exact same process except there is no pause between transactions.

You can change /server/OnlineMigrateSettings.cfg to adjust the priority which can be done while the existing online migration is running. This should be dynamic, and DOES NOT require a stop/restart. You know your offpeak time best so a conservative approach would be to take a time slice and run as close to 100 as possible. Since users are impacted (performance alerts indicating that) this would mean that we would be running the migration all the time, and taking a conservative approach to reducing priority during peak times may still be prudent until true impact is understood.

3. What new tables are used by the online migration process?

The following tables are the ones we use during online migration, we do query or insert on these tables.

Existing tables:

REPOSITORY_CONTENT_STORAGE
REPOSITORY_ITEM_STATES
REPOSITORY_ITEM_CURRENTS
REPOSITORY_ITEM_TYPES
LINKS_AUDITABLE_LINK

New tables created for migrated data

PLANNING_EXECUTION_ELEMENT2
PLNNNGMNLXCTNSCRPTSTPLKPLMNT2H
PLANNNG_MNL_XCTN_SCRPT_STP_LKP

When the online migration starts, we query the REPOSITORY_ITEM_STATES to get the total counts of how many states we need to migrate. Then we migrate them by time order, we process the oldest one 1st. Then we cache 50,000 records at a time, process each one ManualScriptSate, read all of its steps, create new ones and put the data into the above related tables.

We commit to database no more than (numStatesPerRun=100) states are processed. So it's important for the DBA to keep these tables statistics up to date to optimize online migration process.

4. How to enable logging for online migration?

See item 4 under Gathering logs for troubleshooting

Related topics: Deployment web home,

External links:

• IBM RQM 4.0.6 New & Noteworthy

Additional contributors: : PaulEllis, RosaNaranjo, DeniseMcKinnon