I’ve done some pretty masochistic ClearQuest database migrations in my time. By masochistic I mean that instead of using the Rational recommended approach for a database migration (I don’t even know what that is. Fiddling around with the import/export tool? No thanks!), I’ll write routines in Transact/SQL or PL/SQL (SQL Server and Oracle’s procedural language extensions to SQL, respectively) to manually coerce the data into shape. Why? Tends to be more fun. It’s a lot faster than any other approach, too.
The following SQL snippets are useful for generating three types of ClearQuest database identifiers:
master_ids. The first two apply to user databases, and refer to the stateful record IDs (e.g.
CLSIC000000523) and the ubiquitous
dbid column that every record has. The latter applies to ids used by schema ‘metadata’, i.e. core ClearQuest tables/entities that store information about the schema.
16777216 and 33554432: ClearQuest’s Magic Numbers
If you’re prone to digging around in the back end of your database, you may have noticed that
dbid columns always seems to contain a value like ’3355xxxx’, or columns that refer to underlying ClearQuest schema tables, like
defect.state, always seem to contain a value like ’1677xxxx’. This isn’t a coincidence — there’s actually a very simple algorithm behind IDs that are generated for ClearQuest entities/objects:
- If you can create it in ClearQuest (i.e. submitting a new defect), it’s user data, and it will have a
dbidfield that is greater than 33554432.
- If you can create it in ClearQuest Designer (i.e. adding a new state), it’s metadata, and it will have an
idfield that is greater than 16777216.
If you think these numbers are a little esoteric, you’d be right, the decimal representations of the numbers are esoteric. If we look at their values in hex, though, things become a bit more obvious:
- 33554432 is 0×2000000 in hex.
- 16777216 is 0×1000000 in hex.
So, the algorithm behind ClearQuest ID generation is little more than some simple arithmetic: add the current sequence number to the relevant offset, and wallah, you’ve got yourself a new ID. How do you derive the current sequence number? Glad you asked…
Tying it together with
Every ClearQuest user database has a table called
dbglobal, which will only ever contain a single row. The table looks like this:
We’re interested in the two columns that drive ClearQuest’s sequence number generation for stateless and stateful entities:
As soon as you begin the submission of a stateless entity, ClearQuest will generate a unique
dbid for the new record by adding 33554432 (0×20000000 in hex) to the current value of
As soon as you begin the submission of a stateful entity, ClearQuest will generate a unique
id for the record by taking the current value of
next_request_id, converting it to a string and padding it with zeroes if it’s shorter than eight characters, and then prepending the
site_name (which is also stored in
dbglobal). For example, if
next_request_id had a value of 125331, and your
CLSIC, the newly generated ID would be
That’s all well and good for the
id field, but how does it then derive the
dbid that’ll also be used by the entity? By simply adding the value of
next_request_id to the 33554432 offset! So, continuing with the example above, our
CLSIC00125331 record would have a
dbid of 33679763 (33679763 – 125331 = 33554432).
Knowing this, we can derive some useful SQL statements that’ll allow us to set the
id value of an entity based on its
dbid, or set the
dbid value of an entity based on its
id. This is particularily useful during adhoc data migrations when you’ve tweaked the
id values of imported records and need their
dbids to match (if they don’t follow the ‘dbid = RIGHT(id, 8) + 33554432′ algorithm above, ClearQuest will refuse to display the entities).
Verifying the logic
Before I present the update statements, here are some select statements you can run to verify the ID generation logic. In the example, the stateful entity I’m selecting against is called
application_change; you would replace this with whatever your stateful entity name is instead. When you run it, you should see that the values in the first and second columns are identical, as are the values in the third and forth, as can be seen in the diagram below:
The SQL will be slightly different depending on your database vendor.
(More information to follow…)
Programmatic ID Generation
2 comments to All you ever wanted to know about ClearQuest IDs…
Leave a Reply
You must be logged in to post a comment.