cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
2724
Views
10
Helpful
4
Replies

Query Cisco Unity Databases from Linux.

Good morning Developer friends!!

 

Here is a newbie for DevNet, I respect your work very much and I hope I can be a qualified Cisco Developer.

Right now I am working into Collaboration Stuff.

 

Could you please help me with something?

 

Right now I am using Cisco Unity Connection Tools to query a database in unity connection.

in the query builder (GUI) I am putting this.

 

select count(*) from tbl_notifyq

to get the count,

but I would like to do this automatically in several clusters using a Linux computer.

 

 

Which would be the best way to accomplish the task,?

Note: you don;t have to be very specific, I know "Google is your best fried", but I hope you can give me some over-simplified instructions like.

 

Get this,

download this

use this

be happy!

 

What do you think?

Thank you very much for your time, and have a nice day.

 

 

 

1 Accepted Solution

Accepted Solutions

dstaudt
Cisco Employee
Cisco Employee

I got the below working on Ubuntu 19.10 using the Python IfxPy library:

 

 

import IfxPy

connStr = 'CLIENT_LOCALE=en_US.57372;DB_LOCALE=en_US.57372;SERVER=ciscounity;DATABASE=unitydyndb;uid=Administrator;pwd=password'
conn = IfxPy.connect(connStr, '', '')

sql = 'select count(*) from tbl_notifyq'
stmt = IfxPy.exec_immediate(conn, sql)
dictionary = IfxPy.fetch_assoc(stmt)

print('tbl_notifyq: ',  dictionary[''])
 
IfxPy.free_result(stmt)
IfxPy.free_stmt(stmt)

IfxPy.close(conn)

 

 

- Download Informix SDK: https://www.ibm.com/products/informix/developer-tools?mhsrc=ibmsearch_a&mhq=informix
- Install SDK:

- On Ubuntu 19.10 I had to install libncurses5: sudo apt install libncurses5
- I selected the ODBC Driver, Common Database Utilities and Global Language Support (GLS) only
- I installed to /home/dstaudt/opt/IBM/Informix_Client-SDK (hereafter $INFORMIXDIR)
- Create sqlhosts file in $INFORMIXDIR/etc (important to use 'ciscounity' as the dbservername:)

#dbservername nettype hostname servicename options
ciscounity onsoctcp ds-cuc115.cisco.com 20532

- Install IfxPy: pip3 install ifxpy
- Project homepage: https://github.com/OpenInformix/IfxPy
- Setup your environment so that the following variables are active when the Python script runs (in VS Code I used launch.json):

LD_LIBRARY_PATH: /home/dstaudt/opt/IBM/Informix_Client-SDK/lib:/home/dstaudt/opt/IBM/Informix_Client-SDK/lib/esql:/home/dstaudt/opt/IBM/Informix_Client-SDK/lib/cli
INFORMIXDIR: /home/dstaudt/opt/IBM/Informix_Client-SDK

- I was stuck for a long time trying to get client/DB locales correct (weird locale used: en_US.57372 apparently: utf-8). Eventually I used the dbaccess tool (part of the SDK installed above), which might come in handy for CLI access to the database: https://www.ibm.com/support/pages/how-run-informix-sql-commands-your-rational-synergy-database-unix-or-linux-environment
- The key IfxPy sample that showed how to use the locale options: https://github.com/OpenInformix/IfxPy/blob/58edef9321979b82df177f7ba2e9c49bc71544eb/Examples/Sample1.ipynb
- Key info on how to determine the locale of a database: https://www.ibm.com/support/pages/how-fix-error-message-sql1822-informix-error-code-23197
- Info on how to enable necessary roles (System Administrator/Remote Access) and services for the Unity Connection database proxy: http://www.ciscounitytools.com/Applications/CxN/MessageHunter/Help/MessageHunter.htm#_Toc504638754
- Some info re Unity and Informix ODBC: https://ciscounitytools.com/Applications/CxN/InformixODBC/InformixODBC.html
- Cisco Unity Tools: https://ciscounitytools.com/index.html
-IfxPy usage examples: https://github.com/OpenInformix/IfxPy

 

Full VS Code project attached

View solution in original post

4 Replies 4

dstaudt
Cisco Employee
Cisco Employee

I got the below working on Ubuntu 19.10 using the Python IfxPy library:

 

 

import IfxPy

connStr = 'CLIENT_LOCALE=en_US.57372;DB_LOCALE=en_US.57372;SERVER=ciscounity;DATABASE=unitydyndb;uid=Administrator;pwd=password'
conn = IfxPy.connect(connStr, '', '')

sql = 'select count(*) from tbl_notifyq'
stmt = IfxPy.exec_immediate(conn, sql)
dictionary = IfxPy.fetch_assoc(stmt)

print('tbl_notifyq: ',  dictionary[''])
 
IfxPy.free_result(stmt)
IfxPy.free_stmt(stmt)

IfxPy.close(conn)

 

 

- Download Informix SDK: https://www.ibm.com/products/informix/developer-tools?mhsrc=ibmsearch_a&mhq=informix
- Install SDK:

- On Ubuntu 19.10 I had to install libncurses5: sudo apt install libncurses5
- I selected the ODBC Driver, Common Database Utilities and Global Language Support (GLS) only
- I installed to /home/dstaudt/opt/IBM/Informix_Client-SDK (hereafter $INFORMIXDIR)
- Create sqlhosts file in $INFORMIXDIR/etc (important to use 'ciscounity' as the dbservername:)

