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:
site_namecolumn in the
dbglobaltable. This value represents the logical name of your user database, e.g. ‘LON’.
idfield 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:
- Running a SQL statement that is formatted in such a way that it returns other SQL statements (as opposed to say, returning data).
- 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:
The following has been tested on SQL Server 2000 and 2005.
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
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.
Leave a Reply
You must be logged in to post a comment.