Understanding DOORS Next sizings in ELM 6.x to estimate timings when upgrading to DOORS Next 7.x

This article is intended to help contextualize your database size relative to how IBM has tested the upgrade process. We also advise you to make a note of how much storage the database is using for the DOORS Next database both before and after the upgrade.

The article then discusses what tuning will be required for your datasize, with the assumption being that the database administrator (DBA) is a key component in interpreting the advice.

Note: After upgrade, DOORS Next will demand much more resources on the database server and less from the application server due to this re-platforming. DBAs must re-size bufferpools, SGA/PGA sizes to cope with the new workloads, and may also need to amend CPU/RAM resources.

Gathering pre-upgrade repository size

In all the literature within this wiki and across jazz.net, whenever we have discussed repository size in DOORS Next we have discussed this in terms of the total number of artifacts. The 7.0 performance: IBM Engineering Requirements Management DOORS Next details performance expectations post-upgrade, again using the number of artifacts value.

Unfortunately, this is only one useful metric when assessing how long your upgrade may take:

• In a DOORS Next repository which opted out, so did not enable configuration management then this figure is all you need.
• For those that opted-in their projects, then an additional level of granularity is required to understand the size of your repository.

You should take these measurements on the DOORS Next version 6 repository before the upgrade. Please execute the following SQL statement and make a note of the results in case you need to share them with IBM. If you run this SQL, it is important to supply IBM Support with all the values, as each value can effect the duration of your upgrade.

- Oracle

SELECT 'concepts' AS metric, COUNT(DISTINCT CONCEPT) AS value FROM VVCMODEL_VERSION
UNION ALL
SELECT 'versions' AS metric, COUNT(DISTINCT VERSION) AS value FROM VVCMODEL_VERSION
UNION ALL
SELECT 'configs' AS metric,  COUNT(*) AS value FROM VVCMODEL_CONFIGURATION
UNION ALL
SELECT 'changesets' AS matric, COUNT(*) AS value FROM VVCMODEL_CHANGE_SET
UNION ALL
SELECT 'version mappings' AS metric, COUNT(*) AS value FROM VVCMODEL_VERSION
UNION ALL
SELECT 'resources' AS metric, COUNT(*) AS value FROM RESOURCE_RESOURCE
UNION ALL
SELECT 'resource revisions' AS metric, COUNT(*) AS value
   FROM RESOURCE_RESOURCE res
   INNER JOIN REPOSITORY_ITEM_STATES states
   ON states.ITEM_UUID = res.ITEM_ID
UNION ALL
SELECT 'item states' AS metric, COUNT(*) AS value FROM REPOSITORY_ITEM_STATES

- DB2/SQL Server

SELECT 'concepts' AS metric, COUNT(DISTINCT CONCEPT) AS value FROM VVCMODEL.VERSION
UNION ALL
SELECT 'versions' AS metric, COUNT(DISTINCT VERSION) AS value FROM VVCMODEL.VERSION
UNION ALL
SELECT 'configs' AS metric, COUNT(*) AS value FROM VVCMODEL.CONFIGURATION 
UNION ALL
SELECT 'changesets' AS matric, COUNT(*) AS value FROM VVCMODEL.CHANGE_SET 
UNION ALL
SELECT 'version mappings' AS metric, COUNT(*) AS value FROM VVCMODEL.VERSION 
UNION ALL
SELECT 'resources' AS metric, COUNT(*) AS value FROM RESOURCE.RESOURCE 
UNION ALL
SELECT 'resource revisions' AS metric, COUNT(*) AS value
   FROM RESOURCE.RESOURCE res
   INNER JOIN REPOSITORY.ITEM_STATES states 
   ON states.ITEM_UUID = res.ITEM_ID
UNION ALL
SELECT 'item states' AS metric, COUNT(*) AS value FROM REPOSITORY.ITEM_STATES

How to interpret your values

Until ELM 7.0, the key value to determine the size of your database has been the number of artifacts in your repository. In the 7.0 Performance datasheet for DOORS Next performance during usage is still measured in these terms.

In terms of upgrade, the resource revisions metric and the other metrics above will be of more help than the raw number of artifacts in your repository. The resource revisions number is important as it indicates the number of revisions that the upgrade will need to process. There are also other shapes that could determine the overall size. Your Staging environment upgrade is the most reliable indicator of expected upgrade time.

Sample data points from upgrades

• We have made this information database agnostic
• The hardware between the examples were different.
• This table shows how different measurements can impact the overall upgrade time.
• As stated in Migration tuning of the repotools JVM, the JVM heap size was set to 16GB, REPOTOOLS_MX_SIZE=16384

The hardware required to achieve the initial 2.3M time, based on that data shape is available in the

DB Server for IBM testing

• 40 logical processors (hyperthreaded)
• 64 GB RAM
• RAID 10 - 15K SAS, disk x14
• Red Hat Enterprise Linux Server 7.6

RM Server for IBM testing

• 24 logical processors (hyperthreaded)
• 32 GB RAM
• RAID 5 - SAS, disk x4
• Red Hat Enterprise Linux Server 7.6
• WebSphere Liberty, 24GB Heap

Understanding data shape deviations

Large composite changesets

During our upgrade validations, we check to ensure consistency of the changesets that have been delivered. Clients that have used large composite deliveries of changesets may require additional temp dbspace during the upgrade.

Run the following 2 queries to understand your data shape. The second query relies on the output of the first, so replace the ? (question mark) in the 2nd query with the output of the first.

Run the following queries in the DOORS Next 6.x environment:

DB: Oracle

SELECT JZ_PARENT_ID, COUNT(JZ_PARENT_ID)
FROM VVCMODEL_CHANGE_SET_MERGED_FRM GROUP BY JZ_PARENT_ID ORDER BY COUNT(JZ_PARENT_ID) DESC;

SELECT CHANGE_SET_ITEM_ID, TARGET_STREAM_ITEM_ID
FROM VVCMODEL_DELIVERED_CHANGE WHERE CHANGE_SET_ITEM_ID = ?;

DB: SQL Server, DB2

SELECT JZ_PARENT_ID, COUNT(JZ_PARENT_ID)
FROM VVCMODEL.CHANGE_SET_MERGED_FROM GROUP BY JZ_PARENT_ID ORDER BY COUNT(JZ_PARENT_ID) DESC;

SELECT CHANGE_SET_ITEM_ID, TARGET_STREAM_ITEM_ID
FROM VVCMODEL.DELIVERED_CHANGE WHERE CHANGE_SET_ITEM_ID = ?;

Monitoring your upgrade

There is an extensive section in this wiki on how to monitor your system, but there are specific areas to focus on when running your Staging upgrades. It is imperative that you use a staging area and that the hardware is comparable with your production server if you are to ascertain useful timings for your planning.

There are hundreds of tools out there including IBM Tivoli Monitoring (ITM) third party tools like helpsystems-MPG; and open-source like njmon (which assume knowledge, an interest, and hands-on skills). However, in terms of monitoring the upgrade, then the preferred method for AIX and Linux is to use NMON. There are some very useful videos for setting up NMON and NJMON/NIMON on YouTube. This level of data allows for a more accurate correlation of the system at the time of any issues in the logs.

Depending on your topology and network then the importance of each of these metrics will change. For example, you may collocate your database with the application server so will have 0ms latency vs competing resources for I/O. You would then need to ensure you have Disk benchmarking statistics available to understand the impact.

A quick checklist of helpful data if you experience a performance problem during upgrade on your staging system would be: * System definition (CPU, RAM etc.)

• For DOORS Next application server and DB server
  • Logfiles
  • Basic volumetrics from the DOORS Next repository before the upgrade (SPARQL to count artifacts)
  • Number of versions: SQL provided above
  • Number of concepts: SQL provided above
  • Measure the size of the DB before and after the upgrade
  • Performance metrics during the upgrade
    • Whatever you can measure CPU, heap, DB I/O (eg NMON)
  • Elapsed time to upgrade (this will be in the verbose DOORS Next log).

