Repository tools command to recreate incorrectly removed queryable table data

Edit online

Use the repairQueryableTables command to fix data issues caused by CleanupUnreferencedCollectionRecordsAsyncTask.

Purpose

The CleanupUnreferencedCollectionRecordsAsyncTask can cause unintended deletion of rows from certain internal database tables resulting in the data not being displayed in some artifact section views. The CleanupUnreferencedCollectionRecordsAsyncTask is designed to remove rows that are no longer referenced from certain internal database tables. However, some rows that are still referenced might be incorrectly deleted. Though the rows are deleted, data is still available in the database and can be restored by using the repairQueryableTables command.

If you upgraded IBM® Engineering Requirements Management DOORS® Next application from 6.x release to 7.x release, it might have caused some incorrect mappings between internal database tables resulting in the data not being displayed correctly in some artifact section views. Though the rows have been mapped incorrectly, data is still available in the database and can be restored using the repairQueryableTables command.

Run the Online verify tool to determine issues with the server. Running the online verify tool serves the following:
  • You need not shut down the servers to run the repair tool (offline), if they are not affected.
  • You can confirm if the problems are fixed after running the repair tool.
Note: You must shut down the server and backup your database before you run this command.

Restoration of data consumes more time. See Summary for more information.

Parameters

Attribute Description Required Default
teamserver.properties Path to the teamserver.properties file. No conf/qm/teamserver.properties
analyzeOnly Set to true to run the command in analyze mode only without repairing. No false
logFile Path to the log file. No repotools-qm_repairQueryabletables.log

Example procedure

The following is an example procedure to restore the data:

(Optional) Run the repair tool in analyzeOnly mode to generate a log file with list of items that are affected. Note that this will not modify the database.

For Windows operating system Open a command prompt and enter this command:

cd C:\Program Files\IBM\JazzTeamServer\server\
repotools-qm.bat -repairQueryableTables teamserver.properties=conf\qm\teamserver.properties analyzeOnly=true -clean

For Unix operating systems Open a command line and enter this command:

cd opt/IBM/JazzTeamServer/server/
./repotools-qm.sh -repairQueryableTables teamserver.properties=conf/qm/teamserver.properties analyzeOnly=true -clean

Run the repairQueryableTables command to restore the deleted data in Queryable tables.

For Windows operating system Open a command prompt and enter this command:

cd C:\Program Files\IBM\JazzTeamServer\server\
repotools-qm.bat -repairQueryableTables teamserver.properties=conf\qm\teamserver.properties -clean

For Unix operating systems Open a command line and enter this command:

cd opt/IBM/JazzTeamServer/server/
./repotools-qm.sh -repairQueryableTables teamserver.properties=conf/qm/teamserver.properties -clean

Run the offline verify tool to confirm that the problem is fixed after running the repair tool. You can also run the Online verify tool.

For Windows operating system Open a command prompt and enter this command:

cd C:\Program Files\IBM\JazzTeamServer\server\
repotools-qm.bat -verify componentId=com.ibm.team.repository.service.cm level=4

For Unix operating systems Open a command line and enter this command:

cd opt/IBM/JazzTeamServer/server/
./repotools-qm.sh -verify componentId=com.ibm.team.repository.service.cm level=4

Online verify tool

Online Verify Tool requires repodebug permissions.

Perform the following steps to enable repodebug:
  1. Go to /<application>/admin page and click Advanced Properties.
  2. Search for Enable repodebug service option.
  3. Set the value to true, then click Save.
Perform the following steps to check the Verifier status:
  1. Go to /<application>/onlineVerify/start?level=4&componentId=com.ibm.team.repository.service.cm
  2. Refresh the page and check the Progress Completion and Total Runtime to run the task.
  3. Check the Problems section for ERROR Count. The following is an example output:

    0 - No errors

    Check for the message in the Results section for more details.

    Note: If there are more than 1000 change sets with uncomputed dependencies, you must run the computeChangesetDependencies command.
  4. In case of errors, check the message for component ID com.ibm.team.repository.service.cm#verifyQueryableTablesForDataIssues.
  5. Check the message to see if the verification is successful.
    Note: Verification takes time depending on the size of the database.
    • If successful, OK is displayed.
    • If failed, the following message is displayed:
      Data missing in Queryable join tables. Please run the -repairQueryableTables command using repotools.

Summary

The following is summary information after running the repair tool on the jazz.net/qm environment. For deployment topology, see Wiki page. 
Number of Queryable tables processed: 27
Total number of Items in all Queryable tables: 23,626,402
Number of child tables affected & recovered : 6
Number of Items affected & recovered: 2,922 item(s)
Time taken to analyze all items: 3 hours, 51 minutes
Time taken to repair: 3 hours, 29 minutes
Time taken by the verifier (offline) tool: 4 hours 51 minutes