Using PDSQL to update Clearquest databases
Using PDSQL to update Clearquest databases
Often when you add a new field to a Clearquest database you need to tackle the problem of how to update or set the value on all of your old records. Often specialized scripts are created to say set the new field to some known value in all of the older records by using the Clearquest API. This takes time to write such scripts and it takes time to run because all of the action hook code needs to be executed to validate the record, etc. Often this is needless rechecking of already correct values of all of the fields in the record. Updates of this kind can take literally hours to process large numbers of records and have all of that action hook code run for no particular reason.
There is a utility in C:\Program Files\IBM\RationalSDLC\ClearQuest named pdsql. It provides a command line interface to the database directly which you can use to perform updates of fields in a lightning fast manner. The updating of all defect records setting a new field to say "foo" would take literally hours using the Clearquest API but less than a second or two in pdsql.
Running PDSQL
You should have C:\Program Files\IBM\RationalSDLC\ClearQuest in your PATH. When you start up pdsql you need to specify a lot of parameters to connect to the database. Remember you are talking to the database so you must use the true database name, not the Clearquest database name. You can use pdsql -help to see the help usage but usually you'll specify the following parameters:
$ pdsql -u <username> -p <password> -v ss -s <server> -db <database>
Note that the username (-u) is the database username. Also the -db is the database name not the Clearquest database name. The -v ss stands for SQLServer and -s is the server name. for ease I have a simply Bash script to get into pdsql that I've named cqsql.sh:
Ltsdo-adefaria:cat /mcsi/scm_tools/cq/cqsql.sh #!/bin/bash if [ $# -ne 1 ]; then echo "Usage: cqsql" exit 1 fi if [ -n "$(type -p rlwrap)" ]; then rlwrap=rlwrap fi $rlwrap pdsql -u <username> -p <password> -v ss -s <server> -db $1
Once in pdsql you can issue SQL statements. Note that ';' is required to terminate all commands - even quit! Also note there is no command line history and very poor editing. If, for example, you forget and do say an up arrow you have put unprintable characters in your command line and your command will fail. If you use Cygwin and have installed the wonderful utility rlwrap (readline wrap) then you will have a full, bash like command history which is extremely useful!
Issuing SQL commands
Remember, you are talking to the back end database directly so all field names will be the database field names shown in Clearquest Designer - not the field names that Clearquest users use. Some useful, non SQL commands are tables; and columns
Most standard SQL commands work as expected. Here is an example session where I used pdsql to set Reproducible to the string "NO" if it was previously NULL (New short strings are set to NULL when a field is added to a record).
>select count(*) from defect where reproducible is not null;11 >select reproducible from defect where reproducible is not null; reproducible NO NO NO NO NO NO NO NO NO NO NO YES >update defect set reproducible = 'NO' where reproducible is null; 93326 rows affected. >
I used to the count(*) syntax to determine how many of the defect records had reproducible not set to NULL. These would be from any new records created since the action hook code makes sure to set this field to either "YES" or "NO". It's the old records we need to set to "NO" if they were NULL. Since there were only 11 records that were not null I decided to see what they contained. Good, they contain what I expect - "NO"'s and "YES"'s only. Then the simple update command to set reproducible = 'NO' if it was null. This command took a second or two to update 93326 defects!
Setting a reference field
When you look into the database directly you start learning how Clearquest does some of its magic. For a reference field one does not see the key to the reference field in textual form rather one sees an integer field, a dbid. Records have a dbid which allows direct access to the particular record. This way if the textual representation of the key (i.e. Name in the Certification stateless record as shown here) you only need update one record instead of potentially thousands of referenced records.
So then how do you change a reference field? The trick is to find the dbid for that stateless record and plop it into the record that references it. Unfortunately, when the stateless record Certification was created its internal table name was never changed from the default "new1" so we need to use the database name for this table - new1. We select the fields for new1 to reveal that 35541127 is the dbid for the "N/A" record in Certification. We then do a small test changing only one record - DB00031023 - carriercertification reference from 0 -> 35541127. Next I checked that this defect (DB00031023) had a Certification of "N/A" in the Clearquest client. It did. So I know this method works.
>select * from new1;
dbid is_active version lock_version locked_by ratl_mastership ratl_keysite name
0 NULL 1 0 0 0 0 NULL
35541119 1 1 0 0 16777313 16777313 Grade A
35541121 1 1 0 0 16777313 16777313 Grade B
35541123 1 1 0 0 16777313 16777313 Grade C
35541125 1 1 0 0 16777313 16777313 Grade D
35541127 1 1 0 0 16777313 16777313 N/A
>select certification from defect where id='DB00031023';
certification
0
>update defect set certification=35541127 where id='DB00031023';
1 rows affected.
>select id from defect where id='DB00031024' and certification = 0;
id
DB00031024
>select id from defect where id='DB00031024' and certification = 1;
id
>select count(*) from defect;
93407
>select count(*) from defect where certification = 0;
93026
>update defect set certification = 35541127 where certification= 0;
93025 rows affected.