Repository tools command to recreate incorrectly removed queryable table data

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.

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: If you upgraded from 6.0.6.1 GA, 6.0.6.1 iFix001, or 6.0.6.1 iFix002 release, then make sure that you run the repair tool if it was not already run on 6.0.6.1 or during the upgrade.
Important: You must shut down the server before you run the 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.

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

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.

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

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.

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

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

Perform the following steps to enable the OnlineVerifyMetricsTask:
  1. Go to https://host_name:port/qm/admin.
  2. Click Serviceability on the left side and search for com.ibm.team.repository.service.internal.serviceability.OnlineVerifyMetricsTask.
  3. Set Enable Online Verify Metrics MBean to true.
  4. Set Verification Level to 4.
  5. Click Save.
  6. Restart the server.
    Note: The task runs in the background. The online metrics are visible only after the task is complete. The timing of the task might vary on the amount of data and verification level.

Check verifier status

Perform the following steps to check the Verifier status:
  1. Go to https://host_name:port/qm/repodebug/mxBeans.
  2. Select Domain as com.ibm.team.foundation.datavalidation. (This option is displayed only after the verification is completed.)
  3. Select Type as onlineVerifyMetrics. (This option is displayed only after the verification is completed.)
  4. Click Search and click the com.ibm.team.foundation.datavalidation:name=qm,type=onlineVerifyMetrics,componentId=com.ibm.team.repository.service.cm#verifyQueryableTablesForDataIssues link.
  5. Check the message to see if the verification is successful.
    • 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 https://jazz.net/wiki/bin/view/Deployment/DeploymentExampleIBMJazznetEnvironment. 
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