IBM Support

Information about PDSQL and how to use it

Question & Answer


Question

How can administrators use the PDSQL command line tool in IBM Rational ClearQuest for Microsoft Windows environments?

Answer

ClearQuest provides a command line SQL tool in the ClearQuest directory called PDSQL. It can be used to open an SQL session with any database vendor supported by ClearQuest. To use PDSQL, open a command prompt window in the ClearQuest directory. Entering the command "pdsql" will display the syntax information. Here are some examples of using this utilities for each vendor database on different versions of ClearQuest.

Notes:

  • The value for -v must be one of either DB2, ORACLE, SQL_SERVER, or MS_ACCESS. You can also use the abbreviations db2, ora, ss, or access. It is case sensitive.
  • dbUser and dbPass denote the backend database account information.
  • Once connected, "select" statements should be in lower case. PDSQL accepts "select" statements in lower case, but not upper case. It doesn't detect that there is a select statement so there is no error reported and no output, which can be confusing.

    Of course, if a string you are searching on is mixed case or upper case, that will be recognized.

SQL Server on ClearQuest 2003.06.00 through 9.x


-v ss -s <server> -u <dbUser> -p <dbPass> -db <database> [-co "<connect_options>"]

Note: may need connect options (co) if not using the default instance or port 1433, and an empty string is not supported (if empty omit -co option):


PORT=<port_number>

INSTANCE=<instance_name>

Oracle with ClearQuest 2003.06.00 through 2003.06.12


-v ora7 -s <alias> -u <dbUser> -p <dbPass> -co "<connect_options>"

or:

-v ora8 -s <alias> -u <dbUser> -p <dbPass> -co "<connect_options>"

(alias is the SQL*Net or Net8 alias defined for the Oracle database)

Note: may need connect options (separated by semicolon):


HOST=<host>

SID=<sid>

CLIENT_VER=<7, 8.0, 8.1> default is 7 for ora7, 8.0 for ora8

LOB_TYPE=<LONG,CLOB> default is LONG

Oracle with ClearQuest 2003.06.13 through 2003.06.16


-v ora8 -db <sid> -s <host> -u <dbUser> -p <dbPass> -co "<connect_options>"

or:

-v ora9  -db <sid> -s <host> -u <dbUser> -p <dbPass> -co "<connect_options>"

Note: The LOB_TYPE must be defined in the connect options. May need additional connect options if not using the default port 1521:

LOB_TYPE=<lobtype>
PORT=<port_number>



Oracle with ClearQuest 7.x - 9.x


-v ora -db <sid> -s <host> -u <dbUser> -p <dbPass> -co "<connect_options>"

Note: The LOB_TYPE must be defined in the connect options. May need additional connect options if not using the default port 1521:


LOB_TYPE=<lobtype>
PORT=<port_number>

DB2 with ClearQuest 2003.06.00 through 2003.06.12


-v db2 -db <alias> -u <dbUser> -p <dbPass> -co "<connect_options>"

(alias is the cataloged name for the database defined using DB2 Client Configuration Assistant).

DB2 with ClearQuest 2003.06.13 through 9.x


-v db2 -db <dbname> -u <dbUser> -p <dbPass> -s <hostname> -co "<connect_options>"

Note: may need connect options if not using the default port 50000:

PORT=<port_number>

With 7.0.x, may need DB2_SECURE_LOGIN in connect options if server requires encrypted userid and password:


DB2_SECURE_LOGIN

Microsoft Access for ClearQuest 2003.06.00 through 9.x


-v access -db "<some valid access path>" -u admin -co ""

NOTE: For Access and SQL Anywhere, put the <path-to-db-file> in double quotation marks.

SQL Anywhere for ClearQuest 2003.06.13 through 2003.06.15


-v sa -s <server> -u <dbUser> -p <dbPass> -db <database> -co "<connect_options>" -hosts <host1,host2,host3,...> -protocols <TCPIP,IPX,NETBIOS,NAMEDPIPES>

Note: You must specify connect options as SERVER_VER=8.0 to connect to database under SQL Anywhere 8 server. The information about database hosts and protocols is optional. However, it is very useful while connecting to computer not on the VLAN. The database value must be UNC path to the database file on the database server.

May need connect options for SQL Anywhere 8.x:


-co "SERVER_VER=8.0"

Additional notes on PDSQL usage:

For pdsql on-line help and features, just type:


pdsql -help

Pdsql supports all common SQL commands. There are 2 special pdsql commands, "tables" and "columns", that can be used, for example, to see if you are in the right database. Examples:


pdsql> tables;
pdsql> columns table-name;

The table and column names to use in subsequent SQL are those listed by the above commands; there should be no table prefixing necessary, regardless of db vendor.

Sample SQL:


pdsql> update table set new_field = old_field;
pdsql> select new_field from defect;

Commands are executed when the semicolon is read. No explicit "commit" is required in pdsql.

To exit pdsql, use the quit command:


pdsql> quit;

You can use the greater-than sign (>) to store output to a file. Here is an example of how to do this:



C:\pdsql -v ss -s server -u <dbUser> -p <dbPass> -db <database>
 > C:\temp\sqlout.txt
select * from <table>;
quit;

You can use the less-than sign (<) to read the SQL from an input file. This may be useful for very lager SQL statements. Here is an example of how to do this:



C:\pdsql -v ss -s <server> -u <dbUser> -p <dbPass> -db <database>
< C:\temp\sqlin.txt
quit;


You must ensure the sqlin.txt file has a properly formatted SQL statement, for example:


select * from <table>;


Note: You cannot specify both file input and output at the same time. You can only choose one at a time.

[{"Product":{"code":"SSSH5A","label":"Rational ClearQuest"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Command Line Tools","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.0.1;7.1;7.1.1;7.1.2;8.0;8.0.1;9.0","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
01 August 2018

UID

swg21145079