IBM Support

IBM® DOORS® Next upgrade from 6.x to 7.x on SQL Server 2016 appears to hang at Initialization Complete phase.

Troubleshooting


Problem

During the ELM 6.x to ELM 7.0 on SQL Server 2016 upgrade, DOORS® Next upgrade appears to hang at the "Initialization Complete" phase. 
This behavior has only been observed using SQL Server 2016, where we have seen the generation of poor execution plans for key upgrade queries.  The problem is that the SQL Server query being run with this plan will take up considerable resources and time to complete, delaying the upgrade progress.
This scenario may continue for several hours, or not complete at all.
 

Symptom

The symptoms can be detected from various sources.
1) The most obvious symptom is in the console from where the upgrade was initiated.  For example:
Setting committed timestamp to 2017-11-11 11:12:13.14 in change set [UUID _a - abCDEFGhiJklm1O_ABC]
Setting committed timestamp to 2018-11-11 11:12:13.14 in change set [UUID _a - abCDEFGhiJklm1O_ABC]
Setting committed timestamp to 2019-11-11 11:12:13.14 in change set [UUID _a - abCDEFGhiJklm1O_XYZ]
Finished setting missing committed timestamps in change sets.  Updated 100,000 change sets.
Initialization complete.
2) The repotools-rm_addTables.log and the repotools_rm_upgrade.log have no new entries for at least two hours.
Depending on the command that was run to initiate the upgrade, the repotools-rm_addTables.log or the repotools_rm_upgrade.log will contain significant logging.  During SQL query executions, this will pause for as long as it takes to execute the query.
The addTables.log should reflect point 1 above with:
Initialization complete.
020-07-29 17:10:22,461 Finished setting missing committed timestamps in change sets.  Updated 100,000 change sets.
2020-07-29 17:10:22,462 Initialization complete.
3) If you are able to see the SQL Server Profiler, then you can see the following query being run.  This is also evident in the Resource Monitor for running processes on the Windows server.
SELECT COUNT(*)
FROM RESOURCE.RESOURCE RSC
LEFT JOIN DNGMIGRATION.STORAGE_VERSION sver
ON sver.STORAGE = RSC.URI
LEFT JOIN VVCMODEL.VERSION VRS
ON sver.VERSION_STATE_ID = VRS.STATE_ID
JOIN REPOSITORY.ITEM_STATES STATES
ON RSC.ITEM_ID = STATES.ITEM_UUID
AND NOT EXISTS (SELECT 1 FROM REPOSITORY.ITEM_STATES STATES2
WHERE STATES.ITEM_UUID = STATES2.ITEM_UUID
AND STATES.MODIFIED < STATES2.MODIFIED)
LEFT JOIN REPOSITORY.QUERYABLE_JPI_MAPPING JPI1
ON sver.CONCEPT = JPI1.JPI
LEFT JOIN REPOSITORY.QUERYABLE_JPI_MAPPING JPI2
ON VRS.VERSION = JPI2.JPI
WHERE EXISTS(
SELECT 1 FROM DNGMIGRATION.COMPONENTS_TO_MIGRATE comps
WHERE comps.PROJECT_AREA_ID = RSC.CONTEXT_ID
)
AND RSC.URI like 'storage/com.ibm.rdm.resources/%'
AND NOT EXISTS (SELECT 1 FROM VVCMODEL.VERSION V2
WHERE V2.CONCEPT = VRS.CONCEPT
AND V2.DELETED = 0 -- is not a deletion - these don't have item states
AND VRS.CREATION_TIME < V2.CREATION_TIME)
4) It is also it is possible to view this if you generate a javacore.  As it is challenging to create a javacore on Windows, we recommend using the following setting in the teamserver.properties prior to upgrade:
com.ibm.service.migration.jvmDumpPeriodMinutes=60
In the javacore file you will be looking for confirmation that this issue has occurred when this query persists for several hours:
 
3XMTHREADINFO      "[SQL@17:10:51.151], SELECT COUNT(*)
FROM RESOURCE.RESOURCE RSC
LEFT JOIN DNGMIGRATION.STORAGE_VERSION sver
...
<the remainder of the query is printed, as stated in point 3 above>

Cause

IBM initially created defect 136971 to investigate this behavior.
We concluded that this behavior will only be observed when using SQL Server 2016, or where SQL Server 2016 compatibility mode is selected.
This is due to the generation of poor execution plans for key upgrade queries.

Environment

This behavior has only been observed using SQL Server 2016.

Diagnosing The Problem

See Symptoms section above for how to diagnose the problem.
If, after reviewing the "Resolving The Problem" section, you are unable to work around this problem then please contact IBM Support for advice on how to proceed with your upgrade.
We recommend that you run the following Repository Metrics SQL and provide this with your case.
The output should appear similar to this:
metric                          value
concepts                        1,226,340
versions                        2,348,728
configs                         607
changesets                      581,464
version mappings                3,890,073
resources                       5,378,879
resource revisions              9,134,698
item states                     10,556,052
If it is also possible for your DBA to supply the overall size of the RM database within SQL Server, this will be useful information for
It is also helpful to understand the vCPU and RAM resources available on the DOORS® Next application server, as well as the SQL Server database server.

Resolving The Problem

If you have experienced this scenario then, as a minimum, we would expect update statistics (exec sp_updatestats) to be run.
However, the root cause is likely to be the SQL Server execution plans and we recommend upgrading SQL Server:
  • Upgrade to SQL Server 2017 with DOORS® Next 7.0
  • Upgrade to SQL Server 2017 or SQL Server 2019 with DOORS® Next 7.0.1
Note: You may see a warning in some applications that SQL Server 2017 is not supported.  This message can be ignored for the duration of the upgrade process.
If you are seeing this issue using SQL Server 2017 or SQL Server 2019 with DOORS® Next 7.0.1 then check that the compatibility level of the database is set to be 2017 or 2019 (not 2016).
Databases created on SQL Server 2016 may be configured with a compatibility level of 130 (which is 2016) - and in that case, later versions such as SQL Server 2019 will still generate poor execution plans.

To check the compatibility level of the databases, use this SQL:

SELECT name, compatibility_level FROM sys.databases;
Level values of 140 (2017) or 150 (2019) are what you want to see.
 
To change the compatibility level of a database:
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSUVLZ","label":"IBM Engineering Requirements Management DOORS Next"},"ARM Category":[{"code":"a8m50000000Cj3AAAS","label":"DOORS Next Generation->Database *File under JTS*->SQL Server"}],"ARM Case Number":"TS003631143","Platform":[{"code":"PF033","label":"Windows"}],"Version":"7.0.0;7.0.1","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Product Synonym

doors next; DNG; DOORS Next Generation

Document Information

Modified date:
01 December 2020

UID

ibm16254393

Page Feedback