Tutorial: Using Informix tools to access CMS historical database

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:

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:

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.

10 Comments

  • Mark says:

    Hi there,
    I just thought i better let you know that the following link does not work,
    “DB-Access User’s Guide: http://publib.boulder.ibm.com/epubs/pdf/ct1skna.pdf
    But i did find it via yahoo searching for -“DB-Access User’s Guide ” “ct1skna “-
    Lastely thanks for this Post, it is people like you who make the difference and
    has help me tremendously!

    Regards

    Mark

    • dwalin says:

      Hi Mark,

      Indeed the files had gone missing; IBM is so IBM. Thanks for letting me know, I have uploaded the files to my server so they won’t disappear again.

      Regards,
      Alex.

  • Matt says:

    Hi… I’m trying to use your batch file, and no matter what, TuTTY/plink do not want to run /usr/bin/ksh upon connecting. I can access the UNIX prompt manually and get dbaccess to work just fine, but every time I try to connect with /usr/bin/ksh tacked on the end of a plink line, CMS starts up. Any suggestions? :\

    • dwalin says:

      Hi Matt,

      Try adding another user with shell set to /usr/bin/ksh and connect as this user. See if it helps.

      Regards,
      Alex.

  • Pete says:

    Using the Unload, how do I also get the field names as part of the resulting file? like the row_date, acd, split, etc…

    Great site and great info.

    Thanks,

    • dwalin says:

      Pete,

      There’s probably a way to write an SQL query that will do that but it’ll be messy, with schema retrieval and all. The easiest way is to request only the columns you need, like this:

      echo “row_date,acd,split” > data.csv && cms.bat “select row_date, acd, split from dsplit where split = 1” >> data.csv

      The first part of the command will print the header to a file and the second part will query the actual data and append it to the file.

      Regards,
      Alex.

  • agaver says:

    Hello Dwalin,
    Sorry about this noob question, but where do I get the “dbaccess” binary?
    Is there an open source alternative that I could use?
    Thanks a lot for this great article!
    Best Regards,
    agaver

    • dwalin says:

      Hi agaver,

      Every CMS server should have this binary. It’s not in the default PATH though, so you need to run this command to set up the environment variables necessary for it to work:

      . /opt/informix/bin/setenv

      Note the dot is followed by a space, that’s important. After you run this command, you can use “dbaccess” as described in the article.

      Regards,
      Alex.

  • agaver says:

    Hello Dwalin,

    Thanks again for the help and the quick reply.
    I don’t have access to the Informix Server, and I also don’t know if that server admin would be willing to execute this command and send me the binary over… but I’ll ask and see what type of answer I get… for sure I’ll comment again to let you know.
    Thank you very much again, extremely appreciated!!!
    Best Regards,
    Agaver.

  • Javier says:

    Hi Dwalin,

    We have implemented the script and it works perfectly but sometimes, the files are losing data from the CMS. The log files don’t show us any problem and the file is created without problem.

    Regards.
    Javier

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>