Planning your ELM upgrade to version 7.0 also contains additional information which may be required when contacting IBM Support. A list of Must gather information is at the bottom of this article.

Migration tuning of the repotools JVM

In terms of the DOORS Next repository, you should only need to amend the following setting in the repotools-rm file from 1500MB to the values below. This is consistent with the Interactive Upgrade Guide. It is only anticipated that you would require 32GB if your repository is on a very large scale. It is not recommended to increase past 32GB and certainly not recommended to create enormous heaps to speed up the upgrade. We found no evidence that larger heap sizes improved performance, beyond 32GB.

We also recommend that you set a file to record the verbose garbage collection information. For more information on setting the verbosegc log, see the WebSphere help document Writing Verbose GC to a Specified Log -Xverbosegclog in AIX, Linux and Windows

Windows example to increase it to 16G and set verbose GC logging:

set REPOTOOLS_MX_SIZE=16384
set VMARGS=%VMARGS% -verbose:gc -Xverbosegclog:c:\temp\verbosegc.log -XX:NumberOfGCLogFiles=100 -XX:GCLogFileSize=20M

export REPOTOOLS_MX_SIZE=16384
export VMARGS="$VMARGS -verbose:gc -Xverbosegclog:/tmp/verbosegc.log -XX:NumberOfGCLogFiles=100 -XX:GCLogFileSize=20M

Post-upgrade calculations

It is expected that the post-upgraded DOORS Next database will require approximately 60% -100% more than the storage required for the existing DOORS Next 6.x. You must ensure that the database is suitably configured for this growth.

- Oracle

 SELECT 'resource types' AS metric, COUNT(*) AS value FROM DNGMIGRATION_DB_RESOURCE_TYPE
 UNION ALL
 SELECT 'upgrade mappings' AS metric, COUNT(*) AS value FROM DNGMIGRATION_DB_UPGRADE_MAPPNG
 UNION ALL
 SELECT 'mappings' AS metric,  COUNT(*) AS value FROM REPOSITORY_VERSION 
 UNION ALL
 SELECT 'r.versions' AS metric, COUNT(DISTINCT STATE_ID) AS value FROM REPOSITORY_VERSION
 UNION ALL
 SELECT 'item states' AS metric, COUNT(*) AS value FROM REPOSITORY_ITEM_STATES

- DB2/SQL Server

SELECT 'resource types' AS metric, COUNT(*) AS value FROM DNGMIGRATION.DB_RESOURCE_TYPE
UNION ALL
SELECT 'upgrade mappings' AS metric, COUNT(*) AS value FROM DNGMIGRATION.DB_UPGRADE_MAPPING
UNION ALL
SELECT 'mappings' AS metric,  COUNT(*) AS value FROM REPOSITORY.VERSION   
UNION ALL
SELECT 'r.versions' AS metric, COUNT(DISTINCT STATE_ID) AS value FROM REPOSITORY.VERSION
UNION ALL
SELECT 'item states' AS metric, COUNT(*) AS value FROM REPOSITORY.ITEM_STATES

Tuning the database

This next section details a base set of considerations which apply where database servers are typically installed within the context of ELM-only.

The most significant advice we can give for tuning databases for upgrade is already listed in the Interactive Upgrade Guide, which is to ensure that the database statistics are up-to-date, for example:

Oracle: EXEC DBMS_STATS.GATHER_DATABASE_STATS

DB2: DB2 REORGCHK UPDATE STATISTICS ON TABLE ALL

SQL Server: exec sp_updatestats

Database tuning and guidance

When database servers are part of large database farms, then many of these considerations will be redundant. However, we recommend discussing with your database administrator (DBA) to determine what values are appropriate within the context of your enterprise setup. This section is not expected to be copied verbatim. These values are specific to the machine size in use and are not appropriate for any database server which is smaller. When altering the BUFFERPOOL remember to reset this value post-upgrade to the original value when you run db2 get db cfg.

It is also important that you ensure that you have sufficient TEMP tablespaces, which have the ability to auto-extend if necessary. Failure to do so will result in a compromised upgrade and likely to require a rollback and re-attempt at the upgrade.

