cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
727
Views
5
Helpful
3
Replies
Highlighted

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
Highlighted
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

3 REPLIES 3
Highlighted
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

Highlighted

Thank you!

5 Stars!

 

Juan

Highlighted

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?

 

Content for Community-Ad