Wednesday, January 20, 2010

Now you have an alternative to FreeTDS - GbsNcoSql

Thanks to Venkat Saranathan, who developed this tool and wrote the description. He also wrote 'gbscmd' for use with ITM 6.x.

Many times, you need to analyze the Omnibus data using scripts. For this purpose, Netcool Omnibus provides nco_sql utility that lets you run the Omnibus queries from the command line. However, one of the main limitations of the nco_sql utility is that it is nearly impossible to parse the output. The difficulties include single record spanning across multiple lines, no delimiter option to name a few.
We at Gulf Breeze worked on this requirement sometime back and developed an utility to address some of these shortcomings. The utility is written in Java and can be run with the Java Runtime that comes with Omnibus. Some of the benefits/features of this utility includes the following.
  • Currently, it can run any Omnibus "SELECT" queries only. Though the utility could potentially be modified to run other queries, the idea is to use the nco_sql utility for all other type of queries.
  • Consistent one-record per line output
  • Ability to specify your own delimiter with "-d" switch.
  • Ability to suppress header output with "-n" switch.
  • Ability to specify queries in a file or read from command line.
  • No need to compile any FreeTDS drivers. It comes with the necessary FreeTDS drivers.
  • Authorization information kept in a separate file and can be specified with -a switch. You don't need to specify the password in your scripts.
  • Platform independent and works with the IBM and Sun JREs.
Here is a sample usage of this utility.
usage: java -jar GbsNcoSql.jar -a [-n] [-d ] [-f | -q ]

-a File name containing authorization information
-d Delimiter
-f File name containing SQL query text
-h Displays the help information
-n No header output
-q Query text
Here is a sample data.
$ java -jar GbsNcoSql.jar -a my.auth -q "SELECT Node,Tally from alerts.status"
You can create an authorization manually in text editior. Here is how my authorization file looks. Please note that in the value for server is the hostname NOT the object server name.

$ cat my.auth

No comments: