IBM Support

Performance issues and CRRRW7556E after upgrading IBM Rational DOORS Next Generation (DNG) repository

Troubleshooting


Problem

Users observe poor performance and a connection error after upgrading from IBM Rational DOORS Next Generation (DNG) version 5.0.x to 6.0.x, or on large 6.0.x deployments.

Symptom

Typically, after an upgrade, loading Requirements Management (RM) projects takes a long time.  This performance degradation can occur at any time, especially with Oracle 11g, and is not only post-upgrade.

The following error occurs.

A connection to the server cannot be established. The following HTTP status code was received: 0

ID CRRRW7556E The application cannot connect to the server. There might be a problem with your network connection or the server might not be available. Check your computer's network connection. Ask your administrator to check the status of the server.

Cause

There are various possible causes.
 

  • The database statistics did not run after the migration. There are many changes to the RM database tables that need updating in the database statistics.
     
  • When you use Microsoft SQL Server database, you also need to create indexes and make changes to the tables.
     
  • When you use an Oracle database server, you need to do additional procedures to optimize performance, especially Oracle 11g.
     
  • Configuration cache requires tuning due to large repository size. See technote 1995500 if you are on versions 6.0, 6.0.1 or 6.0.2 and continue to experience performance problems after reviewing the solutions below.

Diagnosing The Problem

Verify whether the DB statistics ran since the upgrade. For Oracle and SQL Server, see additional information in this document.

Resolving The Problem

Ask your database administrator to run the statistics command to update the database tables.


Disclaimer
All source code and/or binaries attached to this document are referred to here as "the Program". IBM is not providing program services of any kind for the Program. IBM is providing the Program on an "AS IS" basis without warranty of any kind. IBM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES (INCLUDING LOST PROFITS OR SAVINGS), EVEN IF IBM, OR ITS RESELLER, HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
 

Microsoft SQL Server

 

  • Update the DB statistics using sp_updatestats
  • If you are running 6.0.2 or below, run the following commands against the DOORS Next Generation and Design Manager databases.
    • DROP INDEX VERSION_CONCEPT_DX ON VVCMODEL.VERSION
      DROP INDEX VERSION_STORAGE_DX ON VVCMODEL.VERSION

      Note: The "DROP INDEX" command may fail with the following error. That just means the optimization has not yet been applied. Continue with the ALTER TABLE and CREATE INDEX commands below:



      "Msg 3701, Level 11, State 7, Line 1
      Cannot drop the index 'VVCMODEL.VERSION.VERSION_CONCEPT_DX', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 7, Line 2
      Cannot drop the index 'VVCMODEL.VERSION.VERSION_STORAGE_DX', because it does not exist or you do not have permission."


      ALTER TABLE VVCMODEL.VERSION ALTER COLUMN CONCEPT NVARCHAR(450) NOT NULL
      ALTER TABLE VVCMODEL.VERSION ALTER COLUMN STORAGE NVARCHAR(450) NULL




      CREATE INDEX VERSION_CONCEPT_DX ON VVCMODEL.VERSION (CONCEPT)
      CREATE INDEX VERSION_STORAGE_DX ON VVCMODEL.VERSION (STORAGE)

  • If you are using SQL Server 2014 or higher, it may be necessary to revert to the SQL Server 2012 optimizer due to a known issue with a query. Technote 2000848 has more information on the query.
     

Oracle database
Update the DB statistics and apply the following Oracle database server patches to the database server that hosts the DOORS Next Generation database. Please note that both these patches apply to Linux and Windows database servers.

Prior to Oracle 12cR2, the APAR PI56920, where the length of the Uniform Resource Indicator strings can be an issue with Oracle histograms, can degrade performance.  This is especially so with Oracle 11g where the uniqueness limit is 32 characters.  Oracle 12c has addressed this to reference the first 64, as does the Oracle patches below, but installations using long URIs could still face this issue.

  • Bug 9885553 - DBMS_STATS produces incorrect column NDV distinct values statistics (Oracle Doc ID 9885553.8)
     
  • Bug 13077335 : CARDINALITY IS NOT CORRECTLY CALCULATED FOR LONG VARCHAR COLUMNS WITH HISTOGRAMS (Oracle Doc ID 13077335.8)

    Note: Read the Oracle patch description for specific instructions to learn how to apply and enable each patch. The Oracle patch is disabled by default and must be enabled manually by following the instructions in the Oracle Document ID 13077335.8.
     
  • You should install both Oracle patches and apply the following workaround.
    Note: Even after you enable the Oracle patches you may need to apply the workaround if you see future performance issues related to the VVCMODEL_VERSION table.


The commands to execute are as follows. Adjust the commands accordingly to your environment as the example below uses "RM" as the schema owner.

  1. Use the following SQL to see whether histograms are in use and how many unique indices are in use.

    > select column_name, num_distinct, num_nulls, histogram, notes from user_tab_col_statistics where table_name = 'VVCMODEL_VERSION'
    COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM NOTES
    ----------- ------------ --------- --------- -----
    ID               500000         0       NONE      NULL
    URL              500000      0       NONE      NULL
    CREATED      1732          0      NONE      NULL

    If the database has had a server rename performed on it, then we also advise running the following:

    > select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics where table_name = 'REPOSITORY_QUERYABLE_JPI_MPPNG';

    COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
    ------------------------------ ------------ ---------- ---------------
    JPI                                           50000           0            NONE
    URL                                     50000          0          HEIGHT BALANCED

     
  2. Delete histograms and set statistics preferences on the VVCMODEL_VERSION table to ensure the appropriate indexes are used by executing the statements below on the DOORS Next Generation database.

    exec dbms_stats.set_table_prefs('RM', 'VVCMODEL_VERSION', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE  1 CONCEPT, FOR COLUMNS SIZE 1 STORAGE, FOR COLUMNS SIZE 1 VERSION');
    exec dbms_stats.delete_column_stats(ownname=>'RM', tabname=>'VVCMODEL_VERSION', colname=>'CONCEPT', col_stat_type=>'HISTOGRAM');
    exec dbms_stats.delete_column_stats(ownname=>'RM', tabname=>'VVCMODEL_VERSION', colname=>'STORAGE', col_stat_type=>'HISTOGRAM');
    exec dbms_stats.delete_column_stats(ownname=>'RM', tabname=>'VVCMODEL_VERSION', colname=>'VERSION', col_stat_type=>'HISTOGRAM');

    exec dbms_stats.gather_table_stats('RM','VVCMODEL_VERSION');
     
  3. If the database has had a server rename performed on it, then we also advise running the following:

    exec dbms_stats.set_table_prefs('RM', 'REPOSITORY_QUERYABLE_JPI_MPPNG', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE  1 JPI, FOR COLUMNS SIZE 1 URL');
    exec dbms_stats.delete_column_stats(ownname=>'RM', tabname=>'REPOSITORY_QUERYABLE_JPI_MPPNG', colname=>'URL', col_stat_type=>'HISTOGRAM');
    exec dbms_stats.delete_column_stats(ownname=>'RM', tabname=>'REPOSITORY_QUERYABLE_JPI_MPPNG', colname=>'JPI', col_stat_type=>'HISTOGRAM');

    exec dbms_stats.gather_table_stats('RM','REPOSITORY_QUERYABLE_JPI_MPPNG');
     
  4. There is no longer a need to restart the database.
    If you wish to confirm that the histograms have been deleted, you can re-run the SQL commands from point 1 above.
    You will now see that there is the NONE value in all columns.
     

> select column_name, num_distinct, num_nulls, histogram from user_tab_col_statistics where table_name = 'REPOSITORY_QUERYABLE_JPI_MPPNG';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
JPI                                           50000           0            NONE
URL                                     50000          0          NONE

Note - Oracle 12cR2 is supported from CLM 6.0.5+

You can find other DNG 6.0.x performance considerations in these documents.

Related information

Known issues in IBM Rational DOORS Next Generation 6.0

Document information

More support for: Rational DOORS Next Generation

Component: General information

Software version: 6.0, 6.0.1, 6.0.2, 6.0.3, 6.0.4, 6.0.5, 6.0.6

Operating system(s): Linux, Windows

Reference #: 1975746

Modified date: 17 October 2018