IBM Support

Database Migration: IBM Engineering Lifecycle Management products

Question & Answer


Question

How do we migrate ELM applications from one database vendor to another in a time efficient manner?

Answer

IBM Engineering Lifecycle Management created new repotool commands called exportConcurrent and importConcurrent, which provide a faster migration of Engineering Lifecycle Management (ELM) application repositories from one database vendor to another.
The new repotools commands are expected to supersede repotools export and repotools import commands and are available for IBM Engineering Lifecycle Management versions 6.0.6 and 6.0.6.1.
Patches that contain the new migration tools:
  • 6.0.6.1 iFix025 or later
  • 6.0.6 iFix026 or later
Currently, supported database migrations are:
  • Microsoft® SQL Server to IBM® Db2
  • Microsoft® SQL Server to Oracle® Enterprise Edition DB.
Currently, supported application repositories that can be migrated:
Prerequisites:
  • A nonproduction test environment with a copy of data from production is needed to run a test migration.
    • The copy of production data needs to be recent enough that you are confident it represents all data in the product repository--for example, within the last few months for teams following established development processes.
    • You need test data for each application and application instance you migrate. For example, if you have two RM servers, you need to do a test migration of each. This statement does not apply to clustered CCM or QM servers, since clustered servers share one database instance.
    • A staging environment with hardware equivalent to production is the most reliable indicator of expected migration time.
  • A user with administrative privileges must perform the migration.
  • The ELM servers need to be offline, so an outage window is required.
  • Migration requires an export and import of all repository data together. There is no support for a partial migration, or for the servers to come online between an export and import.
  • The ELM servers must have sufficient disk space to hold export files. The database data is compressed during export, but it is not the same compression ratio on all databases. A good guideline is to use the size of the database when calculating how much disk space needs to be available for export files.
  • Well-performing hardware is required for high-speed migration.

Preparing Servers

While the optimized repotools migration code was reworked to reduce the outage time for a migration, the operating environment also plays a crucial role in how fast a migration can complete. The new repotools -exportConcurrent and repotools -importConcurrent commands make use of multiple CPU cores and fast disk and network I/O.

Disk

  • If possible, use a local disk for writing and reading export files. Exporting to or importing from a network share or network storage device might limit the processing speed of the migration.
  • Solid State Disks (SSD) are recommended for migration storage. Disk speeds are an important part of migration, both on the ELM server running the repotools commands, and the database server. Refer your ELM server admin and DBA to our article on Disk Benchmarking.

CPU Cores

The optimized migration relies heavily on concurrency to reduce its runtime speed.  Running ELM and database servers with at least 8 - 16 cores is recommended.

System Memory

Running the concurrent db migration repotools commands (-exportConcurrent, -importConcurrent, and -compare) can use much of the system memory. It is important to minimize other processes competing for system resources while executing these commands. On Linux, the Out Of Memory Killer can terminate the concurrent migration processes early. Administrators are then forced to correct the issue by reducing memory usage, and rerunning the terminated command.

Databases

You might not need to modify database-specific settings for all cases of migration.  In general, databases that are tuned for ELM daily use are appropriate to use in the export context.  Import databases must be created with ELM application-specific recommendations in mind, found in the product documentation. 

The creation of the databases on the target database server need to be created according to the product documentation and need to be empty with no tables created.

In practice, we observed that import databases optimize their execution plans based on the large occurrence of writes after -importConcurrent is run. This situation can have a negative impact on query performance during compare or running the ELM server that uses the new database vendor initially.

To clear the execution plan on Oracle®, run:

alter system flush shared_pool;
Db2
To clear the execution plans on Db2, either leave the DB2 AUTO_MAINT job enabled, or do the following to execute runstats in a script:
  1. Connect to the database by using the db2 connect command.
  2. Create a script (like this example): 
    db2 -x "SELECT 'RUNSTATS ON TABLE ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ' AND INDEXES ALL;' FROM SYSCAT.TABLES WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%' ORDER BY TABSCHEMA, TABNAME" > db2_runstats.sql.out
  3. Validate the db2_runstats.sql.out contains all the table names to execute runstats on.
  4. Run the script by using db2 -tvf: 
    db2 -tvf db2_runstats.sql.out > outcomestats.txt
Customers observed some performance gains importing to Db2 by doing the following:
  • Set the memory to be automatic, which allows memory growth:
db2 "update db cfg for [DatabaseName] using DATABASE_MEMORY 5000000 automatic"
  • Increase the bufferpool:
db2 "ALTER BUFFERPOOL IBMDEFAULTBP SIZE [Appropriate size based on avaiable physical memory]"
  • Enabling the statement concentrator:
db2 "update db cfg using stmt_conc off immediate"
  • Increase the heap size:
