Alachisoft NCache 4.1 - Online Documentation

Enabling Notifications For DB2

 
  For enabling notifications for DB2 follow these steps:
 
  1. 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'
    CREATE TABLE ncache_db_sync (
    cache_key VARCHAR ( 256 ),
    cache_id VARCHAR ( 256 ),
    modified BIT DEFAULT ( 0 ),
    work_in_progress BIT DEFAULT ( 0 ),
    PRIMARY KEY ( cache_key , cache_id ) );
     
  2. 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
    CREATE TRIGGER myTrigger
    ON dbo . Products
    FOR DELETE , UPDATE
    AS
    UPDATE ncache_db_sync
    SET modified = 1
    WHERE cache_key IN
    ( Cast (( Select old . ProductID from DELETED old ) as VarChar ) + ':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.
     
  3. Create a new Clustered Cache or a Local Cache in NCache Manager with the name "sqlDependencyCache".
  4. Start the cache "sqlDependencyCache".
  5. Create a Java project in any Java environment.
  6. Add reference of NCClient from installationFolder../NCache/java/lib/
  7. Add the following import statement in your project:
     
    import com.alachisoft.ncache.web.caching.*;
    import com.alachisoft.ncache.runtime.dependencies.*;
     
  8. Declare a variable "_cache" in the code and initialize it with "sqlDependencyCache", and clear it if this cache was used previously.
     
    Cache _cache = NCache.initializeCache("sqlDependencyCache");
    _cache.clear();
     
  9. Create a String "connectionString" containing information to connect with SQL database e.g.
     
    String connectionString = "Provider=SQLOLEDB; Data Source=Computer_Name//SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;";
     
  10. Create a dependency and add 10 items to the cache
     
    for (int i = 0; i < 10; i++)
    {
    //Cache key for product id 10 will be "10:dbo.Products"
    CacheDependency dependency = DBDependencyFactory.CreateOleDbCacheDependency(connectionString, Integer.toString(i) + ":dbo.Products");
    _cache.insert(i + ":dbo.Products", i, dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default);
    }
     
  11. Now update the key "6" of the Database table using the following SQL command:--
    [SQL]
    UPDATE Products SET ProductName = 'Modified Product' WHERE ProductID =6
     
  12. This invalidates cacheKey cache_key 6:dbo.Products and is removed by NCache in ncache_db_sync table.
 
 
See Also
 
Copyright © 2005-2012 Alachisoft. All rights reserved.