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:
- Sybase are currently offering their Sybase ASE Express Edition product free of charge, which can be downloaded from http://www.sybase.com/linuxpromo
- FreeTDS ( http://www.freetds.org) is an open source implementation of the TDS protocol and provides the required libraries for both Unix and Linux
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:
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:
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