db2 "update db cfg using APPLHEAPSZ 10000 automatic"
These settings are not meant to be definitive, but can be used as examples to discuss with database administrators and implemented when appropriate.

Migration Task List

  1. Before your test migration, delete unnecessary attachments to reduce space. Orphaned work item attachments can take up space in your database, and increase migration times and complexity. To reduce that risk, you can use the WorkitemAttachmentMigrationUtility. You can also configure EWM to store large attachments outside of the default database.
  2. Before your test migration, perform a -verify to check database integrity. A verify is one way to determine the health of the database BEFORE you begin a migration, and also as a method of determining AFTER whether the migration introduced any problems. To run a verify: 
    <JAZZ_HOME>/server/repotools_[app] -verify level=5
    If any issues are reported, then use Troubleshooting the Verify command or contact IBM Support
  3. Back up the applications to be migrated. When you migrate from one database vendor to another database vendor (for example, Microsoft SQL Server to IBM Db2), it is critical to back up each application database before you perform the migration. It is recommended to store your data for a six-month period after a database migration, providing enough time to ensure that the product is functioning as expected and that no remedial actions are necessary later. Consult the IBM Documentation topic "Backing up and restoring IBM Engineering Lifecycle Management (ELM) applications" for more information on this process.
  4. Increase repotools script heap Xmx to at least 4096M. The migration commands require more than the default 1500M heap space. In internal testing, 4096M has proved to be sufficient, but there could be cases where an even larger value is needed to prevent potential memory contention issues. In repositories with many large multi-gigabyte sized attachments, consider increasing the Xmx value to 8192M. To increase the heap, define the following system environment variable (M must not be appended to the system environment variable): 
    REPOTOOLS_MX_SIZE=8192
  5. Export the data with -exportConcurrent. To perform an export: 
    <JAZZ_HOME>/server/repotools_[app] -exportConcurrent -clean toFile=../export/[app].zip
    Important statistical information about the migration execution can be gathered and saved to the repotools log.  This information can be used by support when troubleshooting a migration.  statsLogFrequency is only required to change the default logging period once every 60 minutes.
<JAZZ_HOME>server/repotools_[app] -exportConcurrent -clean toFile=../export/[app].zip statsLogFrequency=60
If you want to ignore the statistics while exporting the data, need to pass the following parameters:
<JAZZ_HOME>/server/repotools_[app] -exportConcurrent statsEnabled=false -clean toFile=../export/[app].zip
or
<JAZZ_HOME>/server/repotools_[app] -exportConcurrent statsEnabled=no -clean toFile=../export/[app].zip
  1. Check the log file for error messages and runtime statistics. 
    <JAZZ_HOME>/server/repotools_[app]_exportConcurrent.log
  2. Update the ELM server with new database settings. You can either set up a new ELM server installation that uses the same version as your export ELM Server, or use the existing export server. If you reuse the existing server, back up the old teamserver.properties files so they can be used in the -compare tool later on (Example: teamserver-sql.properties). If you set up a new ELM instance, ensure you keep the same URI, by changing the proxy configuration, or installing side by side on the existing server. Update the following properties in the server/conf/[app]/teamserver.properties file to point to the new location. 
     com.ibm.team.repository.db.vendor
 com.ibm.team.repository.db.jdbc.location
 com.ibm.team.repository.db.jdbc.password
  3. Import the data with -importConcurrent. To perform an import: 
    <JAZZ_HOME>/server/repotools_[app] -importConcurrent -clean fromFile=../export/[app].zip

    Important statistical information about the migration execution can be collected and saved to the repotools log. This information can be used by support if troubleshooting a migration is required. Note that statsLogFrequency is only required to change the default logging period once every 60 minutes.

    <JAZZ_HOME>/server/repotools_[app] -importConcurrent -clean fromFile=../export/[app].zip statsLogFrequency=60
    If you want to ignore the statistics when importing the data, need to pass one of the following parameters: 
    <JAZZ_HOME>/server/repotools_[app] -importConcurrent statsEnabled=false -clean fromFile=../export/[app].zip
