Cache option for SYBASE
<< Cache Option for ORACLE - TOC - Cache option for DB2 >>
This chapter shows a step-by-step method for caching Sybase database into CSQL and demonstrates automatic update propagation to Sybase database.
Setting up CSQL and SYBASE
Prerequisites
For CSQL Cache to work, Sybase server, Sybase ODBC Connector, and UnixODBC packages need to be installed. . Follow below links to download the packages
- SYBASE Database
http://www.sybase.com/linux/ase
- SYABSE ODBC Connector
It comes with the Sybase build package
- UnixODBC
(http://rpm.pbone.net/index.php3/stat/3/srodzaj/1/search/unixODBC)
Sybase Database
After installed Sybase, starts the server as per the instructions provided in the Sybase user manual.
Sybase ODBC Connector
The driver library of Sybase ODBC connector is usually named as libsybdrvodb.so and will be present under $SYBASE/DataAccess/ODBC/lib directory.
UnixODBC Connector
The driver library of unixODBC is named as libodbc.so and 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 sybase along with odbc driver path. The ~/.odbc.ini file which is present in your home directory should contain the below lines.
[ODBC Data Sources] [sybase] Driver = /opt/sybase/DataAccess/ODBC/lib/libsybdrvodb.so Description = Sybase ODBC Data Source SERVER = <sybase> PORT = <port> USER = sa Password = Database = tempdb UseCursor = 1
The above variables value depends on your installation of Sybase database. The variables such as Driver, SERVER, PORT, USER, Password, etc., would be filled up by the actual value with respect to the procedure followed for Sybase installation.
Check the connection using DSN name with isql tool.
$isql sybase It should display the following +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
NOTE: The readme.txt file can help to generate odbc.ini and odbcinst.ini which is present in $SYBASE/DataAccess/ODBC/samples
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.
Configure the Cache (csql.conf)
Some cache section variables in csql.conf file have to be modified in order to cache the tables from Sybase 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 = sybase 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 sybase 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 sybase will be placed instead of MySQL when these 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.sybase 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 = sybase 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 Sybase
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 Sybase to cache
The below DDL statement is for creating a t1 table where f1 is a primary integer key.
$isql sybase SQL>CREATE TABLE t1 (f1 int, f2 char (196), primary key (f1));
Now insert some records and commit the changes:
SQL> INSERT INTO p1 VALUES (1, Hi); SQL> INSERT INTO p1 VALUES (2, All); SQL> COMMIT;
Create log table (csql_log_int) in Sybase
As mentioned before, for propagating direct updates from Sybase 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 sybase SQL> create table csql_log_int ( tablename char (64), pkid int, operation int, cacheid int, id int identity primary key );
Create trigger in Sybase
Lets say for cached table t1 having primary key field f1, follows are the format for creating the trigger in sybase database. A sample file (trigger.sybase) 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.
create trigger t1_insert on t1 for insert as begin Insert into csql_log_int (tablename, pkid, operation, cacheid) select t1, inserted.f1, 1,1 from inserted end create trigger t1_delete on t1 for delete as begin Insert into csql_log_int (tablename, pkid, operation, cacheid) select t1, deleted.f1, 2,1 from deleted end create trigger t1_update on t1 for update as begin Insert into csql_log_int (tablename, pkid, operation, cacheid) select t1, deleted.f1, 2,1 from deleted Insert into csql_log_int (tablename, pkid, operation, cacheid) select t1, inserted.f1, 1,1 from inserted end end;
NOTE: If your table name and field name is different then replace t1 in the above script to the cached table name and f1 to the primary key fieldname of the cached table.
After editing the trigger.sybase file as per the need, this could be executed by running the below command.
$ isql sybase <trigger.sybase
After executing this file, the trigger would be executed on the table. If above fails, create trigger manually in sybase.
Steps for Bi-directional Cache
After setting up all above configurations, the t1 table will be cached from Sybase and any modification (in terms of DML statements) happened to t1 table which is present in Sybase 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 Sybase DSN and tools should be used instead of MySQL.
Bi-directional cache on non-integer primary key
Let us consider a table t1 having a non-integer (say char) primary key field to be cached for the purpose of bi-directional. In such a situation, a new integer field (lets say f4) needs to be added to the table t1 which will be a NOT NULL and UNIQUE key with IDENTITY flag. Follow below SQL statement to add f4 field.
$isql sybase SQL>create table t1 ( f1 int, f2 char (10), f3 float, primary key (f2) ); $alter table t1 add column f4 int not null unique identity;
The trigger needs to be modified for f4 field and will be used to create the trigger for all DML operations.
<< Cache Option for ORACLE - TOC - Cache option for DB2 >>