If you are unsure if you encountered this situation, you can parse the upgrade log for messages similar to the Oracle error message:

"ORA-01652: unable to extend temp segment by 128 in tablespace TEMP"

DB2

When the IBM Performance Engineering team ran the DB2 upgrade of DOORS Next for 7.x, there were some tunings performed to the database. First, we forced DB2 to maximize all the available RAM to the DNG DB Memory as well as its bufferpool size, e.g.:

First, run these three commands as db2admin user prior to the upgrade, to understand your configurations and settings for the current database(s):

$ db2 connect to <DNG_DB>
$ db2pd -dbptnmem
$ db2 get db cfg for <DNG_DB>

replacing to the actual database name.  

db2 "ALTER BUFFERPOOL IBMDEFAULTBP SIZE 2500000"
db2 "update db cfg for RMDB using DATABASE_MEMORY 15111640"

Note that this is an extreme example on a 64G-RAM system, where the RMDB alone has around 60G RAM assigned, with ~40G bufferpool. We unset the automatic flag so that STMM (self-tuning) does not shrink them inadvertently during migration. They could vary depending on the size of the database and the actual RAM available on the system.   Then we secured the minimums for the following:

db2 update db cfg using stmt_conc literals immediate
db2 update db cfg using APPLHEAPSZ 10000 automatic
db2 update db cfg using LOGFILSIZ 65536
db2 update db cfg using SORTHEAP 28615
db2 update db cfg using SHEAPTHRES_SHR 143077
db2 update db cfg using PCKCACHESZ 32184
db2 update db cfg using STMTHEAP 8000

Finally, we altered inline length for LOB data:

alter table REPOSITORY.ITEM_STATES alter column ITEM_VALUE set inline length 2048;
alter table REPOSITORY.CONTENT_STORAGE alter column CONTENT_BYTES set inline length 4096;
reorg table REPOSITORY.ITEM_STATES allow read access longlobdata;
reorg table REPOSITORY.CONTENT_STORAGE allow read access longlobdata;

This section of the article is a guide for DBAs and not prescriptive, but advisory. We are therefore only covering the DB2 settings that were altered in order to improve performance. There is more information on these values and how they would be set for production performance in the Db2 Tuning portion of the 7.0 Performance report.

Oracle

This section of the article is a guide for DBAs and not prescriptive, but advisory. We are therefore only covering the Oracle settings that were altered in order to improve performance.

Oracle memory management settings:

automatic memory management
memory_max_target=50000M scope=spfile;
memory_target=50000M scope=spfile;
sga_max_size=0 scope=spfile;
sga_target=0 scope=spfile;
pga_aggregate_limit=0 scope=spfile;
pga_aggregate_target=0 scope=spfile;

There is more information on these values and how they would be set for production performance in the Oracle Tuning portion of the 7.0 Performance report.

Some useful guides for interpreting AWR reports are:

• Oracle Database Tips by Donald Burleson
• Performance Tuning Basics 15 : AWR Report Analysis
• AWR reports (Part I) – 10 most important bits

Considerations for larger imports.

What is considered a large import can be subjective, but in terms of this article it is any migration of data in this manner where new tables are being populated in the database from a Jena data store should treat this as a large import. It is common to increase the number of transaction logs to accommodate such an operation.

• Increase the log file of the database server. Empirically, our expertise resides with DB2, therefore the following settings are recommended for change in order to unblock the bottleneck of the database server.

   db2 connect to RM7
   db2 update db cfg for RM7 using LOGPRIMARY 120
   db2 update db cfg for RM7 using LOGFILSIZ 16384

It is important to ensure that your Oracle archive logs are appropriate for a very large transaction that occurs as part of the DNG upgrade to avoid an Oracle DB archive logging full filesystem producing the below error :

