2 <html lang="en-us" itemscope itemtype="http://schema.org/Article">
5 <meta name="description" content="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...">
6 <meta name="generator" content="Movable Type 5.2.3">
7 <title>Using PDSQL to update Clearquest databases - Status</title>
8 <link rel="alternate" type="application/atom+xml" title="Recent Entries" href="http://defaria.com/blogs/Status/atom.xml">
9 <link rel="canonical" href="http://defaria.com/blogs/Status/2012/02/using-pdsql-to.html">
10 <meta name="viewport" content="width=device-width,initial-scale=1">
11 <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles.css">
13 <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles_ie.css">
14 <script src="/mt/mt-static/support/theme_static/rainier/js/html5shiv.js"></script>
17 <link rel="start" href="http://defaria.com/blogs/Status/">
19 <link rel="prev" href="http://defaria.com/blogs/Status/2012/01/outlook-top-pos.html" title="Outlook Top Posting">
20 <link rel="next" href="http://defaria.com/blogs/Status/2012/04/perlcritic-1.html" title="Perl::Critic">
21 <!-- Open Graph Protocol -->
22 <meta property="og:type" content="article">
23 <meta property="og:locale" content="en-us">
24 <meta property="og:title" content="Using PDSQL to update Clearquest databases">
25 <meta property="og:url" content="http://defaria.com/blogs/Status/2012/02/using-pdsql-to.html">
26 <meta property="og:description" content="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...">
27 <meta property="og:site_name" content="Status">
28 <meta property="og:image" content="/mt/mt-static/support/theme_static/rainier/img/siteicon-sample.png">
30 <meta itemprop="description" content="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...">
31 <link itemprop="url" href="http://defaria.com/blogs/Status/2012/02/using-pdsql-to.html">
32 <link itemprop="image" href="/mt/mt-static/support/theme_static/rainier/img/siteicon-sample.png">
37 <div id="container-inner">
38 <header id="header" role="banner">
39 <div id="header-inner">
40 <div id="header-content">
42 <a href="http://defaria.com/blogs/Status/">
51 <nav role="navigation">
53 <li><a href="http://defaria.com/blogs/Status/">Home</a></li>
62 <div id="content-inner">
63 <ul class="breadcrumb breadcrumb-list">
64 <li class="breadcrumb-list-item"><a href="http://defaria.com/blogs/Status/">Home</a></li>
65 <li class="breadcrumb-list-item">Using PDSQL to update Clearquest databases</li>
67 <div id="individual-main" class="main" role="main">
68 <article id="entry-1971" class="entry entry-asset asset hentry">
69 <div class="asset-header">
70 <h2 itemprop="name" class="asset-name entry-title">Using PDSQL to update Clearquest databases</h2>
71 <footer class="asset-meta">
72 <ul class="asset-meta-list">
73 <li class="asset-meta-list-item">Posted on <time datetime="2012-02-13T15:10:03-08:00" itemprop="datePublished">February 13, 2012</time></li>
74 <li class="asset-meta-list-item">by <span class="author entry-author vcard"></span></li>
77 <li class="asset-meta-list-item">in <a itemprop="articleSection" rel="tag" href="http://defaria.com/blogs/Status/broadcom/">Broadcom</a></li>
83 <div class="entry-content asset-content" itemprop="articleBody">
84 <h2>Using PDSQL to update Clearquest databases</h2>
86 <p>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.</p>
88 <p>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.</p>
89 <h3>Running PDSQL</h3>
91 <p>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:</p>
94 $ pdsql -u <username> -p <password> -v ss -s <server> -db <database>
97 <p>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:</p>
100 Ltsdo-adefaria:cat /mcsi/scm_tools/cq/cqsql.sh
102 if [ $# -ne 1 ]; then
103 echo "Usage: cqsql <db>"
107 if [ -n "$(type -p rlwrap)" ]; then
111 $rlwrap pdsql -u <username> -p <password> -v ss -s <server> -db $1
114 <p>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!</p>
116 <h3>Issuing SQL commands</h3>
118 <p>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 <tablename>;. These commands show the table names in the database and the columns in the a particular table.</p>
120 <p>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).</p>
122 <div class=code><pre>
123 >select count(*) from defect where reproducible is not null;
128 >select reproducible from defect where reproducible is not null;
144 >update defect set reproducible = 'NO' where reproducible is null;
149 <p>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!</p>
151 <h3>Setting a reference field</h3>
153 <p>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.</p>
155 <p>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.</p>
157 <div class=code><pre>
160 dbid is_active version lock_version locked_by ratl_mastership ratl_keysite name
161 0 NULL 1 0 0 0 0 NULL
162 35541119 1 1 0 0 16777313 16777313 Grade A
163 35541121 1 1 0 0 16777313 16777313 Grade B
164 35541123 1 1 0 0 16777313 16777313 Grade C
165 35541125 1 1 0 0 16777313 16777313 Grade D
166 35541127 1 1 0 0 16777313 16777313 N/A
168 >select certification from defect where id='DB00031023';
173 >update defect set certification=35541127 where id='DB00031023';
176 >select id from defect where id='DB00031024' and certification = 0;
182 >select id from defect where id='DB00031024' and certification = 1;
186 >select count(*) from defect;
190 >select count(*) from defect where certification = 0;
194 >update defect set certification = 35541127 where certification= 0;
198 <nav class="page-navigation entry-navigation pagination content-nav">
199 <ul class="page-navigation-list">
201 <li class="page-navigation-list-item page-navigation-prev"><a rel="prev" href="http://defaria.com/blogs/Status/2012/01/outlook-top-pos.html" title="Outlook Top Posting">Previous entry</a></li>
204 <li class="page-navigation-list-item page-navigation-next"><a rel="next" href="http://defaria.com/blogs/Status/2012/04/perlcritic-1.html" title="Perl::Critic">Next entry</a></li>
209 <aside id="zenback" class="zenback feedback">
210 Please paste Zenback script code here.
217 <aside class="widgets related" role="complementary">
218 <nav class="widget-search widget">
219 <div class="widget-content">
220 <form method="get" id="search" action="http://defaria.com/mt/mt-search.cgi">
222 <input type="text" name="search" value="" placeholder="Search...">
224 <input type="hidden" name="IncludeBlogs" value="8">
226 <input type="hidden" name="limit" value="20">
227 <button type="submit" name="button">
228 <img alt="Search" src="/mt/mt-static/support/theme_static/rainier/img/search-icon.png">
234 <nav class="widget-archive-category widget">
235 <h3 class="widget-header">Categories</h3>
236 <div class="widget-content">
239 <ul class="widget-list">
242 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/ameriquest/">Ameriquest (99)</a>
250 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/audience/">Audience (3)</a>
258 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/broadcom/">Broadcom (76)</a>
266 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/gpdb/">GPDB (35)</a>
274 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/general-dynamics/">General Dynamics (61)</a>
282 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/general-electric/">General Electric (13)</a>
290 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/hewlett-packard/">Hewlett Packard (13)</a>
298 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/lynuxworks/">LynuxWorks (162)</a>
306 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/pqa/">PQA (35)</a>
314 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/salira/">Salira (79)</a>
322 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/tellabs/">Tellabs (2)</a>
330 <li class="widget-list-item"><a href="http://defaria.com/blogs/Status/texas-instruments/">Texas Instruments (31)</a>
342 <nav class="widget-archive-dropdown widget">
343 <h3 class="widget-header">Archives</h3>
344 <div class="widget-content">
346 <option>Select a Month...</option>
348 <option value="http://defaria.com/blogs/Status/2016/02/">February 2016</option>
352 <option value="http://defaria.com/blogs/Status/2014/09/">September 2014</option>
356 <option value="http://defaria.com/blogs/Status/2014/04/">April 2014</option>
360 <option value="http://defaria.com/blogs/Status/2014/03/">March 2014</option>
364 <option value="http://defaria.com/blogs/Status/2013/02/">February 2013</option>
368 <option value="http://defaria.com/blogs/Status/2012/09/">September 2012</option>
372 <option value="http://defaria.com/blogs/Status/2012/08/">August 2012</option>
376 <option value="http://defaria.com/blogs/Status/2012/05/">May 2012</option>
380 <option value="http://defaria.com/blogs/Status/2012/04/">April 2012</option>
384 <option value="http://defaria.com/blogs/Status/2012/02/">February 2012</option>
388 <option value="http://defaria.com/blogs/Status/2012/01/">January 2012</option>
392 <option value="http://defaria.com/blogs/Status/2011/10/">October 2011</option>
396 <option value="http://defaria.com/blogs/Status/2011/07/">July 2011</option>
400 <option value="http://defaria.com/blogs/Status/2010/09/">September 2010</option>
404 <option value="http://defaria.com/blogs/Status/2010/08/">August 2010</option>
408 <option value="http://defaria.com/blogs/Status/2010/04/">April 2010</option>
412 <option value="http://defaria.com/blogs/Status/2010/03/">March 2010</option>
416 <option value="http://defaria.com/blogs/Status/2010/02/">February 2010</option>
420 <option value="http://defaria.com/blogs/Status/2009/05/">May 2009</option>
424 <option value="http://defaria.com/blogs/Status/2009/04/">April 2009</option>
428 <option value="http://defaria.com/blogs/Status/2008/07/">July 2008</option>
432 <option value="http://defaria.com/blogs/Status/2008/05/">May 2008</option>
436 <option value="http://defaria.com/blogs/Status/2008/04/">April 2008</option>
440 <option value="http://defaria.com/blogs/Status/2008/03/">March 2008</option>
444 <option value="http://defaria.com/blogs/Status/2008/02/">February 2008</option>
448 <option value="http://defaria.com/blogs/Status/2008/01/">January 2008</option>
452 <option value="http://defaria.com/blogs/Status/2007/12/">December 2007</option>
456 <option value="http://defaria.com/blogs/Status/2007/11/">November 2007</option>
460 <option value="http://defaria.com/blogs/Status/2007/10/">October 2007</option>
464 <option value="http://defaria.com/blogs/Status/2007/09/">September 2007</option>
468 <option value="http://defaria.com/blogs/Status/2007/08/">August 2007</option>
472 <option value="http://defaria.com/blogs/Status/2007/07/">July 2007</option>
476 <option value="http://defaria.com/blogs/Status/2007/06/">June 2007</option>
480 <option value="http://defaria.com/blogs/Status/2007/05/">May 2007</option>
484 <option value="http://defaria.com/blogs/Status/2007/04/">April 2007</option>
488 <option value="http://defaria.com/blogs/Status/2007/03/">March 2007</option>
492 <option value="http://defaria.com/blogs/Status/2007/01/">January 2007</option>
496 <option value="http://defaria.com/blogs/Status/2006/12/">December 2006</option>
500 <option value="http://defaria.com/blogs/Status/2006/11/">November 2006</option>
504 <option value="http://defaria.com/blogs/Status/2006/10/">October 2006</option>
508 <option value="http://defaria.com/blogs/Status/2006/09/">September 2006</option>
512 <option value="http://defaria.com/blogs/Status/2006/07/">July 2006</option>
516 <option value="http://defaria.com/blogs/Status/2006/06/">June 2006</option>
520 <option value="http://defaria.com/blogs/Status/2006/05/">May 2006</option>
524 <option value="http://defaria.com/blogs/Status/2006/04/">April 2006</option>
528 <option value="http://defaria.com/blogs/Status/2006/03/">March 2006</option>
532 <option value="http://defaria.com/blogs/Status/2006/02/">February 2006</option>
536 <option value="http://defaria.com/blogs/Status/2006/01/">January 2006</option>
540 <option value="http://defaria.com/blogs/Status/2005/12/">December 2005</option>
544 <option value="http://defaria.com/blogs/Status/2005/11/">November 2005</option>
548 <option value="http://defaria.com/blogs/Status/2005/10/">October 2005</option>
552 <option value="http://defaria.com/blogs/Status/2005/09/">September 2005</option>
556 <option value="http://defaria.com/blogs/Status/2005/08/">August 2005</option>
560 <option value="http://defaria.com/blogs/Status/2005/07/">July 2005</option>
564 <option value="http://defaria.com/blogs/Status/2005/06/">June 2005</option>
568 <option value="http://defaria.com/blogs/Status/2005/05/">May 2005</option>
572 <option value="http://defaria.com/blogs/Status/2005/04/">April 2005</option>
576 <option value="http://defaria.com/blogs/Status/2005/03/">March 2005</option>
580 <option value="http://defaria.com/blogs/Status/2005/02/">February 2005</option>
584 <option value="http://defaria.com/blogs/Status/2005/01/">January 2005</option>
588 <option value="http://defaria.com/blogs/Status/2004/12/">December 2004</option>
592 <option value="http://defaria.com/blogs/Status/2004/09/">September 2004</option>
596 <option value="http://defaria.com/blogs/Status/2004/08/">August 2004</option>
600 <option value="http://defaria.com/blogs/Status/2004/07/">July 2004</option>
604 <option value="http://defaria.com/blogs/Status/2004/06/">June 2004</option>
608 <option value="http://defaria.com/blogs/Status/2004/05/">May 2004</option>
612 <option value="http://defaria.com/blogs/Status/2004/04/">April 2004</option>
616 <option value="http://defaria.com/blogs/Status/2004/03/">March 2004</option>
620 <option value="http://defaria.com/blogs/Status/2004/02/">February 2004</option>
624 <option value="http://defaria.com/blogs/Status/2004/01/">January 2004</option>
628 <option value="http://defaria.com/blogs/Status/2003/12/">December 2003</option>
632 <option value="http://defaria.com/blogs/Status/2003/11/">November 2003</option>
640 <div class="widget-syndication widget section">
641 <div class="widget-content">
642 <p><img src="http://defaria.com/mt/mt-static/images/status_icons/feed.gif" alt="Subscribe to feed" width="9" height="9" /> <a href="http://defaria.com/blogs/Status/atom.xml">Subscribe to this blog's feed</a></p>
650 <footer id="footer" role="contentinfo">
651 <div id="footer-inner">
652 <div id="footer-content">
653 <nav role="navigation">
655 <li><a href="http://defaria.com/blogs/Status/">Home</a></li>
661 <p class="license">© Copyright 2016.</p>
662 <p class="poweredby">Powered by <a href="http://www.movabletype.org/">Movable Type</a></p>
668 <script src="http://defaria.com/mt/mt-static/jquery/jquery.min.js"></script>
669 <script src="http://defaria.com/blogs/Status/mt-theme-scale2.js"></script>