Topic Thread

Expand all | Collapse all

Using IfxSmartTrigger on utf8 database

  • 1.  Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 03:42 AM

    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?



    Thanks for your answers!



    ------------------------------
    Kind Regards
    Stefan
    ------------------------------


  • 2.  RE: Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 03:50 AM

    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

     






  • 3.  RE: Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 04:26 AM
    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
    ------------------------------



  • 4.  RE: Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 05:01 AM

    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
    ibm-champion-rgb-130px

    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

    image001.jpg@01CDC3E9.1425CBB0

    image002.jpg@01CDC3E9.1425CBB0

    image003.jpg@01CDC3E9.1425CBB0

     

     






  • 5.  RE: Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 07:57 AM

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



  • 6.  RE: Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 02:25 PM
    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
    ------------------------------



  • 7.  RE: Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 08:39 AM

    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

     






  • 8.  RE: Using IfxSmartTrigger on utf8 database

    Posted Wed April 22, 2020 08:51 PM
    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
    ------------------------------



  • 9.  RE: Using IfxSmartTrigger on utf8 database

    Posted Thu April 23, 2020 12:35 PM

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



  • 10.  RE: Using IfxSmartTrigger on utf8 database

    Posted Thu April 23, 2020 01:39 PM
    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
    ------------------------------



  • 11.  RE: Using IfxSmartTrigger on utf8 database

    Posted Fri April 24, 2020 02:03 AM

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