avaya.dwalin.ru

Following my previous article, I decided to expand on this topic; I feel it may prove useful for people who don’t want to (or can’t) mess with ODBC in CMS to still have access to historical and administrative data.

Here’s how to do it.

As you probably know CMS uses Informix IDS (Informix SE before CMS R9) as its main database. Informix has a lot of features, one of them is availability of interactive command-line client named ‘dbaccess’. This program has two modes: one is text-based UI interface with menus and built-in query editor that can be used to test queries before deploying in production; the other one is line-based interface that can be used for scripting purposes. It is the second mode that is of most interest to us.

In order to use dbaccess, we need to set up the environment first. Informix relies on having several environment variables defined:

• DB_LOCALE contains locale definition for database; CMS uses UTF-8 since time immemorial.
• INFORMIXDIR should contain path to Informix root directory. In CMS, it is /opt/informix.
• INFORMIXSERVER defines name of the server instance to use. For CMS below R9, it is ‘cms_se’, for R9+ it is ‘cms_ol’.
• ONCONFIG points to Informix IDS configuration file. Not used below R9.
• PATH adds /opt/informix/bin to current executable path.

To avoid setting all these variables manually every time, Avaya has provided a helper script that is available with CMS R9 and above. It contains no commands except those setting environment variables, it is standard and it can (and should) be used each time you need to access Informix. In ksh or bash you do it by including it with a dot command:

. /opt/informix/bin/setenv

Note that there should be one or more spaces between the dot and file name, otherwise the command won’t work.

Having set the environment, we can now use dbaccess, first in UI mode:

dbaccess cms@cms_ol

This command tells dbaccess program to connect directly to database cms on the server cms_ol, which is default database and what we actually need. This yields a screen like this:

In turn, Query-Language menu gives us the following options:

Out of these, I find most useful the New-Run-Modify trio as they give really easy way to write and test SQL queries:

The editor itself is a bit kludgy and its interface takes some time and tries to get used to; however it’s not that ugly as to make it unusable. Its main advantage is that you can actually work on the query, fixing errors and perfecting it in several takes without having to save it somewhere else and mess with copy-paste, or type it all over every time which can get really annoying after several attempts at some long query.

The other side of dbaccess is more useful if you want to do scripting with it. It is actually quite easy to use, too:

This command line interface accepts any SQL statement that is supported by Informix database, and more. There is a lot of IBM-esque documentation for Informix that can be hard to navigate through; I would recommend having these documents at the ready in case you need to look something up:

• DB-Access User’s Guide
• Guide to SQL: Reference
• Guide to SQL: Syntax

Yes and file names are so self-explaining, too. Gotta love IBM.

My pet peeve with dbaccess is that it doesn’t separate its output from data dumps in any way, so a query result may look like this:

The problem is that dbaccess is interactive, that is, intended to be used by humans rather than computers; if you want to use dbaccess for scripting you may have to go through chores of parsing dbaccess output to separate actual grain from chaff. But don’t worry, there’s a trick that we can use to avoid all this mess: UNLOAD TO ‘file’. It looks like this:

And here it is, the data I requested:

Let’s take a look at it:

However I was talking about scripting, but that’s real easy when you know your shell:

But wait, there’s more! Using SSH we can run these commands remotely on the CMS without littering and get only the data we need:

And finally, combining it all in one package, here’s the script we can run on client side to get data from CMS:

#!/bin/sh

USER=dwalin
HOST=cms

ssh $USER@$HOST /usr/bin/ksh <<END
. /opt/informix/bin/setenv
CLIENT_LOCALE='en_us.utf8'
export CLIENT_LOCALE
dbaccess cms@cms_ol - > /dev/null 2>/dev/null <<ENDQUERY
unload to '/tmp/dbaccess.out' delimiter ','
$1
ENDQUERY
cat /tmp/dbaccess.out
rm /tmp/dbaccess.out
END

This of course is shell script that can be used in Unix, Linux or Mac OS X. Here’s Windows .bat version:

@echo off

SET SESSION=cms

echo . /opt/informix/bin/setenv>%TEMP%\dbaccess.scr
echo CLIENT_LOCALE='en_us.utf8'>>%TEMP%\dbaccess.scr
echo export CLIENT_LOCALE>>%TEMP%\dbaccess.scr
echo dbaccess cms@cms_ol - ^>/dev/null 2^>/dev/null ^<^<END>>%TEMP%\dbaccess.scr
echo unload to '/tmp/dbaccess.out' delimiter ','>>%TEMP%\dbaccess.scr
echo %*;>>%TEMP%\dbaccess.scr
echo END>>%TEMP%\dbaccess.scr
echo perl -n0p -i -e 's/\n/\r\n/g' /tmp/dbaccess.out>>%TEMP%\dbaccess.scr
echo cat /tmp/dbaccess.out>>%TEMP%\dbaccess.scr
echo rm /tmp/dbaccess.out>>%TEMP%\dbaccess.scr

plink -batch -m %TEMP%\dbaccess.scr -load %SESSION% /usr/bin/ksh
del %TEMP%\dbaccess.scr

For this script to work, you will need to have PuTTY (or TuTTY) installed and configured with at least one saved session; in this example I was using session named ‘cms’ but you can change it to whatever suits your environment best. I have already posted a tutorial on getting TuTTY up and ready (Connecting to CMS terminal with TuTTY); the only additional step is to download plink.exe from PuTTY download page and place it somewhere in %PATH%.

Now you can try running it:

Voila!

Of course, it is not that useful to have a printout in Command Prompt window so it would make sense to save this output to some file using redirection:

I don’t have Microsoft Office installed in Windows VM but I’m pretty sure this file should have opened in Excel without problems. Neat eh?

Of course there can be a lot of settings to tune, but basically it’s there. And it can be very powerful tool indeed: while getting access to CMS data via ODBC is more official and supported by Avaya, setting it up can be a real chore and many people will decide to do without CMS data just to avoid messing with ODBC. The best part about dbaccess solution is that there is absolutely no administrator superpowers involved. That’s right folks, any ordinary system user can use this approach to offload the data he or she needs in a robust and secure way without even asking anybody! Ain’t it cool?

One more word regarding non-English users: CMS uses UTF-8 encoding to store data internally, which means it supports all (or almost all) languages out there. Unfortunately, not all systems support UTF-8 yet, including Windows; I am not totally sure but applications like Word or Excel may or may not support UTF-8. I don’t have Windows Excel so I have no way of checking it. If you have any trouble with non-Latin characters in data, drop me a line and we’ll try to think up something. For European languages like German, French or Spanish we can use default iconv tables that come with Solaris; other languages and character sets are less lucky so there may be some extra work involved. I’m always glad to help and my fee is moderate; drop me an e-mail and we’ll get there.

And finally, here are links to downloadable copies of the scripts above:

• Shell script: http://avaya.dwalin.ru/wp-content/uploads/2011/08/cms.sh
• Windows batch script: http://avaya.dwalin.ru/wp-content/uploads/2011/08/cms.bat

I don’t like these HTML escape characters when there are so many angle brackets in my scripts…

All right, that’s it for today. Hope you’ll find this article worth your time.