Enabling Notifications For DB2

Create a table 'ncache_db_sync' having four fields (cache_key VARCHAR, cache_id VARCHAR, modified BIT and work_in_progress BIT). Script to create the table is as follows:

--Create table'ncache_db_sync'

CREATETABLE ncache_db_sync (

cache_key VARCHAR( 256 ),

cache_id VARCHAR( 256 ),

modified BITDEFAULT( 0 ),

work_in_progress BITDEFAULT( 0 ),

PRIMARYKEY( cache_key , cache_id ));

Create UPDATE and DELETE triggers, for every table on which notification is required. They set the 'modified' field of corresponding row in the ncache_db_sync table to 1. To carry out the task, see the following sample script that creates trigger on 'Products' table:

--create trigger

CREATETRIGGER myTrigger

ON dbo . Products

FORDELETE,UPDATE

AS

UPDATE ncache_db_sync

SET modified = 1

WHERE cache_key IN

(Cast((Select old . ProductID from DELETED old )asVarChar)+':dbo.Products');

Note: cache_key must be the same key that is used to add the corresponding record in the cache.

Now, As soon as an item is added to cache with dependency, a row will be created in table 'ncache_db_sync' for this cache key. We'll have to make sure that the format of cache key while adding into cache is exactly same as defined in the corresponding trigger. For our example, cache key for product id 10 should be like, "10:dbo.Products". Following code demonstrates how to add items with dependency.