All you ever wanted to know about ClearQuest IDs…


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: ids, dbids and 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:

  1. If you can create it in ClearQuest (i.e. submitting a new defect), it’s user data, and it will have a dbid field that is greater than 33554432.
  2. If you can create it in ClearQuest Designer (i.e. adding a new state), it’s metadata, and it will have an id field 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:

  1. 33554432 is 0×2000000 in hex.
  2. 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 dbglobal

Every ClearQuest user database has a table called dbglobal, which will only ever contain a single row. The table looks like this:
dbglobal table
We’re interested in the two columns that drive ClearQuest’s sequence number generation for stateless and stateful entities: next_aux_id and next_request_id.

next_aux_id

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 next_aux_id.

next_request_id

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 site_name was CLSIC, the newly generated ID would be CLSIC00125331.

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:
select generated ids
The SQL will be slightly different depending on your database vendor.

SQL Server
loading http://blogs.onresolve.com/select_generated_id_dbid.sqlsrv.sql…
Oracle
loading http://blogs.onresolve.com/select_generated_id_dbid.ora.sql…
DB2
loading http://blogs.onresolve.com/select_generated_id_dbid.db2.sql…

Generating ids from dbids

(More information to follow…)

Programmatic ID Generation

SQL Server
get_next_aux_id.sqlsrv.sql
loading http://blogs.onresolve.com/get_next_aux_id.sqlsrv.sql…
get_next_request_id.sqlsrv.sql
loading http://blogs.onresolve.com/get_next_request_id.sqlsrv.sql…
get_next_master_id.sqlsrv.sql

2 comments to All you ever wanted to know about ClearQuest IDs…

  • Gabriel Costa

    Very useful post, helped me a lot in the understanding of it.

    Thanks!

  • mjaskilka

    I’m thinking the answer to my question is in the above post, but I don’t quite see it. We had to change the Clearquest ID of a record in the Defect table so I did:
    update defect set id = ‘new #’ where id = ‘old #’;

    This works, but when I do edit > find record, if I plug in the new number it can’t find it, but if I plug in the Old number it was originally created with, it brings up the right record but with the new number. I’m thinking there’s another table that needs to be updated? We chose New # to be a number we knew not to exist in defect. Any ideas? Thanks.

Leave a Reply