How to use "pdm_isql" to test connection and SQL queries from CA SDM to the database

Document ID : KB000122402
Last Modified Date : 21/12/2018
Show Technical Document Details
Question:
The command line "pdm_isql" can be used to test the connection from CA Service Desk Manager (CA SDM or CA ITSM) to the database.

This is useful for testing there is a live connection between the CA SDM application server and the database server.

It is most useful for testing that a SQL command passed to the database works as expected. If the command does not produces the results expected from pdm_isql, then it will not produce the correct results when called from CA SDM, Jaspersoft Reports or any other SQL query source.

Another key advantage is that pdm_isql obeys the "pdm" formats, such as "pdmToday" used in queries. These are not available if querying directly in MS SQL.

There is also the option to read from an input file, for large SQL queries.
Environment:
Any version of CA Service Desk Manager (CA SDM/CMDB/ITSM) and a database.
Answer:
Enter the text in blue and bold, replacing your Service Desk username, password and server hostname.

Note to use a Service Desk username and password with sufficient access rights to the pdm utility commands.
This is NOT the database username and password.
Typically the "ServiceDesk" user is created at the time of installation with "ServiceDesk" as a default password by many sites, however, for security reasons this should be changed prior to the system going live into production. 

The following shows a successful connection for a server with hostname "My_Server_123".
Note the "Start with command" syntax, and that this example it has a prefix of "casd_" before the hostname.
 

C:\PROGRA~2\CA\SERVIC~1>pdm_isql

.....................................

. CA Service Desk Manager Interactive SQL

. Start with command: connect userid*"password"@casd_My_Server_123

. Then standard SQL SELECT statements terminated with a semicolon

.....................................

DataDirect OpenAccess SDK Interactive SQL (ODBC) Version 7.2

(c) Copyright 1995-2013 Progress Software Corporation. All rights reserved.

 

Program Locale:English_United States.1252

ISQL> connect ServiceDesk*"ServiceDesk"@casd_My_Server_123;

SQL: connecting to database: My_Server_123...

Elapsed time 48 ms.

ISQL> select * from usp_servers;

id      local_host      timezone        server_id       slump_port      server_type     database_type   upload_path     external_dns_name

platform        description     linked  delete_flag     last_mod_dt     last_mod_by

 

1001    My_Server_123  NULL    28746   2100    0       0       NULL            1       Local System (My_Server_123)   1       0       NULL

NULL

 

Rows selected = 1

SQL: Operation successful.

Elapsed time 10 ms.

Elapsed time: Prepare 4 ms.      Execute 0 ms.   Fetch results 2 ms.

ISQL>
 

Reading large SQL queries from an input file with CMDFILE
You can use the command "CMDFILE <input file name>" to use pdm_isql to read from a file.

The queries must end with a semi-colon in order to run.
The input file may be placed in any location and have any valid name.
 

Example:
Here are three SQL commands in one input file called "Input.txt"

- - - Start of Input.txt

select * from pri;
select * from urg;
select * from imp;

- - - End of Input.txt

The command to run and the results are:

ISQL> CMDFILE Input.txt
Elapsed time 0 ms.
id      sym     enum    service_type    delete_flag     description     last_mod_dt     last_mod_by

500     5       1       NULL    0       LOW Priority    NULL    NULL
501     4       2       NULL    0       MEDIUM-LOW Priority     NULL    NULL
502     3       3       NULL    0       MEDIUM Priority NULL    NULL
503     2       4       NULL    0       MEDIUM-HIGH Priority    NULL    NULL
504     1       5       sdsc:5403       0       HIGH Priority   NULL    NULL
505     None    0       NULL    0       Priority unassigned     NULL    NULL

Rows selected = 6
SQL: Operation successful.
Elapsed time 15 ms.
Elapsed time: Prepare 0 ms.      Execute 0 ms.   Fetch results 0 ms.
id      sym     delete_flag     enum    service_type    description     last_mod_dt     last_mod_by     value

1100    1-When Possible 0       0       NULL    Requires an available response  NULL    NULL    1
1101    2-Soon  0       1       NULL    Requires a temperate response   NULL    NULL    2
1102    3-Quickly       0       2       NULL    Requires a timely response      NULL    NULL    3
1103    4-Very Quickly  0       3       NULL    Requires an escalated response  NULL    NULL    4
1104    5-Immediate     0       4       NULL    Requires an instant response    NULL    NULL    5

Rows selected = 5
SQL: Operation successful.
Elapsed time 16 ms.
Elapsed time: Prepare 0 ms.      Execute 0 ms.   Fetch results 0 ms.
id      sym     delete_flag     enum    description     last_mod_dt     last_mod_by     value

1600    5-One person    0       1       Impacts service used by one person      NULL    NULL    5
1601    4-Small Group   0       2       Impacts service used by a small group   NULL    NULL    4
1602    3-Single Group  0       3       Impacts service used by a single group  NULL    NULL    3
1603    2-Multiple Groups       0       4       Impacts service used by multiple groups NULL    NULL    2
1604    1-Entire organization   0       5       Impacts service used by the entire org  NULL    NULL    1
1605    None    0       0       Impact is unassigned    NULL    NULL    0

Rows selected = 6
SQL: Operation successful.
Elapsed time 0 ms.
Elapsed time: Prepare 0 ms.      Execute 0 ms.   Fetch results 0 ms.
ISQL>








 

Additional Information:
https://comm.support.ca.com/kb/How-to-test-the-CA-Data-Direct-ODBC-driver-to-check-if-its-working/KB000052989