Introducing HCL Informix Smart Triggers

7/17/2017

Updated: 8/30/18

Databases are historically a great place to store data and retrieve data. However, they are not always the easiest to process events and receive alerts from, especially in real time or near real time. You can use a specialized software for this, but what if you wanted the best of both worlds? An enterprise level database for storage and security, but with the ability to process events and alerts as they happen.

What are Smart Triggers?
It’s a new feature introduced as part of the JDBC driver in 4.10.JC9 and in the corresponding server 12.10.xC9. Smart Triggers are special ‘push’ notifications from the HCL Informix server to your Java application. This push notification is backed by a selective trigger that you create from your client application (it’s not a normal database table trigger) that will push an event to the client when your trigger criteria happens. No more polling for events when you want to know if something has changed.

Let’s take an example. Say you have an accounts table that stores how much money a user has. You would like to have your application be alerted, triggered when a balance goes below amount. For our application to know this, we would have to poll for that, or write a custom routine in the server that can somehow signal our application that this happens.

With this new feature the application itself can now register a Smart Trigger to be alerted when the balance goes below the target amount. What makes Smart Triggers nice is that they require no schema changes, no custom code for the server (user defined routines), and can be created and destroyed purely from an application. Since Smart Triggers use the push notification back-end feature of the server, all the processing for our triggers is done on the server and the application itself remains thin and lightweight. You can register many triggers and keep your application footprint small.

The triggers themselves are based on simple select SQL statements. A simple select statement in this case is a query on exactly 1 table, with basic projection and predicate clauses.
Comparing Smart Triggers with a regular table trigger

Smart Triggers in Action
For this let’s take our banking example with user accounts. We have a very simple schema.

Now we can write a few lines of Java code to create our Smart Trigger.

Notice that other than passing in the URL for our database, we can go straight to adding a Smart Trigger. There are several optional settings for Smart Triggers, but you can get started without them. Details on all the options are covered in the Javadoc that ships with the JDBC driver. With the addTrigger(…) method we can choose the table/owner/database and specify the query we want to run. This query is our filter. We will only get notified on changed rows (insert/update/delete) that match this condition. The final part of a Smart Trigger definition is a callback you provide. With that callback (a simple implementation of the IfmxSmartTrigger interface) you will get a string object back from the Smart trigger with information about the row(s) and transactions involved that met the criteria of the trigger.

When a trigger is executed, the notify method will get called, and you can process the data to take whatever action you want. The String JSON you get back looks like this:

Different operations (update vs insert) and whether it was part of a transaction will alter the JSON structure slightly when returned.

What's Next?
In future versions, we look to incorporate feedback we got from our first implementation. We look to include detachable triggers so a client can disconnect and reconnect without losing any records. We are also looking to improve the auditing capability by adding the user ID who initiated the change to the data. And as always, we welcome additional features/comments/suggestions.

Conclusion
We hope that Smart Triggers can provide new and interesting ways to interact with your data, from monitoring to selective data streaming, you now have a real-time accessor to your data.

Brian Hughes
Software Engineer
HCL Informix
Connect with me on LinkedIn

Informix is a trademark of IBM Corporation in at least one jurisdiction and is used under license.

22 Comments

Art Kagel link

7/19/2017 05:19:32 am

Are these triggers available for applications written in other host languages, ex: C, C++, PHP, Perl, etc.?

Brian Hughes

7/25/2017 07:30:06 am

Hi Art,

Currently the Smart Trigger API is only in JDBC. Nagaraju posted a blog on using some raw API's to do the push notifications which can apply to some of our other drivers. Smart Trigger is a handy API over the server push notification feature. We are looking for feedback on which languages/drivers users would like to see Smart Triggers API added to in the future. We are looking at which drivers users would most want to see add this to prioritize which ones we work on next. Hope this helps!

Carlos Murphy

8/9/2017 12:53:19 pm

Hi Art, you can use trigger introspection feature to audit database changes generically. That allows you to generate the data/logs in any format: JSON, CSV, XML, etc.

See: https://www.ibm.com/developerworks/data/library/techarticle/dm-0410roy/

Dirk Steinkamp

8/29/2017 01:17:09 am

Hi Brian,

that looks like a great functionality!

We have some Delphi-applications running, that would benefit from a high-Level-implementation of the smart triggers.

I haven't looked into the details of the driver regarding the smart triggers yet, but do you think the low-level-functionality would work for Delphi right now out of the box? We have especially one application running, that currently is using heavy polling, and could benefit greatly.

Thank you!

Dirk

Bernhard Walter

9/13/2017 05:47:01 am

Hi Brian

thanks a lot for your example. This sounds really fantastic.
But only JDBC?
Hope other languages (C++, PHP) coming soon. Our applications are using no JDBC we use ODBC ...

Thanks a lot

Best regards

12/26/2017 10:33:40 am

Hi Bernhard,

We are taking feedback from customers on which languages we should target next. No guarantees or timelines yet, but ODBC seems like a popular second choice for us to look at.

Xavier Escoté link

12/25/2017 08:49:36 am

Hi Brian,

Very good!
Put in production.

A suggestion.

Once you start watch on a table or set of tables, posterior additions or removes will block if there are no transactions
on watching tables.

In a complex environment, I need to add or remove watching several tables frequently. As I suppose you can not "interrupt" blob reading, I suggest you could have a dummy table to trigger operations.

In any case, that's what I've implemented and it let's me add and remove watching tables without waiting for operations in pre-existing list.

Thanks in advance,
Xavier

12/26/2017 11:13:27 am

Hi Xavier, we are excited to hear this being used in production! We had noticed the potential issue of being blocked on the read if you needed to add/remove triggers. The ability to set a timeout which sends a trigger regardless if a transaction was made is an option we made available. Another possible option is to use many smart trigger instances and close/open them as you need them.

The use of a dummy table is an interesting solution we hadn't thought of. Glad it's working for you. We appreciate the feedback!

Hamza Mayet

4/18/2018 09:19:58 am

Hi Brian,

i am not getting proper json string in notify(String json) callback.
issue is with the Dat and Datetime columns

i am getting value of column like this {"action_dt":2003-02-11} instead it should be like this {"action_dt":"2003-02-11"}.
Any idea why? ..

Thanks in advance,
Hamza

4/18/2018 10:45:44 am

Hi Hamza,

I passed this along to the server team and looks like an issue with the server generating the JSON document that is sent to the client (JDBC driver) when a date is sent back.

We should be able to get that fixed on the server side, unfortunately without a patch for the server, the client will need to try to work around the issue. I'm guessing JSON parsers will throw an error trying to read this as it is.

4/19/2018 12:54:50 am

Hi Brian,

Thanks for your immediate response , Yes JSON parsers are throwing error. I will handle this with regex for now.

Miroslav Krňoul

11/21/2018 06:48:12 am

Hello, there is active Enterpise Replication necessary?

11/28/2018 06:19:07 am

Hi Miroslav,

Yes, smart triggers uses the replicates and log reading abilities from the Enterprise Replication feature. However you don't need to work with it directly. If you already have ER running, then smart triggers uses that. If you do not have ER running, then as long as you have an sbspace or a storage pool to create such a space, creating your first smart trigger will automatically start up the parts of ER needed behind the scenes.

Jayaram

7/27/2019 07:21:05 am

Hi Brian, I see that when ER is stopped / broken down, the smart trigger no longer gets any push data, neither the timeout message comes. The JDBC Connection too is intact, there is no failure indication.
How to handle this scenario? Am i missing something while registering the trigger ?

Brian hughes

7/29/2019 02:22:15 pm

Jayaram -- In some cases when ER goes down the server might send an ifx_error JSON message to the smart trigger clients. Unfortunately the ifx_error message types are not propagated to the listeners you have attached to the smart trigger.

There are two workarounds 1) Subclass IfxSmartTrigger and correct the behavior in the protected method processWatchData() 2) Call the public API readTriggerEvent and process the events in your own code (bypassing the listeners you would attach to the smart trigger.

We have fixes for the propagation problem going into the latest drivers. I cannot say for certain if ER will ALWAYS send such a message if ER breaks down vs is shutdown by the user.

hope this helps.

Peter Budai

4/3/2019 05:00:23 am

Do I understand it correctly that smart trigers are active only when any jdbc client registers and then listens to them just on time when they occurs?
In other words: are monitored data changes lost if monitoring app is not running?

4/3/2019 07:20:19 am

In the original version (and this post) that was correct. However in 12.10.XC10 and the corresponding 4.10.JC10 JDBC driver, you can set the trigger as "detachable". When you do that, the application can disconnect and reconnect and the server will buffer the messages. You can set how much space the server reserves for each smart trigger with the API calls. See the javadocs at https://static.javadoc.io/com.ibm.informix/jdbc/4.50.1/com/informix/smartTrigger/IfmxThreadedSmartTrigger.html

qas

8/14/2019 11:22:30 pm

After reataching few times with same session Id, I see same message sent few times, only op_num is inreasing. What I do wrong?

Manish

9/11/2019 04:58:12 am

Hi Brian,

How do we use IfxSmartTrigger to retrieve data from the point connection is lost? what we are trying to achieve is retrieving old data from the point the smart trigger got disconnected.

try (IfxSmartTrigger trigger = new IfxSmartTrigger(connectionURL);) {
trigger.timeout(TIMEOUT_IN_SECONDS).label("ALERT");
//Add triggers 10 of them

trigger.transactionID(4604210930016l);
trigger.commitTime(1568200905);
//trigger.sessionID("35");
trigger.watch();

}

Any permutations and combination of the above is not working for me. How to handle this scenario? Am i missing something while registering the trigger ? A working example would be wonderful..

9/11/2019 06:21:26 am

This is the informix version we are using -> IBM Informix Dynamic Server Version 12.10.UC9W1

9/11/2019 04:59:19 am

9/11/2019 06:23:10 am

This is the informix version we are using -> IBM Informix Dynamic Server Version 12.10.UC9W1 .

Introducing HCL Informix Smart Triggers

Leave a Reply.

Archives

Categories