Cache Option for ORACLE
<< Cache Option for POSTGRES - TOC - Cache option for SYBASE >>
This chapter shows a step-by-step method for caching Oracle database into CSQL and demonstrates automatic update propagation to Oracle database.
Setting up CSQL and Oracle
Pre-requisites
The “ UnixODBC ” package and “ Oracle “ have to be installed in the system
Oracle Database
Oracle environment script needs to be run before starting the server. This file will be present in $ORACLE_HOME/bin and name of the script is “oracle_env.sh”
Usually this library is named as libsqlora.so and will be present in lib directory of ORACLE installation directory.
UnixODBC
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
Below odbc.ini file contains the properties for DSN (Data Source Name), named oracle and should be appended these lines in odbc.ini file at home directory (~/.odbc.ini)
[oracle] Description = Oracle ODBC driver for Oracle 10g Driver = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1 DSN = oracle ServerName = localhost USER = lakshya PASSWORD = lakshya123
This guide assumes that the driver library is present in /usr/lib directory. If it is installed in some other directory change the DRIVER parameter value accordingly.
Check the connection using DSN name with isql tool.
$isql oracle It should display the following +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
If it does not show the above message then try with as follow
$isql oracle <user> <password> It should display the following +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
NOTE: The Script file can help to generate odbc.ini and odbcinst.ini which is present in $ORACLE_HOME/odbc/utl/odbc_update_ini.sh
Unidirectional
Configure csql.conf file
Some cache section variables must have to be modified in csql.conf file. Cache section variables should contain below specified value.
CACHE_TABLE=true SITE_ID=1 DSN = oracle 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 Oracle database. Although CSQL supports multiple cache tables, one Oracle table could be cached to keep the example simple. The step-by-step procedure discussed here for caching a table and to make some update and see caching in action.
Create a table in Oracle
Using the command isql in oracle, the SQL prompt could be achieved. Through this prompt the DDL and DML statements can be executed in Oracle database.
Connect to the database and create a table:
$isql oracle <oracle_user> <oracle_password> SQL> CREATE TABLE T1(f1 int, f2 char (196), primary key (f1));
Now insert some records and commit the changes:
SQL> INSERT INTO T1 VALUES (1, 'Hi'); SQL> INSERT INTO T1 VALUES (2, 'All'); SQL> COMMIT;
Load Oracle table into Cache
Before using the cachetable tool to cache the table from oracle, the data sources needs to be added into csqlds.conf file by using “csqlds” tool. Mainly the data source name from which the table belongs will be entered into the csqlds.conf file followed by DS’s user name, password and Target DB name. Refer the section csqlds tool and csqlds.conf file. Open another terminal and move to the CSQL root directory and set up the environment using the command (. ./setupenv.ksh) .
$csqlds -U lakshya -P lakshya123 -D oracle -N oracle -a
here 1st "oracle" is used as DSN and 2nd "oracle" is used as DB name After execution of this command, the entries for ‘oracle’ data source will be present in csqlds.conf file, which will be used by the cachetable tool to cache the table from this DS. Now, use the cachetable tool to load the Oracle table into Cache. Make sure that CSQL Server is running before the cachetable tool is used. Refer the Section 3.7. (Starting and Stopping csqlserver) in CSQL UserManual for starting and stopping the CSQL Server.
Open another terminal and move to the CSQL root directory and set up the environment using the command $. ./setupenv.ksh.
$ cachetable -U root -P manager -t T1
Now check the contents of the cache table, which is present in main memory database using CSQL tool.
CSQL>SELECT * FROM T1;
----------------------------------------------------
f1 f2
----------------------------------------------------
1 Hi
2 All
Refer the section cachetable for more information on cachetable tool.
CSQL - to - Oracle Updates
The unidirectional feature makes sure that if any modification operations (INSERT, UPDATE, DELETE) performed on the table in Cache will propagate to the original table at Oracle automatically.
Now update one record of cache table, it should propagate to the Oracle. The ‘T1’ table would have been updated in both the database.
Run csql tool with -g option, it creates an isql session which acts as gateway for CSQL, Oracle, MyQsl and propagates changes to the original tables at target database.
Follow the below instructions:
Create gateway connection
$csql -g CSQL>
Update first row in CSQL
CSQL>update T1 set f2='Hi Smith' where f1=1; Statement Executed: Rows Affected = 1
Checks the update in CSQL
CSQL>select * from T1;
-----------------------------
f1 f2
-----------------------------
1 Hi Smith
2 All
Checks the update in Oracle
SQL> select * from T1; +------+----------+ | f1 | f2 | +------+----------+ | 1 | Hi Smith | | 2 | All | +------+----------+
If the INSERT or DELETE operation is performed, the effect of that operation will be done at both cache and target database. For Example, if new record is inserted into the cache, it will be inserted in target database as well.
Unload the cache table
Cached tables can be unloaded (removed from the cache) using –u option of cachetable tool
Unload the Table using “cachetable -u” option
$cachetable -U root -P manager -t T1 -u
Note: CSQL restricts dropping the cache table using DROP statement. Application is expected to unload the table first and then drop it.
Bi-directional Cache
Bi-directional caching is implemented using triggers of the target database. This requires additional tables and triggers, which needs to be installed on the tables in target database.
Sample trigger code is available in the file trigger.osql under the CSQL root directory
Configuring csql.conf file
For bi-directional cache, some changes are required in cache section parameters of csql.conf file. Cache section variables should contain below specified value
CACHE_TABLE=true SITE_ID=1 DSN = oracle 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, which is set to true for bi-directional caching.
Setting up Oracle
After configuring csql.conf file, a log table needs to be created in target database to hold the operation 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 Oracle
$isql oracle <user name> <password> 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 csql_log_int table in Oracle
As mentioned before, for propagating direct updates from Oracle 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 oracle lakshya lakshya123 SQL> create table csql_log_int (tablename char (64), pkid int, operation int, cacheid int, id int not null); SQL> CREATE SEQUENCE csql_id MINVALUE 1 INCREMENT BY 1 START WITH 1;
Create trigger
Lets say for cached table ‘t1’ having primary key field ‘f1’, following is the format for creating the triggers in Oracle database. A sample file (trigger.osql) is available at the CSQL root directory; this could be modified with cached table name and its primary key field.
CREATE OR REPLACE TRIGGER triggert1
AFTER insert or update or delete on t1
FOR EACH ROW declare rid PLS_INTEGER;
begin
SELECT CSQL_ID.NEXTVAL INTO rid FROM dual;
if inserting then insert into csql_log_int values ('t1',:new.f1,1,1,rid);
end if;
if updating then insert into csql_log_int values ('t1',:old.f1,2,1,rid);
SELECT CSQL_ID.NEXTVAL INTO rid FROM dual; insert into csql_log_int values
('t1',:new.f1,1,1,rid); end if;
if deleting then insert into csql_log_int values ('t1',:old.f1,2,1,rid);
end if;
end;
Note: Trigger name ends with the table name. 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.osql file as per the need, this could be executed by running the below command.
$ isql oracle <trigger.osql
After executing this file the trigger would be created on the table.
Steps for bi-directional cache
After set up the configurations, cache the t1 table from Oracle and use bi-directional cache. That means any modification to original table, which is present in Oracle will automatically propagated to the cache table in CSQL.
Load Oracle table to Cache
Now run the csqlserver and use the cachetable tool to cache Oracle table into cache.
Create another terminal and move to csql root directory and set up the environment (. ./setupenv.ksh). Make the entries in csqlds.conf file for your appropriate Data Source. Refer the Section csqlds tool
$ cachetable -U root -P manager -t p1
Now check the contents of the cache table, which is present in Cache using CSQL tool.
CSQL>SELECT * FROM p1;
-------------------------
f1 f2
-------------------------
1 Hi
2 All
Refer the section cachetable for cachetable tool.
Oracle – to – CSQL Update propagation
Insert some records in p1 table in Oracle, and the record should be propagated to the cached table at CSQL automatically based on the time specified in CACHE_RECEIVER_WAIT_SECS variable. The update will appear in cache before CACHE_RECEIVER_WAIT_SECS.
After this query the cached table in CSQL and the original table in Oracle database will contain the same number of records.
Insert Records in Oracle
$isql oracle <user name> <password> SQL> insert into p1 values (3,'NoOne'); SQLRowCount returns 1 SQL> insert into p1 values (4,'EveryOne'); SQLRowCount returns 1
two more records are inserted.
Query the p1 table in Oracle
SQL> select * from p1; +----+----------+ | f1 | f2 | +----+----------+ | 4 | EveryOne | | 3 | NoOne | | 2 | All | | 1 | Hi | +----+----------+
Query the p1 table in CSQL with -g option
CSQL>select * from p1;
----------------------------------
f1 f2
----------------------------------
1 Hi
2 All
3 NoOne
4 EveryOne
The records will be found in cache, those are inserted into the original table at Oracle
Bi-directional cache on non-integer primary key
Let us consider a table having non-integer (say char) primary key field to be cached for the purpose of bi-directional. For example:
$isql oracle <user name> <password> SQL>create table t1 (f1 int, f2 char (10), f3 float, primary key(f2));
For bi-directional cache on table ‘t1’ having non-integer primary key field f2, we need to add a extra key field “f4” which is not-null and unique Auto_Increment field.
$alter table t1 add column f4 int not null unique auto_increment;
After adding an extra key field, make necessary changes on trigger file for the field ‘ f4’ as key field. Run the trigger and do the operation as per the requirement.
Note: For bi-directional cache on a table having non-integer primary key, add a NOT NULL UNIQUE indexed field in target table and run trigger assuming this new field as primary key. For oracle cache this type of table by -F option.
<< Cache Option for POSTGRES - TOC - Cache option for SYBASE >>