Can DB2 tell a web-app when a table data is updated?

Question

I have a table of non trivial size on a DB2 database that is updated X times a day per user input in another application. This table is also read by my web-app to display some info to another set of users. I have a large number of users on my web app and they need to do lots of fuzzy string lookups with data that is up-to-the-minute accurate. So, I need a server side cache to do my fuzzy logic on and to keep the DB from getting hammered.

So, what's the best option? I would hate to pull the entire table every minute when the data changes so rarely. I could setup a trigger to update a timestamp of a smaller table and poll that to see if I need refresh my cache, but that seems hacky to.

Ideally I would like to have DB2 tell my web-app when something changes, or at least provide a very lightweight mechanism to detect data level changes.

Answer 1

I think if your web application is running in WebSphere, setting up MQ would be a pretty good solution.

You could write triggers that use the MQ Series routines to add things to a queue, and your web app could subscribe to the queue and listen for updates.

If your web app is not in WebSphere then you could still look at this option but it might be more difficult.

Answer 2

Hi Andrew

A simple solution could be to have a timestamp (somewhere) for the latest change on to table.

The timestamp could be located in a small table/view that is updated by either the application that updates the big table or by an update-trigger on the big table.
The update-triggers only task would be to update the "help"-timestamp with currenttimestamp.

Then the webapp only checks this timestamp.
If the timestamp is newer then what the webapp has then the data is reread from the big table.

A "low-tech"-solution thats fairly non intrusive to the exsisting system.

Hope this solution fits your setup.

Regards
     Sigersted

Answer 3

Having the database push a message to your webapp is certainly doable via a variety of mechanisms (like mqseries, etc). Similar and easier is to write a java stored procedure that gets kicked off by the trigger and hands the data to your cache-maintenance interface. But both of these solutions involve a lot of versioning dependencies, etc that could be a real PITA.

Another option might be to reconsider the entire approach. Is it possible that instead of maintaining a cache on your app's side you could perform your text searching on the original table?

But my suggestion is to do as you (and the other poster) mention - and just update a timestamp in a single-row table purposed to do this, then have your web-app poll that table. Similarly you could just push the changed rows to this small table - and have your cache-maintenance program pull from this table. Either of these is very simple to implement - and should be very reliable.