Using IfxSmartTrigger on utf8 database

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