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.

Avaya Cms Odbc Driver Download

AVAYA INFORMIX ODBC DRIVER DOWNLOAD - My boss want me to creat him an account so other department can use that account to run some kind of script to query the data in the informix database and put it into another Oracle databse, but I don't want the guy to change the information on our CMS Informix database like deleting something Hi, The problem I ran. Avaya CMS R16 ODBC and JDBC February 2010 5 Preface Avaya Call Management System (CMS) is an appl ication for businesses and organizations that use Avaya communication servers to process large volumes of telephone calls using the Automatic Call Distribution (ACD) feature. Avaya CMS supports solutions for routing and agent.

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:

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:

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:

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

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.

Our General Recommendation

Options have changed over time. The version (R#.#) of your CMS is key to everything else.

• For CMS R16.2 or later -- The optimal solution will be specific to your environment and deployment. We can assist in narrowing the field of options, and recommend the optimal solution.

• For CMS R16.1 or earlier -- As discussed in some detail below, the optimal and recommended solution is to install the currently shipping Multi-Tier server components on the CMS host, and the version-matched Multi-Tier client components on ALL client hosts.

Ibm Informix Odbc Driver 64 Bit

We can assist with installation and/or upgrade, either as Professional Services or through a Support Case.

The Details

Options have changed over time. The version (R#.#) of your CMS is key to everything else.

• For CMS R16.2 or later -- Any of our Single-Tier drivers may be used in the usual way, and our Multi-Tier solutions may be deployed in 'three-tier' style. None of our components may be installed on the CMS R16.2 host itself, but such installation is not required for successful connections.

• For CMS R16.1, R16.0, and R15.x -- Any of our Single-Tier drivers may be used (subject to limitations described below), and the server-side components of our Multi-Tier solutions may be upgraded (or installed) on the CMS host itself. This is fully permitted by all Avaya maintenance agreements.

• For CMS R14.x, and all previous -- The server-side components of our Multi-Tier solutions may be upgraded (or installed) on the CMS host itself, permitting access from any Multi-Tier-supported client environment. This is fully permitted by all Avaya maintenance agreements.

Some Explanation

Through CMS R14, the Informix configuration shipped by Avaya was set to accept client connections only from processes running on the same physical host -- so no network client connections were possible except through our Multi-Tier Universal Data Access components, the server-side components of which Avaya OEM licensed and shipped pre-installed on the CMS server host. This additional level of database security was part of the original reason that Avaya chose our solution.

This configuration prevents use of any 'Wire Protocol' driver, any of our Single-Tier ('Lite' or 'Express') solutions, and indeed, any solution from any vendor which does not include either (a) installing some components on the CMS host, or (b) reconfiguring the Informix instance to accept such remote client connections.

Avaya Cms Informix Odbc Driver For Mac 64-bit

VERY IMPORTANT
The pre-installed OpenLink components on the CMS host are typically from our Release 3.x or 4.x, and occasionally from our Release 5.x, all of which have been deprecated for some time.
The version of the Multi-Tier Generic Client on the data consumer side must be matched to the version of the Multi-Tier Request Broker running on the target host(s) (i.e., the CMS appliance). Mixing Multi-Tier Client components from one Release with Multi-Tier Server components from another Release is untested and unsupported, and is not recommended.

You may continue to use any existing server-side components under the existing license, if your client-side requirements permit (most significantly, if you do not require 64-bit Windows client support, for which Multi-Tier Release 6.x is required). If you do not have the appropriate version-matched client-side installers, you may need to enroll in a Maintenance & Support Agreement to gain access to such deprecated installers.

Our currently shipping Release 6.x ODBC Generic Clients are available in both 32-bit and 64-bit form, for all current versions of AIX, Linux, Windows, Mac OS X, Solaris, HP-UX, and others. 32-bit and 64-bit Generic Clients are also available for use with JDBC or ADO.NET client applications, running on most versions (old and new) of AIX, Linux, Windows, Mac OS X, Solaris, HP-UX, and others.

Most users are best off upgrading any and all existing OpenLink components (both client- and server-side) to our currently shipping Release. Such upgrade or installation falls within Avaya's 'permitted use' and will not disrupt any maintenance or support agreements, on any CMS R16.1 or earlier.

The license which may be needed from us is for the current release Multi-Tier Request Broker and Informix-specific Database Agent. The price for this license is impacted by the number of concurrent database sessions, the number of concurrent client hosts, the number of processor cores in the Broker host, and some other factors which do not generally apply to CMS environments.

Further Options

Users may choose to acquire our Single-Tier ODBC drivers and licenses for the relevant client host(s), with the following caveats --

• Single-Tier drivers are licensed per-installed-host, and can cost significantly more than the Multi-Tier option which license is based more on concurrent usage.

• Single-Tier drivers are licensed entirely on the client-side, and can connect to any number of remote DBMS instances, subject to licensed concurrency limits.

• Single-Tier drivers do require that the target DBMS instances be configured to accept 'sockets' based connections from the relevant client hosts. This can be a significant security hole.

Or, rather than installing/upgrading Multi-Tier server components on the CMS host, you may choose to use a 'gateway' host for a 'three-tier' deployment --

• The DBMS instances must still be configured to accept 'sockets' based connections, but they need only be permitted from the 'gateway' host, to which all Generic Client data consumers would connect. This may help mitigate the security concerns.

• All Generic Clients must then be upgraded to match the Request Broker and Database Agent versions on this 'gateway' host, and the Informix Client SDK must also be installed on the 'gateway' (but not on the individual client hosts).

The last option, with the lowest overall performance, the most susceptible to compatibility issues through the connectivity chain, and often the highest licensing expense, is to set up a multi-level gateway, with the data consumer (e.g., 64-bit Microsoft SQL Server) connecting to our 64-bit Release 6.x Multi-Tier Generic Client ODBC Driver; thence to our 32-bit Release 6.x Multi-Tier Request Broker and Bridge Agent for ODBC Data Sources, which bind to the 32-bit Generic Client ODBC Driver which matches the Multi-Tier Request Broker and Informix-specific Database Agent which are already present and licensed on your CMS host. Our Professional Services and Support teams are ready, willing, and able to assist you in deploying, testing, and pricing this solution.