<JAZZ_HOME>/server/repotools_[app] -importConcurrent statsEnabled=no -clean fromFile=../export/[app].zip
  4. Check the log file for error messages and runtime statistics. 
    <JAZZ_HOME>/server/repotools_[app]_importConcurrent.log
  5. Consult the appropriate database vendor guidance in this document for clearing execution plans, and clear the plans.
     
  6. Important: It is necessary to run compare successfully in a test environment before planning your production migration to ensure there are no discrepancies between the source and target databases.
    • It is not recommended to run compare after your production migration because it could require an outage of the production system longer than is feasible. Using the test system to validate the exported data is all present in the import database, a repotools compare can be used to perform an exhaustive cell by cell comparison of two ELM databases. Running this command requires two teamserver.properties files, one that contains connection information to the old database, and one that contains connection information to the new database. It also requires that both databases are accessible from one ELM server location.
    • IMPORTANT: Do not start any of the ELM applications accessing the source or target databases until the compare completes. If you start any of the servers, changes in the tables occur, creating false positive errors in the compare log.
    • While the compare can run with either database set as source or target, performance is much better when the SQL Server database is used in the source.teamserver.properties argument, and Oracle or Db2 is used in the target.teamserver.properties argument.
    • To run a compare: 
      <EXPORT JAZZ_HOME>/server/repotools_[app] -compare target.teamserver.properties=/opt/IBM/<IMPORT JAZZ_HOME>/server/conf/jts/teamserver.properties
  7. Rerun the verify to check database integrity. After the migration and compare, run the -verify again to ensure no new issues were introduced by the migration. 
    <JAZZ_HOME>/server/repotools_[app] -verify level=5

LQE/LDX Migration instructions:

  1. Follow instructions to perform an LQE backup
  2. Follow instructions to set-up the database.
  3. Shutdown LQE.
  4. Change the JDBC driver for LQE.
  5. Edit dbconnection.properties file for Oracle or Db2.
    • Oracle® Database Example:
    • db.vendor=ORACLE
db.location=thin:lqeDBuser/{password}@//localhost:1521/LQEDB
db.password=lqeDBpassword
    • IBM Db2 Example:
    • db.vendor=DB2
db.location=//localhost:50000/JAZZ:user=db2admin;password={password};
db.password=lqeDBpassword
  6. Start up LQE following instructions to perform LQE Restore

Performance

If the export and import in your test environment takes longer than the time window you have available as an outage, review repotools Statistics Monitoring output and consider ways you can optimize your operational environment.  The Statistics Monitoring tracks useful hardware, software, and system performance information that can be used to compare an environment to a more highly performing setup.  Server ping times between ELM and database machines should be as low as possible.

Some ways to speed up migrations:

  • Disable Anti-Virus software for the duration of the migration
  • Use SSD drives
  • Provide ample RAM and cores for both the ELM and Database servers
  • Add more heap space to the repotools script commands

The threadpool sizes used by optimized migration can also be modified using Java properties defined in the repotools script file:

-Dcom.ibm.team.repository.migration.internal.service.sql.MigrationThreadPool.THREAD_POOL_SIZE=50
(Default is 20)   
-Dcom.ibm.team.repository.migration.internal.service.sql.DBStore.STORE_THREAD_POOL_SIZE=60
(Default is 26. Should always be 6 or more than the number defined in MigrationThreadPool.THREAD_POOL_SIZE. Example if MigrationThreadPool.THREAD_POOL_SIZE is set to 50, DBStore.STORE_THREAD_POOL_SIZE should be set to 56 or higher)
Troubleshooting
Failures
Most issues in the tool will be denoted by an error message of some kind in the repotools command log file.  Some errors will cause the tool to stop running immediately, while other issues can be safely logged and allow the tool to continue, allowing multiple issues to be identified in one run.  If the error message is unclear or does not provide an obvious path to resolution, most information IBM Support will need is contained in the log file. In some cases, the export file can be helpful as well, provided it is not too large for transfer and any private data sharing issues can be resolved.
Specific issues:
  • If rebuilding indices fails because data is not unique, this will require a support case to resolve any duplicate data issues before rebuilding indices and using the server.
  • If you see the error "Results file already exists." when running compare, or "The file already exists. Use the option "overwrite" to overwrite an existing file. when running exportConcurrent. Both the -exportConcurrent and -compare commands can fail if they are run multiple times without clearing out any generated files. Both commands allow the parameter overwrite=true to allow the command to overwrite any existing files.
  • Applications sometimes write log events to the database on service initialization, however this interferes with compare operations. So it is possible to see exceptions like the following in the log; these can be safely ignored:
2021-12-09 08:20:53,328 Unable to log startup event com.ibm.team.repository.common.MigrationInProgressException: CRJAZ1966E Write operations cannot be performed on item type "DBEvent" at this time.  An application migration is currently in progress.
  •  When loading a Project area after a migration, you see a 400 bad request. You need to ensure the Jena indices are configured properly, i.e.
    
com.ibm.team.jfs.index.root.directory=indices
    Do not put the application name in the root directory path.

[{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSPRJQ","label":"IBM Engineering Lifecycle Management Base"},"ARM Category":[{"code":"a8m0z000000CbOfAAK","label":"Jazz Team Server-\u003EDatabase"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0.1;7.0.2"},{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSYMRC","label":"Rational Collaborative Lifecycle Management"},"ARM Category":[{"code":"a8m0z000000CbOfAAK","label":"Jazz Team Server-\u003EDatabase"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"6.0.6"}]

Document Information

Modified date:
05 April 2023

UID

ibm16552594

Page Feedback