Wednesday, March 12, 2008

Undocumented ITM 6.1 EIB SQL Client

Anyone who as worked with ITM 6.1 is familiar with the SOAP interface for making SQL requests against the EIB database. There is also another tool you can use to issue EIB SQL and runs locally to your TEMS.

In the $CANDLEHOME//ms/bin (%CANDLE_HOME\cms for Windows) there is an executable called kdstsns. This is an EIB SQL client that allows you to run SQL against the EIB database running on the TEMS.

There are a few caveats.

It's undocumented and unsupported and dangerous.
You have to create an SQL file that you want to run in $CANDLEHOME/tables/cicatrsq/SQLLIB directory.

For example if I wanted to list all the actions defined I would create the file actions.sql in $CANDLEHOME/tables/cicatrsq/SQLLIB (%CANDLE_HOME%\cms\SQLLIB for Windows):

The tricky part is each line in this file can not exceed 72 characters so make sure you wrap long SQL statements to 72 characters.

Once you have the file created, run kdstnsns. On Linux and probably Unix you will have to cd to $CANDLEHOME/tables/cicatrsq and run the command using $CANDLEHOME//ms/bin/kdstsns

This program asks for some information, the defaults appear to work fine. Then you are prompted to enter an SQL member name. Here you would type "actions" for the file we created. Next, kdstsns will execute the SQL and display the results (or error). The exit the interactive session simply type "end"

There may be a way to run the command in batch mode. Stay tuned for more information.

No comments: