Accessing ObjectServer Databases with Perl

By Anthony Mico

It’s common practice within OMNIbus implementations to create custom databases and tables within the ObjectServer. These databases are typically used to store information such as server maintenance windows, escalation routes and support contact details. Much of this type of data tends to be dynamic in nature and as a result requires regular updates. Being able to manage these updates programmatically can offer a number of benefits, whether it is just simplifying the data input process or being able to automate it entirely.

One possible way of providing this automation is to embed calls to the native nco_sql utility within the code. Whilst this is functional it isn’t always ideal and certainly doesn’t offer the rich database functionality many of today’s programming languages provide.

In this article we will demonstrate how Perl can be configured and used to access the ObjectServer.

Perl DBI/DBD

DBI is a database access module for the Perl programming language. It defines a set of methods, variables and conventions that provide a consistent application interface, regardless of the underlying database being used (which could be Oracle, MySQL, MS SQL Server etc). Although DBI provides the common interface, it is database driver modules (DBD) that actually implement the database connectivity layer. As the ObjectServer implements the TDS (Tabular Data Stream) protocol, we can use the DBD::Sybase module.

Linux

Client Software

Although the DBI and DBD modules provide what is required from a Perl perspective, the DBD module requires the underlying database client libraries in order to access the database.

On Linux there are a couple of options:

For the purpose of this article, we will use the FreeTDS libraries. The CentOS 5 distribution used in this example came bundled with Perl v5.8.8 with DBI v1.52.

Gather the required software

DBD::Sybase from   http://www.peppler.org/freeware/dbd-sybase.html (version 1.08)

FreeTDS from   http://www.freetds.org/ (stable version 0.82)

Linux patch from   ftp://mirrors3.kernel.org/metalab/ALPHA/freetds/old/0.82/cspublic.BLK_VERSION_150.patch

Install FreeTDS

Note the following steps assume that gcc is installed and that all the required software has been copied into /tmp.

cd /tmp
tar xvfz freetds-stable.tgz
cd freetds-0.82
./configure
make
make install
make clean

Apply the patch

cd /usr/local/include
patch cspublic.h /tmp/cspublic.BLK_VERSION_150.patch

Update the FreeTDS configuration file

Assuming a default install of FreeTDS, the general configuration and server connection details are maintained in the file /usr/local/etc/freetds.conf.

Add an entry for the target ObjectServer at the bottom of the file:

[NCOMS]
host = otto
port = 4100
tds version = 5.0

The text size entry in the [global] section should also be commented out, as shown below:

; text size = 64512

Test the connection

At this point we should now be able to connect to the ObjectServer using the FreeTDS provided utility tsql, as shown below:

[ netcool@otto ~]$ tsql -S NCOMS -U root
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password:
1>

Install DBD::Sybase

With FreeTDS installed, the DBD::Sybase module can now be installed.

cd /tmp
tar xvfz DBD-Sybase-1.08.tar.gz

cd DBD-Sybase-1.08

export SYBASE=/usr/local

perl Makefile.PL
make
make test
make install

Both the make and make test commands are likely to generate a number of warning messages, which can be ignored.

Setting the environment

In order for DBD::Sybase to work correctly, it must be able to locate the client libraries. The default FreeTDS configuration places the libraries into /usr/local/lib, although this location can be overridden during the configure step by specifying a --prefix flag.

To ensure this directory is in our environment, we can update the LD_LIBRARY_PATH variable, as shown below:

[ netcool@otto ~]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib

Alternatively, this can be done on a more permanent basis by using ldconfig, as shown below:

[ root@otto ~]# cd /etc/ld.so.conf.d
[ root@otto ld.so.conf.d]# echo /usr/local/lib > freetds.conf
[ root@otto ld.so.conf.d]# ldconfig

Example Perl script

With the DBD::Sybase module and FreeTDS libraries in place, we should now be able to connect to the ObjectServer via Perl. The below script shows a basic example of querying the alerts.status table:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# Build up the DSN
my $user = ‘root’;
my $password = ”;
my $server = ‘NCOMS’;

my $dsn = “dbi:Sybase:server=$server”;

# Connect to the database
my $dbh = DBI->connect($dsn, $user, $password, { AutoCommit => 0 })
|| die “Failed to connect!”;

# Define the SQL statement to be executed
my $sql = qq(select Node, Severity, Summary from alerts.status order by Severity desc);

# Prepare it
my $sth = $dbh->prepare($sql);

# Execute it
$sth->execute;

# Check for errors
if ( $sth->errstr )
{
print $sth->errstr, “n”;
}
else
{
# Process the output
while ( my ($node, $severity, $summary) = $sth->fetchrow_array )
{
printf(“%-25s %-5s %-s”, unpack(‘A*’,$node), $severity, unpack(‘A*’,$summary));
}
}

# Disconnect from the database
$dbh->disconnect;

Although pretty basic, there are a couple of lines worth covering in more detail.

It is standard DBI practice to check the value of $sth->err to determine whether statement execution was successful or not. However within the ObjectServer context, this value does not appear to get set. Fortunately, any error messages generated by the ObjectServer are captured and can be retrieved with $sth->errstr.

Another thing to be aware of is that the ObjectServer returns VARCHAR type fields in a null terminated format. The Perl unpack function has been used in the example to remove this termination, as highlighted below:

printf("%-25s %-5s %-s ", unpack('A*',$node), $severity, unpack('A*',$summary));

More information about programming the Perl DBI, along with code samples, can be found at   http://www.orb-data.com/index.php?pageId=371.

Windows

Perl can also be used on Windows to access the ObjectServer. If you don’t currently have it installed, the Perl distribution provided by ActiveState (  http://www.activestate.com) tends to be the standard employed.

Install DBI

Assuming the server has internet connectivity, the interactive Perl Package Manager (ppm) utility can then be used to install the DBI module directly from the ActiveState repository:

ppm> install DBI
ppm> quit

Install DBD::Sybase

The author of DBD::Sybase, Michael Peppler, provides an ActiveState version of the module. At the time of writing, the latest version is DBD-Sybase-1.07_01.zip, which can be downloaded from   http://www.peppler.org/downloads/ActiveState.

Once downloaded, the file should be unzipped to a temporary directory such as C:Temp.

To install the module, open a command window, change to the temporary directory and then enter the following command:

ppm install DBD-Sybase.ppd

Client libraries

As with Linux/Unix, the underlying database client libraries are required, unfortunately obtaining the libraries on Windows proves to be a little more challenging. By far the easiest method is to use the libraries provided by the 7.1 version of OMNIbus, which can be installed by running the Netcool Installer without any features selected, as shown in the screenshot below:

omnibus1

The client library files provided with version 7.2 have been renamed slightly which causes DBD::Sybase a problem. Although the files can be copied and renamed, not all the required .dlls appear to be included.

Configure the Interfaces file

The Servers Editor utility, which will have been installed by the Netcool Installer, should be used to configure interfaces file with details of the target ObjectServer as per normal, as shown in the below screenshot:

omnibus2

Before attempting to use the DBD::Sybase module, the SYBASE environment variable must be set. Assuming the default location was used when installing the client libraries, the SYBASE variable should be set as follows:

set SYBASE=C:Progra~1Micromusenetcool

Alternatively, the variable can be set directly within the Perl script itself, as shown below:

$ENV{SYBASE} = "C:/Progra~1/Micromuse/Netcool";

Hits: 418