Changing the logical name of a ClearQuest user database.


During the normal course of ClearQuest development, I often want to create a new development database from a production backup and have it available from the same schema (i.e. I don’t want to create a separate schema database from a production snapshot as well). Because you can’t have two user databases registered to a schema with the same logical database name, you need to be able to change the logical name of the newly created development database in some way. For example, if my production database is called LON, I want to call my new development instance LOND1 (I use the convention ‘D’ to indicate a development database; other conventions: ‘A’ for acceptance/UAT, and ‘S’ for staging). This blog assumes your new development database has been created and subsequently primed using production data (see my other blog posts on how to accomplish this).

Changing the logical name of a database is actually quite straight forward; you only need to change two things:

  1. The site_name column in the dbglobal table. This value represents the logical name of your user database, e.g. ‘LON’.
  2. The id field of every stateful record in the database.

If you set your site name to ‘LOND1′, ClearQuest will expect that the id of a stateful entity will also begin with ‘LOND1′; and rightly so. However, because we’ve taken a backup from a database named ‘LON’, all of our ids start with ‘LON’, not ‘LOND1′. We need to run some SQL that finds all the stateful entities in our user database and changes their id such that it starts with ‘LOND1′ instead of ‘LON’.

There are a few ways you could do this. You could write a procedure in your vendor’s transactional query language (i.e. Transact/SQL, PL/SQL, etc) to change all the ids programmatically, in a loop. However, I choose to use a slightly different method that involves two simple steps:

  1. Running a SQL statement that is formatted in such a way that it returns other SQL statements (as opposed to say, returning data).
  2. Running the SQL statements returned from the step above.

I’ve provided examples for how to accomplish this in SQL Server, Oracle and DB2 below:

SQL Server

The following has been tested on SQL Server 2000 and 2005.

loading http://blogs.onresolve.com/public/download/tnelson/cq/change_logical_db_name.sqlsrv.sql…

Oracle

The following has been tested on Oracle 10g. It should work on 9i and 11i, but won’t work on 8i as it doesn’t have named support for INNER JOINs.

loading http://blogs.onresolve.com/public/download/tnelson/cq/change_logical_db_name.ora.sql…

DB2

The following has been tested on DB2 v8. It should also work fine on other versions.

2 comments to Changing the logical name of a ClearQuest user database.

  • ckk

    hello:
    I use your examples in SQL server2000,but it prompt “object name” is of no effect, as follows:

    UPDATE ancillary_change SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE app_deployment_record SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE application_change SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE application_release SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE application_test_record SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE change_acceptance_record SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE deployment_record_t SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE release_schedule SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE solution_review SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE task SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;
    UPDATE test_record SET id = ‘Ros’ + RIGHT(id, 8) WHERE dbid 0 AND LEFT(id, 5) ‘Ros’;

    look forward to your return.Thank you very much!

  • Hi there. I’m not exactly sure what you mean by ‘prompt “object name” is of no effect’. Can you paste the exact error message you’re getting?

Leave a Reply