Workarounds: Server Rename problems in CLM 4.0.1 and 4.0.2

Author: Heather Sterling
Last Updated: March 15, 2013
Build Basis: Rational solution for Collaborative Lifecycle Management 4.0.1 and 4.0.2

Problem Summary

An SQL exception might occur during a server rename if Jazz Team Server or an application contains URLs that are close to the maximum character length that Jazz supports.

Problem

The applications of the Rational solution for Collaborative Lifecycle Management (CLM) store many URLs in columns in the database. The URLs have a 1000-character limit. In rare cases, this limit can be exceeded during a rename.  If this issue occurs on Jazz Team Server, when you run the repotools importURLMappings command, an error occurs.  If the problem occurs in an application, the error is shown as a rename failure for that application on the server rename status page for Jazz Team Server.  This problem does not apply to the Requirements Management (RM) or Lifecycle Project Administration (LPA) applications because they do not store the URLs in a relational database. This problem has two variations.

Variation 1

The target URL is longer than the source URL.  For example, if you move from a short name to a fully-qualified domain name, you might have this mapping:

source=https://myhost:9443/jts
target=https://myhost.domain.com:9443/jts

In this case, the target URL is 11 characters longer than the source.  If the database contains an artifact URL that is 990 characters long, after the rename, the new artifact URL will be 1001 characters long, which exceeds the column limit and causes an SQL exception. The DB2 exception is shown as follows.  On other database vendors, the error might look different and have a different error code.

com.ibm.team.repository.service.mapping.internal.ServerRenameService] ERROR sitory.service.mapping.internal.ServerRenameHelper  - CRJAZ2358E An error occurred while processing the server rename events.  com.ibm.team.repository.common.InternalRepositoryException: CRJAZ0329I SQL statement execution failedString data, right truncation  SQL: Insert into {REPOSITORY.QUERYABLE_JPI_MAPPING} ({JPI}, {URL}) select distinct t1.URI, TRIM((t2.URL || substr(t1.URI, length(t2.JPI)+1,length(t1.URI)))) from MARKERS.MARKER t1,REPOSITORY.JPI_MAPPING t2 where t1.URI is not null and t1.URI <> '' and t2.JPI = (select t3.JPI from REPOSITORY.JPI_MAPPING t3 where ((locate(t3.JPI || '/' ,t1.URI,1) = 1) or (t1.URI = t3.JPI)) order by length(t3.JPI) desc fetch first 1 rows only ) and not exists (select 1 from {REPOSITORY.QUERYABLE_JPI_MAPPING M} where {M.JPI}=t1.URI)  SQL Exception #1  SQL Message: DB2 SQL Error: SQLCODE=-433, SQLSTATE=22001, SQLERRMC=https://rename1net.torolab.ibm.com:9446/qm/web/console/Ratio, DRIVER=4.14.88  SQL State: 22001  Error Code: -433     

Variation 2

This variation happens on subsequent renames only; this problem never occurs during an initial rename. In this case, the length of a source URL mapping plus the length of an artifact URL exceeds the 1000 character limit. For example, you might have this mapping:

source=https://myhost1.domain.com:9443/jts
target=https://myhost2.domain.com:9443/jts

In that mapping, the source URL is 36 characters.  If an artifact URL exceeds 964 characters, the rename will fail.  The DB2 exception is shown as follows.  On other database vendors, the error might look different and have a different error code.

2012-11-08 11:58:30,170 [com.ibm.team.repository.service.mapping.internal.ServerRenameService] ERROR sitory.service.mapping.internal.ServerRenameHelper  - CRJAZ2358E An error occurred while processing the server rename events.  com.ibm.team.repository.common.InternalRepositoryException: CRJAZ0329I SQL statement execution failedSubstring error  SQL: Update {REPOSITORY.QUERYABLE_JPI_MAPPING} M SET M.{URL} = COALESCE((select TRIM(t2.NEW_URL || substr(t1.{URL}, length(t2.OLD_URL)+1,length(t1.{URL})))  FROM {REPOSITORY.QUERYABLE_JPI_MAPPING} t1, REPOSITORY.MAPPING_EVENT_URL_CHANGES t2 where M.{URL} = t1.{URL} and t2.JZ_PARENT_ID = '_gWp1MCnFEeKOGLzCLILy-w' and ((locate(t2.OLD_URL || '/' ,t1.{URL},1) = 1) or (t1.{URL} = t2.OLD_URL)) ORDER BY t2.JZ_POSITION fetch first 1 rows only  ), M.{URL})  SQL Exception #1  SQL Message: DB2 SQL Error: SQLCODE=-138, SQLSTATE=22011, SQLERRMC=null, DRIVER=4.14.88  SQL State: 22011  Error Code: -138     

Workaround

Variation 1

To completely avoid this problem, ensure that the target URLs are not longer than the source URLs.  For example, these mappings will not cause a problem:

source=https://host1.domain.com:9443/jts
target=https://host2.domain.com:9443/jts

source=https://host1.longdomain.com:9443/jts
target=https://host2.domain.com:9443/jts

In rare circumstances, this mapping might cause a problem:

source=https://host1:9443/jts
target=https://host1.domain.com:9443/jts

If the target URL must be longer than the source URL, proceed with the rename. It is rare that URLs exceed the 1000 character limit. Currently, no tool detects before a rename whether the long URLs exist in the database.  If you encounter this error, contact IBM Software Support to determine the appropriate corrective action, which might include these tasks:

1. Truncating the URL column and repairing the item.  This action might result in a broken link or incorrect data.
2. Deleting the item.
3. Using a shorter target URL as a last resort if the piece of data is critical.

Variation 2

This variation of the problem cannot be completely avoided by choosing target URLs that are shorter than or equal to the source URLs.  However, the workaround for this variation is the same as the workaround for variation 1.

Related Information

The following links point to related information:

• 240215: SQL truncation error during rename when source URLs are under 1000 and target URLs are over 1000
• 240461: Second rename of jazzdev staging test failed in QM with SQL exception

Problem Summary

During a server rename, if the public URL of the original source server contains a trailing forward slash, importURLMappings might fail for Jazz Team Server. Similarly, if the public URL of an application contains a trailing forward slash, the online rename for the application might fail.

Problem

A public URL does not typically contain a trailing forward slash, or a forward slash at the end of the URL. In addition, public URLs are not generated with trailing forward slashes by default. However, an administrator can explicitly add a forward slash to the end of a URL by using the setup wizard. In that case, the public URL that is stored in the teamserver.properties file and in the database both contain a trailing forward slash. If a server is being renamed and the public URL of Jazz Team Server contains a trailing forward slash, importURLMappings fails on Jazz Team Server. In addition, if the public URL of an application contains a trailing forward slash, the online rename of the application fails. To determine whether this problem is occurring, check the server logs for this error:

CRJAZ2197E The public URI for this server has already been set to “https://host:port/jts/”. It cannot be changed to “https://host:port/jts/” once the server has been configured. If the server has moved to a new location at “https://host:port/jts/” follow the instructions to rename the server.

Also, in the teamserver.properties file, look for the trailing forward slash. The property name is com.ibm.team.repository.server.webapp.url.

Workaround

To avoid this issue completely, remove the trailing forward slash from the com.ibm.team.repository.server.webapp.url property value in the teamserver.properties file before running importURLMappings. The import will succeed without error.

If the issue is not corrected before importURLMappings is run, update the database value of the public URL to remove the forward slash from the end of the URL. Contact your database administrator to run the following commands. The commands must be run against the database of the failing Jazz Team Server or application. The SQL commands differ depending on the database vendors. In fact, the commands can vary on a given vendor because the column names might be different.

Complete the following steps:

1. Look up the JAZZ.PROPERTIES table to determine the column names. This table contains two columns: a key column and a value column.
2. To retrieve the current value, enter this SQL command, where VALUE is the name of the value column and KEY is the name of the key column:
   SELECT VALUE FROM JAZZ.PROPERTIES WHERE KEY='com.ibm.team.repository.service.mapping.internal.DatabasePrefixUrlLockService.dbPrefixUrl'
3. Verify that the value has a forward slash at the end.
4. Remove the forward slash by entering the following SQL command. Make sure that the value is equivalent to the value in step 3, but that the value does not contain a forward slash on the end. For example, if https://myhost:9443/jts/ is returned, the new value is https://myhost:9443/jts.
   UPDATE JAZZ.PROPERTIES SET VALUE='https://host:port/jts' WHERE KEY='com.ibm.team.repository.service.mapping.internal.DatabasePrefixUrlLockService.dbPrefixUrl'

If the failure occurred during an application rename, before you update the value, stop the server. After you update the value, restart the server and restart Jazz Team Server.

Related Information

[Always link back to the corresponding work item on Jazz.net; link out to other resources if possible.]

The following links point to related information:

• 241697: The BaseServerDescriptionServicePropertyValidator does not evaluate trailing slashes of the public URL uniformly

Problem Summary

During server rename, importURLMappings reports a failure when the data warehouse is unreachable, even if the data warehouse is disabled.

Problem

When you run the repotools-jts importURLMappings command, the following error occurs:

CRJAZ2271E An error occurred processing the URL mapping.
*** MAPPINGS IMPORT COMMAND FAILED ***
CRJAZ2450E The command failed. The URL mappings were not imported. See the log file for more details.

In addition, the repotools-jts_importURLMappings.log file shows this error:

CRJAZ0503I The server was unable to connect to the following database: "X:/conf/jts/derby/warehouseDB"
Exception: "Failed to create database 'X:/conf/jts/derby/warehouseDB', see the next exception for details."

This problem occurs when the data warehouse is disabled and the database location points to an invalid or unreachable location, such as a non-existent drive or host.

For example:

com.ibm.team.datawarehouse.datawarehouse.jobs.enabled=false
com.ibm.team.datawarehouse.db.jdbc.location = X:/conf/jts/derby/warehouseDB

Because of a bug, the data warehouse database is initialized regardless of the value of the enabled setting. As a result, the URL mappings import command fails when the database location is invalid or unreachable.

Workaround

Complete the following steps:

1. In the conf/jts/teamserver.properties file, comment out all of the properties that are related to the data warehouse:
   • #com.ibm.team.datawarehouse.datawarehouse.jobs.enabled=false
   • #com.ibm.team.datawarehouse.datawarehouse.provider=Remote
   • #com.ibm.team.datawarehouse.db.automatic.setup=true
   • #com.ibm.team.datawarehouse.db.jdbc.location=X:/conf/jts/derby/warehouseDB
   • #com.ibm.team.datawarehouse.db.net.port=1527
   • #com.ibm.team.datawarehouse.db.vendor=DERBY
2. Run the importURLMappings command again. The command should work.
3. After the server rename, uncomment the data warehouse properties that you commented out in the first step.

Related Information

The following links point to related information:

• Server Rename: Reporting should check if datawarehouse is used before trying to update (241156)