2020-07-23 20:08:56,011 CRJAZ0503E The server was unable to connect to the database. Exception:
"CRJAZ2654E The server was unable to connect to the database because the database connection settings are invalid or because database is unreachable (SQLSTATE: 64000, SQLCODE: 257). 
Verify the connection settings in your teamserver.properties file. See the Jazz Team Server log for more information."
2020-07-23 20:08:57,044 CRJAZ2654E The server was unable to connect to the database because the database connection settings are invalid or because database is unreachable (SQLSTATE: 64000, SQLCODE: 257). 
Verify the connection settings in your teamserver.properties file. See the Jazz Team Server log for more information.
java.sql.SQLException: ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.

The solution for this error is either :

- increase the frequency of your archive logs backup and cleanup or - disable the archive logging during the upgrade activities (NOARCHIVELOG mode) (any type of point in time recovery operations or online backups won't be possible untill it's enabled again )

Undo tablespace

For large upgrades, and/or where the data may be unusual due to import scripts, OSLC, or any other non-GUI usage then it is recommended that the undo tablespace is monitored and set to autoextend where possible.

If you encounter the error below, then either add more datafiles to the undo tablespace, set the autoextensible flag or enlarge the datafile(s). See Oracle documentation for more advice on how to handle this feature. If you choose to add datafiles, then we recommend adding additional datafiles in 64GB increments (for large enterprise clients). Also, note that the error below is an example of where this error could occur. Each dataset may require resources in different places of the upgrade.

Caused by: com.ibm.team.repository.common.InternalRepositoryException: CRJAZ0329E The database query could not be completed. Vendor specific exception
SQL: INSERT INTO DNG.DNGRTFCTSDBRTFCTMDMSTRNGXTNSNS(JZ_PARENT_ID, INTERNAL_ID, KEY, VALUE) select * from (select ? as JZ_PARENT_ID, ? as INTERNAL_ID, ? as KEY, ? as VALUE from DUAL) X 
where not exists (select 1 from DNG.DNGRTFCTSDBRTFCTMDMSTRNGXTNSNS Y where X.INTERNAL_ID=Y.INTERNAL_ID and X.JZ_PARENT_ID=Y.JZ_PARENT_ID)
SQL Exception #1
SQL Message: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

SQL State: 99999
Error Code: 30036

Ensure the hard disks in use are optimal.

Solid State Disks(SSD) are recommended for the RM indices location and the database server's log file area. Standard 10k rpm as disk speed is generally insufficient for the read/write access required on very heavily used directories. Use hdparm –tT to track disk speed. The Timing cached reads per second are crucial.

Typically with database servers, the IOPS (I/O per second) is a significant indicator of performance. We recommend that your DBA refer to our article on Disk Benchmarking. This article covers tools that can benchmark the performance of storage systems, and includes some sample results from the IBM labs. The typical methods to calculate the IOPS of the DB server are these benchmarks:

• Calibrate_io (Oracle)
• SLOB (Oracle)
• Sysbench (OS)

Considerations for Staging comparisons (Must gather)

It is important to size any run in a Staging environment appropriately. The ideal is to use comparable hardware, as well as the same software settings in the application (repotools-rm) and database (as discussed above) to know how long the upgrade will take. If it is not possible to do so, then you should understand the following characteristics:

• Monitor machine performance counters of DB Server and DOORS Next server during migration.
• nmon (Linux), perfmon (Windows) - in 10-minute intervals
• AWR report from DB server - collect hourly 
• During the test, did the DB server and DOORS Next server get their full allocation of CPU cores and RAM?  Are the CPU cores and RAM resources allocated to their VMs dedicated?
• During test, was the DB server only supporting the upgrade or were their other DB activities, e.g. maintenance or other applications being serviced at that time?
• During the test, what was the CPU and Memory utilization of the DB server?
• What is the network latency between the system running repotools and the database server?
• Collect JVM verbose GC logs on repotools-rm

Related topics: 7.0 performance: IBM Engineering Requirements Management DOORS Next, Planning your ELM upgrade to version 7.0, Oracle 12c Tuning Guide

External links:

• IBM

Additional contributors: HongyanHuo, MarkGoossen, TimFeeney, MoatazHarone, VaughnRokosz