Using IfxSmartTrigger on utf8 database
Answered: green (solid confidence) — Eric Vercelletto confirmed SmartTrigger runs through sysadmin and spotted the locale mismatch; setting DB_LOCALE in the JDBC URL fixed it, as shown in SangGyu Jeong's worked example. The asker confirmed it works, though special characters still arrive wrongly encoded.
Advisory only.
Posted in 2020
Stefan Wilfling got error -23197 (locale mismatch) using IfxSmartTrigger on 14.10.FC3 with utf8 databases, since sysadmin uses en_US.819. Nagaraju Inturi and SangGyu Jeong said to set DB_LOCALE (and CLIENT_LOCALE) in the connection URL. That fixed it; Stefan found only DB_LOCALE was needed, but special characters still came back wrongly encoded.
Topics: Error Codes & Troubleshooting, Connectivity: ODBC / JDBC / .NET, Triggers, Constraints & Referential Integrity, Java & JDBC Development, Internationalization & Character Sets
Hi! I currently test the Smarttrigger-Feature. Tested at DB-VERSION=14.10.FC3, JDBC-VERSION=4.50.JC3. I use the following code: IfxSmartTrigger trigger = new IfxSmartTrigger( triggerURL , properties); trigger.addTrigger("tablexy", "informix", database, "SELECT id FROM tablexy", new SmartTriggerNotify()); trigger.watch(); When i use the testdatabase as "triggerURL", I get: java.sql.SQLException: Routine (task) can not be resolved. When I use the sysadmin-Database as "triggerURL", I get: SQLException: Error executing task pushdata register: Cannot open database testdb, error code -1 sql error -23197 isam error 0 ErrorCode -23197: -23197 Database locale information mismatch. So the first question is: What is the correct database for the SmartTrigger-Initialization? sysadmin or the real database? JavaDoc is no information ( IfxSmartTrigger - jdbc 4.10.12 javadoc) Here it´s (more or less) the sysadmin-DB: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_st_01.htm At this example it´s not the sysadmin-Database (and not the triggered database): Informix Data Streaming Overview Thanks for your answers! ------------------------------ Kind Regards Stefan ------------------------------ #Informix
Hi Stephan
Task is a function in sysadmin, so I would say the smart trigger is running from sysadmin. And effectively, you specify the DB name in the smart trigger declaration
You have a DB Locale issue which is preventing the trigger to fire, I think
Or a difference of DB locale between sysadmin and your database
Check by running in dbaccess
Select * from sysdbslocale
And compare the values
Hi! Yes we´ve different Locales: sysadmin: Default-Locale (en_US.819) Our databases: Always utf8 (en_US.57372) So that means, that SmartTrigger doesn´t work at different locale? ------------------------------ Kind Regards Stefan ------------------------------
That might be the issue, supposedly someone from IBM or HCL will look at this post If this is a test, you can rebuild your instance from scratch with UTF8, but if not, this is more complex to resolve. Let us see what they say Have a great day and stay safe Eric Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting Board of Directors, International Informix Users group IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org
Hi! When this really doesn´t work with other Locales, I can stop my test. All our databases have utf8-Encoding. For me this is generally a strange think, that I have to connect to the sysadmin-database. When it´s a task, I understand it, but I don´t like it, because all users (where I want to use the smarttriggers) need access to the sysadmin-database. Hopefully someone has a solution. ------------------------------ Kind Regards Stefan ------------------------------
I don't think you can change the locales for sys*, my ER systems are all utf8 across the board, (SERVER, DB, CLIENT) yet all the sys* are enUS Cheers Paul
Please set DB_LOCALE to en_US.utf8 in smart trigger client environment. DB_LOCALE should match with user database codeset. System databases are codeset neutral. Thanks & Regards, Nagaraju ------------------------------ Nagaraju Inturi ------------------------------
Hi Stefan,
Just specify the DB_LOCALE and CLIENT_LOCALE environment variables in the url.
I used Brian's code for testing. Please refer to the post below.
http://www.hcl-informix-user.com/blogs/introducing-hcl-informix-smart-triggers
Below is what I tested.
1. Check database locale
[informix@db2 skjeong]$ echo "select * from sysdbslocale where dbs_dbsname in ('sysadmin','bank')" | dbaccess sysmaster
Database selected.
dbs_dbsname sysadmin
dbs_collate en_US.819
dbs_dbsname bank
dbs_collate en_US.57372
2 row(s) retrieved.
2. Running java program without locale environment variables
[informix@db2 skjeong]$ head -10 SmartTrigger.java
...
try(IfxSmartTrigger trigger = new IfxSmartTrigger("jdbc:informix-sqli://xxx.xx.xx.xx:53331/sysadmin:user=informix;password=password");) {
[informix@db2 skjeong]$ export CLASSPATH=/work1/informix/1210FC10/jdbc/lib/ifxjdbc.jar:/work1/informix/ids1410fc3/skjeong/gson-2.8.2.jar:.
[informix@db2 skjeong]$ javac SmartTrigger.java
[informix@db2 skjeong]$ java SmartTrigger
Exception in thread "main" java.sql.SQLException: Error executing table registration
at com.informix.smartTrigger.IfxSmartTrigger.watch(IfxSmartTrigger.java:539)
at SmartTrigger.main(SmartTrigger.java:13)
Caused by: java.sql.SQLException: Error executing task pushdata register: Cannot open database bank, error code -1 sql error -23197 isam error 0
at com.informix.smartTrigger.IfxSmartTrigger.registerTriggers(IfxSmartTrigger.java:482)
at com.informix.smartTrigger.IfxSmartTrigger.watch(IfxSmartTrigger.java:536)
... 1 more
3. Running java program after setting locale environment variable
[informix@db2 skjeong]$ head -10 SmartTrigger.java
...
try(IfxSmartTrigger trigger = new IfxSmartTrigger("jdbc:informix-sqli://xxx.xx.xx.xx:53331/sysadmin:user=informix;password=password;DB_LOCALE=en_us.utf8;CLIENT_LOCALE=en_us.utf8");) {
[informix@db2 skjeong]$ /work1/informix/ids1410fc3/jvm/jre/bin/java SmartTrigger
Bank Account Ping!
-- No balance issues
Bank Account Ping!
-- No balance issues
Bank Account Ping!
-- Bank Account Alert detected!
{"operation":"delete","table":"account","owner":"informix","database":"bank","label":"bank_alert_1","txnid":1421638541952,"operation_owner_id":1001,"operation_session_id":544,"commit_time":1587602769,"op_num":1,"restart_logid":331,"restart_logpos":4211028,"rowdata":{"id":22,"name":"John Doe","balance":-23.4499999999999990}}
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Hi! Many Thanks. Now it works. There´s only one problem with the encoding: I get the wrong value for a String for special characters: I´ve updated the value to "üOö" and the Result at the String is "üO". I think this is double-encoded. But this is not so important for me, because I only need the changed id´s. ------------------------------ Kind Regards Stefan ------------------------------
Hi Stefan, Can you please confirm if you also updated CLIENT_LOCALE to en_us.UTF8 in smart trigger client environment ? Otherwise client may try utf8 to 819 codeset conversion. Thanks & Regards, Nagaraju ------------------------------ Nagaraju Inturi ------------------------------
Hi! Now I tested with/without DB_LOCALE/CLIENT_LOCALE in the URL: Without DB_LOCALE the connect doesn´t work With/Without CLIENT_LOCALE I get the same result: Wrong encoding -> So I don´t need the CLIENT_LOCALE in the url. For my test that means: I only need the DB_LOCALE in the sysmaster-URL. Don´t use SmartTriggers for Strings ------------------------------ Kind Regards Stefan ------------------------------