#dbservername nettype hostname servicename options
ciscounity onsoctcp ds-cuc115.cisco.com 20532

- Install IfxPy: pip3 install ifxpy
- Project homepage: https://github.com/OpenInformix/IfxPy
- Setup your environment so that the following variables are active when the Python script runs (in VS Code I used launch.json):

LD_LIBRARY_PATH: /home/dstaudt/opt/IBM/Informix_Client-SDK/lib:/home/dstaudt/opt/IBM/Informix_Client-SDK/lib/esql:/home/dstaudt/opt/IBM/Informix_Client-SDK/lib/cli
INFORMIXDIR: /home/dstaudt/opt/IBM/Informix_Client-SDK

- I was stuck for a long time trying to get client/DB locales correct (weird locale used: en_US.57372 apparently: utf-8). Eventually I used the dbaccess tool (part of the SDK installed above), which might come in handy for CLI access to the database: https://www.ibm.com/support/pages/how-run-informix-sql-commands-your-rational-synergy-database-unix-or-linux-environment
- The key IfxPy sample that showed how to use the locale options: https://github.com/OpenInformix/IfxPy/blob/58edef9321979b82df177f7ba2e9c49bc71544eb/Examples/Sample1.ipynb
- Key info on how to determine the locale of a database: https://www.ibm.com/support/pages/how-fix-error-message-sql1822-informix-error-code-23197
- Info on how to enable necessary roles (System Administrator/Remote Access) and services for the Unity Connection database proxy: http://www.ciscounitytools.com/Applications/CxN/MessageHunter/Help/MessageHunter.htm#_Toc504638754
- Some info re Unity and Informix ODBC: https://ciscounitytools.com/Applications/CxN/InformixODBC/InformixODBC.html
- Cisco Unity Tools: https://ciscounitytools.com/index.html
-IfxPy usage examples: https://github.com/OpenInformix/IfxPy

 

Full VS Code project attached

Thank you!

5 Stars!

 

Juan

Took a while to tested I Struggled a lot to make it work in Widows, at the end I could because version of python and win64 drivers and Ifxpy.

If you are interesting in what exactly I am struggling on, please take a look at.

 

https://github.com/OpenInformix/IfxPy/issues/50

 

Then I exactly followed your instructions in Linux and I got ti working,

I got this error:

juan@ubuntu:~/py_works$ python3 cuc_sql.py 
Traceback (most recent call last):
  File "cuc_sql.py", line 4, in <module>
    conn = IfxPy.connect(connStr, "", "")
Exception: [Informix][Informix ODBC Driver][Informix]Network connection is broken. SQLCODE=-25582

That was because the user needed to have the "Remote Administrator" Role assigned.

 

Another disadvantage, the password is sent in clear text.

This is the partial output of a wireshark capture.

sqlexec administrator -pciscopsdt 4.50.FC2 serial -dunitydyndb -fIEEEI DBPATH=//ciscounity DBMONEY=$. CLIENT_LOCALE=en_US.57372 NODEFDAC=no CLNT_PAM_CAPABLE=1 DB_LOCALE=en_US.57372 :.....=...d.e...=..IEEEI..lsrvinfx......+Informix Dynamic Server Version 9.56.UC9W1..#Software Serial Number AAA#...ciscounity_pub....<..............on.........=soctcp.......f...............k......Nt......hq-cuc...../..n.......t./.........%/usr/local/cm/db/informix/bin/oninit....~......}.b....~.	.............Q.......x..DBTEMP../tmp..SHELL..	/bin/bash...SUBQCACHESZ...10..PATH.x/home/juan/.local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin..NODEFDAC..no..OPTOFC..0..........#...c.............c............... select count(*) from tbl_notifyq...1.............	....................(count(*))...................7..................sql_cur2...........	...............	...........................7...............
...............8.8

Do you know how this can be encrypted?

 

dwasserzug1
Level 1
Level 1

I was able to get this to work on RHEL 7.

I'll put my steps if it helps anyone.

 

Prerequisites:

  • Using RHEL 7 x86_64.
  • Python is already installed.
  • Ifxpy is already installed.
  • ncurses libraries are already installed.
  • "Connection Database Proxy" is activated in "Cisco Unity Connection Serviceability -> Service Management".

  • Remote Administrator role is associated to your user.

1. https://www.ibm.com/products/informix/developer-tools?mhsrc=ibmsearch_a&mhq=informix

Eventually found the file I needed for x86_64 Linux (ibm.csdk.4.50.FC5.LNX.tar)

2. Uploaded tar to server using SFTP.

3. Elevated to root.

sudo -i -u root

4. unzip the tar file from step 1 and 2.

tar xvf ibm.csdk.4.50.FC5.LNX.tar

5. Ran installer (selected default options for all).

./installclientsdk

6. Created static environment paths file.

vi /etc/profile.d/informix.sh

  7. Added path information to the file.

#!/bin/bash
export LD_LIBRARY_PATH=/opt/IBM/Informix_Client-SDK/lib:/opt/IBM/Informix_Client-SDK/lib/esql:/opt/IBM/Informix_Client-SDK/lib/cli
export INFORMIXDIR=/opt/IBM/Informix_Client-SDK

 8. Created the sqlhosts file.

vi /opt/IBM/Informix_Client-SDK/etc/sqlhosts

 9. Entered in the hostname for the test (replace unityloab01 with the real name).

ciscounity onsoctcp unitylab01 20532

10. I ran dstaudt's script and it worked fine. I was also able to write my own script using the same and it worked fine.

 

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: