01-20-2020 02:24 PM
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.
Solved! Go to Solution.
01-21-2020 01:00 PM - edited 01-23-2020 08:23 AM
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
01-21-2020 01:00 PM - edited 01-23-2020 08:23 AM
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
01-23-2020 07:42 AM
Thank you!
5 Stars!
Juan
08-28-2020 06:39 PM
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?
05-11-2021 02:17 PM
I was able to get this to work on RHEL 7.
I'll put my steps if it helps anyone.
Prerequisites:
"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.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide