Cache option for IBM DB2 database
<< Cache option for SYBASE - TOC - Multi Node Distributed Caching >>
IBM DB2 database is spread all over the areas, including Telecommunication, Banking, Stock Market, Health Care and etc., because of its reliability, availability, and scalability. All these industries needs a real-time data which seems inevitably for customers which proportionally benefits the Organization in terms of raising revenue and cutting cost as CSQL MMDB extends a faster access of data than DB2.
CSQL’s Cache technology provides the connective to DB2 database and allows application to transfer of data between the CSQL and DB2 in both ways. (Unidirectional and Bi-directional). Cache technology is facilitated in such a way that the complex queries like sub queries will be getting access to DB2 and all other queries like single point look up for integers or characters which happens nearly 90% for DB2 database in a real-time scenario will get access to CSQL to capture and process of high-volume data flows.
This chapter shows a step-by-step method for caching a single table or group of related DB2 tables into CSQL and demonstrates automatic update propagation of data in CSQL in both ways.
Setting up CSQL and DB2
Prerequisites
For CSQL Cache to work; DB2 server, DB2 ODBC Connector, and UnixODBC packages need to be installed. Follow below links to download the packages
- DB2 Database (db2exc_952_LNX_x86.tar.gz)
- DB2 ODBC Connector
It comes with the DB2 build package
- UnixODBC
(http://rpm.pbone.net/index.php3/stat/3/srodzaj/1/search/unixODBC)
DB2 Database
After installed DB2, starts the server as per the instructions provided in the Sybase user manual.
DB2 ODBC Connector
The driver library of DB2 ODBC connector is usually named as libdb2.so would be present under this path /opt/ibm/db2/V9.5_01/lib32/libdb2.so.If the download package is different from above provided then the path may different for ODBC connector.
UnixODBC Connector
The driver library of unixODBC is named as libodbc.so will be available at /usr/lib or /usr/local/lib directory.
Configure- odbc.ini file
The odbc.ini file is a data-source configuration information file. This file contains list of data sources and properties for each database those are present in system. Below odbc.ini file contains the properties for DSN (Data Source Name) named DB2 along with odbc driver path. The ~/.odbc.ini file which is present in your home directory should contain the below lines.
[ODBC Data Sources] [db2] Driver = /opt/ibm/db2/V9.5_01/lib32/libdb2.so Description = DB2 ODBC Data Source user = <User_Name> Password = <Password> Fileusage = 1 Dontdlclose = 1
The above variables value depends on your installation of DB2 database. The variables such as user and, Password, would be filled up by the actual value with respect to the procedure followed for DB2 installation.
Check the connection using DSN name with isql tool.
$isql db2 <User_Name> <Password> It should display the following +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
Unidirectional Cache
The default caching in CSQL is one-way (or unidirectional cache), which means all updates (Insert, Update, Delete statement execution) on cached tables will be automatically propagated to target database(db2).
Configure the Cache (csql.conf)
Some cache section variables in csql.conf file have to be modified in order to cache the tables from DB2 database. Refer 4.1.1. Cache section variables.
Cache section variables should contain below specified value for unidirectional cache.
CACHE_TABLE=true SITE_ID=1 DSN = db2 CACHE_MODE=SYNC ENABLE_BIDIRECTIONAL_CACHE=false CACHE_RECEIVER_WAIT_SECS=10 TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf DS_CONFIG_FILE=/tmp/csql/csqlds.conf
Steps for Unidirectional Cache
This section describes how to create a unidirectional cache that caches the contents of a single table from DB2 database.
Follow the section 6.2.2.1.for discussion on “Cache MySQL data”. The step-by-step procedure has been discussed for caching a table and to make some update and see caching in action. The difference is only that DB2 database will be placed instead of MySQL database when theoe sections are used.
Bi-directional Cache
CSQL also supports bi-directional caching in which direct updates on target database are also propagated to CSQL Cache automatically.
Bi-directional caching is implemented using triggers of the target database. This requires additional log table and triggers, which needs to be installed on the tables in target database.
NOTE: Sample trigger code is available in the file trigger.db2 under the CSQL root directory.
Configure Cache (csql.conf)
For bi-directional cache, some modification needs to be made in cache section in csql.conf file. Refer 4.1.1. Cache Section Variables.
CACHE_TABLE=true SITE_ID=1 DSN = db2 CACHE_MODE=SYNC ENABLE_BIDIRECTIONAL_CACHE=true CACHE_RECEIVER_WAIT_SECS=10 TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf DS_CONFIG_FILE=/tmp/csql/csqlds.conf
The only difference between unidirectional and bi-directional configuration is the value of ENABLE_BIDIRECTIONAL_CACHE parameter is set to true to enable bi-directional caching.
Setting up DB2
After configuring csql.conf file, a log table needs to be created in target database to hold the log records for all the cached tables. The triggers need to be installed on cached table for INSERT, UPDATE and DELETE operation.
Create a table in DB2 to cache
The below DDL statement is for creating a ‘t1’ table where ‘f1’ is a primary integer key.
$isql db2 <User_Name> <Password> SQL>CREATE TABLE t1 ( f1 int not null primary key, f2 char (196), );
Now insert some records and commit the changes:
SQL> INSERT INTO t1 VALUES (1, ‘Hi’); SQL> INSERT INTO t1 VALUES (2, ‘All’); SQL> COMMIT;
Create log table (csql_log_int) in DB2
As mentioned before, for propagating direct updates from DB2 to cache, a log table should have to be created to hold operation logs of all cached tables.
Use the below statements for creating log table:
$isql db2 <User_Name> <Password>
create table csql_log_int(
tablename char(64),
pkid int,
operation int,
cacheid int,
id int not null GENERATED as IDENTITY
);
Create trigger in DB2
Lets say for cached table ‘t1’ having primary key field ‘f1’, follows are the format for creating the trigger in DB2 database. A sample file (trigger.db2) is available sample directory, which is present under CSQL root directory; this could be modified with cached table name and its primary key field accordingly.
drop trigger triggert1insert
CREATE TRIGGER triggert1insert AFTER INSERT ON t1 REFERENCING NEW AS N FOR EACH ROW BEGIN
ATOMIC Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('t1', N.f1, 1,1); End
drop trigger triggert1update
CREATE TRIGGER triggert1update AFTER UPDATE ON t1 REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN ATOMIC Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('t1', O.f1, 2,1);
Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('t1', N.f1, 1,1); End
drop trigger triggert1delete
CREATE TRIGGER triggert1delete AFTER DELETE ON t1 REFERENCING OLD AS O FOR EACH ROW BEGIN
ATOMIC Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('t1', O.f1, 2,1); End
NOTE: If your table name and field name is different then replace ‘t1’ in the above script and ‘f1’ to the primary key fieldname of the cached table.
After editing the trigger.db2 file as per the need, this could be executed by running the below command.
$ isql db2 <User_Name> <Password> <trigger.db2
After executing this file, the trigger would be executed on the table. If above fails, create trigger manually in DB2.
Steps for Bi-directional Cache
After setting up all above configurations, the ‘t1’ table will be cached from DB2 database and any modification (in terms of DML statements) happened to ‘t1’ table which is present in DB2 database will be automatically propagating to the CSQL in a periodically manner, default is 10 seconds.
Follow the section 6.3.6 to see bi-directional caching in action. Note the DB2 database DSN and tools should be used instead of MySQL database.
<< Cache option for SYBASE - TOC - Multi Node Distributed Caching >>