Show Cached Table Information in CSQL

Cache Table Information
The -S option is used to get the cache table information. If it is used with any specific table name, which has been cached, then only that table information will be displayed.
$cachetable -U root -P manager -S
$cachetable -U root -P manager -t <TableName> -S

Example:

$ cachetable -t t1 -c “f1<=2”;

$ cachetable -S

“cache table” caches tables through interactive csql tool

cachetable tool worked at command prompt for all caching purposes. Programmatically, using SQL, JDBC and ODBC interfaces, cache statements can be executed to give flexibility to the application developer and also for administrator to cache the table and do any caching related operations.

Syntax: Cache a table

CACHE TABLE <Table Name>
[ CONDITION “<Record Condition >”
FIELDLIST “<Field Condition>”
PK <Field Name> DIRECT
DSN <Dsn Name> NO SCHEMA] ;

Syntax: Uncache a table

UNCACHE TABLE <Table Name>;

Examples: cache a table

The below statement will cache a table.

CSQL>cache table t1;

Example: Uncache a table

CSQL>uncache table t1;

Example: cache a table with condition The below command is record level caching.

CSQL>cache table t1 condition “f1=10”;

Example 4: cache table with selected fields.

CSQL>cache table t1 FIELDLIST “f1,f2” ;

csqltable.conf file plays an important role in Csql-Cache

Description about the csqltable.conf file

It holds 6 information
a) Mode
b) Table to be cached
c) Primary Key
d) Condition
e) Field List
f) DSN of Target DB

a) Mode
Different types of modes are there which depends upon various caching options.

1 : Simple caching : (cachetable -U root -P manager -t <table name>)
2 : Conditional Caching : (cachetable -U root -P manager -t <table name> -c “<condition>”)
4 : Field Level Caching : (cachetable -U root -P manager -t <table name> -f “<field list>”)
8 : Direct Caching : (cachetable -U root -P manager -t <table name> -D)
14 : Combination of 2,4,8 modes : (cachetable -U root -P manager -t <table name> -c “<condition>” -f “<field list>” -D)

b) Table to be cached
This is the table name from which is cached to csql from Target DB.

c) Primary Key
This indicates the primary key field name, if you choose bidirectional cache option.

d) Condition
This indicates the condition , bases on which you cahed the table.

e) Field List
This indicates the field list of the table, bases on which you cahed the table.

f) DSN of Target DB
DSN of the target DB

Use cachetable tool for cache the table from target database.
use “cachetable -?” to find help for cachetable tool

Sample:
$ cachetable -U root -P manager -t t1 -c “f1=11” -f “f1,f2” -D
This command will put the entry into csqltable.conf as:
“14 t1 NULL f1=11 f1,f2 myodbc3”
The above entry shows that f1 and f2 fields of t1 table is cached using direct option with condition f1=11. Here “-D” indicates that Caching is direct.

Direct Cache
In this cache mode, updates to cache directly go to the target database table and will not update the cached table. This mode is useful in cases where scalar functions or auto increment key is used during DML operations. In such cases, bi-directional cache mode should be turned on so that updates directly goes to target database and comes back to cache eventually within cache receiver wait seconds maintaining the data consistency between cache and target database. Use -D option to cache table in this mode.

csqlds tool plays important role in csql-cache

csqlds tool helps to populate csqlds.conf file

Before caching a table from Target database to csql you need to put entry into csqlds.conf file. It keeps the DSN information of the target database.

This blog gives details about csqlds.conf file

—————————————
Description about the csqlds.conf file
—————————————

It holds 4 information
1: DSN of target database
2: user name
3: password for the user
4: DB name of target database.

these informations you can get from your ~/.odbc.ini file
You can populate this file using a tool provided by csql called “csqlds”

$ csqlds [-U username] [-P passwd] [-D dsnname] [-N tdbname] -a

Example Sample:
$ csqlds root root123 myodbc3 mysql -a
After execution of this command, the entries for ‘myodbc3’ data source will be present in csqlds.conf file, which will be used by the cachetable tool to cache the table from this Data source
This command will put entry to your csqlds.conf file as
“myodbc3 root root123 mysql”

then proceed with cachetable tool for work with cache feature of csql

Configuring CSQL for replication

CSQL Replication offers complete data protection through automated, real-time, zero-latency fail over and recovery with high availability and load balancing cluster for CSQL MMDB. It provides both synchronous and asynchronous transaction propagation to peer instances. It enables scaling application throughput by sharing the read load across replicated CSQL instances.

Operational Restrictions
Replication imposes the following operational Restrictions:

• Replication is supported between CSQL instances
• A CSQL server instance can participate in only one quorum.

Once a table is replicated DDL statements cannot be executed on that table.

• DROP TABLE

The following additional restrictions also apply to tables defined for replication:

• The Primary key constraint not to be removed.
• Column not to be dropped or added.
• The Primary key column not to be modified.

The following SQL statements are permitted:

• ADD INDEX
• DROP INDEX

Configuration Parameter
The database server configuration file (csql.conf) under CSQL root directory includes eleven configuration parameters that affect the behavior of Replication.

Replication Configuration parameters:

SITE_ID
REPLICATION
REPLICATION_SITES
NETWORK_CONFIG_FILE
CONFL_RESOL_FILE
MSG_KEY
ASYNC_MSGMAX
MAX_QUEUE_LOGSNetwork Configuration Parameters:
CSQL_SQL_SERVER
PORT

Setting up parameters in csql.conf file
The configuration file, csql.conf has eleven parameters that need to be set up for
replication. Some of the parameters are present in Cache Section and most are present in
SQL Network Server Section in csql.conf file.

• REPLICATION

This is a Boolean parameter and should be set to true to enable replication across site.

• SITE_ID

This is an integer parameter and it identifies a CSQL instance in a quorum.Set this to a unique value for all the nodes in the quorum. For example, this parameter will be set to 1 for CSQL Instance-1 and will be set to 2 for CSQL Instance-2 and so on.

• CSQL_SQL_SERVER

This is a Boolean parameter and should be set to true when replication is turned on.

• PORT

This is an integer parameter to set the port for network access, by default it is set to the number 5678.

• NETWORK_CONFIG_FILE

This is a string parameter, which contains the complete path to the file, which holds the complete information about the peer site information in the quorum.

• MSG_KEY

This is an integer parameter is used for creating message queue for MsgQueue server. It is used when replication mode is Asynchronous only. This is an internal parameter and user need not modify this value.

The two instances of CSQL should work in two different machines, for that we have to configure the replication and set up the network file which contains the information about the peer site.
Based on this deployment, CSQL Instance-1 is considered as Source site or Active Site, CSQL Instance –2 is considered as Destination Site or Standby Site. The Replication
configuration is made accordingly both in csql.conf file and csqlnw.conf file.

Configure csql.conf file for CSQL Instance – 1.

The replication configuration parameters are as follows.

SYS_DB_KEY=3001
USER_DB_KEY=4001
LOG_FILE=/tmp/SITE1/log/csql/log.out
DATABASE_FILE=/tmp/SITE1/csql/db
SITE_ID=1
TABLE_CONFIG_FILE=/tmp/SITE1/csqltable.conf
DS_CONFIG_FILE=/tmp/SITE1/csql/csqlds.conf
CONFL_RESOL_FILE=/tmp/SITE1/csql/conflResoFile.txt
CACHE_MODE=SYNC
CSQL_SQL_SERVER=true
PORT=5001
REPLICATION=true
MSG_KEY=2531
ID_SHM_KEY=1951
NETWORK_CONFIG_FILE=/tmp/SITE1/csqlnw.conf

Configure csql.conf file for CSQL Instance – 2
The replication configuration parameters are as follows.

SYS_DB_KEY=3002
USER_DB_KEY=4002
LOG_FILE=/tmp/SITE2/log/csql/log.out
DATABASE_FILE=/tmp/SITE2/csql/db
SITE_ID=2
TABLE_CONFIG_FILE=/tmp/SITE2/csqltable.conf
DS_CONFIG_FILE=/tmp/SITE2/csql/csqlds.conf
CONFL_RESOL_FILE=/tmp/SITE2/csql/conflResoFile.txt
CACHE_MODE=SYNC
CSQL_SQL_SERVER=true
PORT=5002
REPLICATION=true
MSG_KEY=2532
ID_SHM_KEY=1952
NETWORK_CONFIG_FILE=/tmp/SITE2/csqlnw.conf

Configure csqlnw.conf file for each instance:
The network configuration file should contain the information about both the CSQL Instances and it should contain the below specified information. The file path has to be mentioned in the NETWORK_CONFIG_FILE parameter in csql.conf file.
File Format:

ID : IP_address : Port : Repl_Mode

Synchronous Replication
In case of synchronous mode, source site transaction commits return only after the transaction is committed at source instance and other destination instances in the quorum.
Information in csqlnw.conf file at Instance-1

1:localhost:5001:SYNC
2:localhost:5002:SYNC

Information in csqlnw.conf file at Instance-2

1:localhost:5001:SYNC
2:localhost:5002:SYNC

Steps to replicate table :
Follow the below steps to replicate data from Instance-1 to Instance-2. Before beginning with operations, Start the CSQL Server in two hosts with all necessary
configuration parameters in csql.conf file.

Start the CSQL Server at both the Instances.
The below contents shows that all the required servers are running and it should match at
both the Sites except Server’s PID value.

$csqlserver
Sys_DB [Size=0001MB]
User_DB [Size=0010MB]
Network Server Started [PID=21640]
Replication Server Started [PID=21641]
Database Server Started…

Replicate the table at Instance-1 using “repltable” tool
Assume that the “emp” table is present at the Instance-1 with some records. Use repltable tool to replicate the table.

$csql

CSQL> create table emp(f1 int, f2 int);

$ repltable –t emp
After executing this command entry will be made into the csqltable.conf file.

Replicate the table at Instance-2
By executing the below command the table is replicated at Instance-2 and the entry is made in csqltable.conf file.
$ repltable –t emp
Now the table ‘t1’ is present in Source Site as well as in Destination Site. Insert some records using csql tool with -r option at Instance-1 and it will be replicated to
Instance-2.

repltable tool in CSQL

repltable tool
The repltable tool is used to replicate table between sites. To do so, the source site needs to publish the table so that other sites can subscribe it.
Syntax:
repltable [-U username] [-P password] [ -t <TableName> [-E] [-u] ] [?]
-U username
This is a optional argument and its required for authentication.
-P password
This is a optional argument also required for authentication.
-t tablename
The specified table name will be replicated with this –t option.
-u
To keep the table unpublished and as a normal table.
-E
To change the table statues from cached to replicated.

replverify tool in CSQL

replverify tool
replverify tool displays the missing records in the specified replicated table in either
sites, if any. This tool should be used only when CSQL_SQL_SERVER=true is set in the
csql.conf file.
Syntax:
replverify
[-U username]
[-P passwd]
-t <tablename>
-i <siteID>
[-p]
[-f]
[?] Help

Description:
username -> username to connect with csql.
passwd -> password for the above username to connect with csql.
tablename -> replicated table name which is replicated in quorum.
siteID -> sitedID of remote site.
p -> verification at primary key field level
f -> verification at record level
? -> help

1. Verification based on number of records
[SITE1]$ replverify -U root -P manager -t t1 -i 2

The above table shows that there are 4 records in both the sites in the quorum.

2. Find missing records using -p option with replverify
[SITE1]$ replverify -U root -P manager -t t1 -i 2 -p

The above table shows that There is no missing records in either of the sites in the quorum.

3. Find mismatching field values using -f option with replverify
[SITE1]$ replverify -U root -P manager -t t1 -i 2 -f

Example:
If any record/records are missing from any/both of the sites, or any mismatching field values are there in either of the sites
Records in table t1 at SITE1

CSQL>select * from t1;

Records in table t1 at SITE2

CSQL>select * from t1;

[SITE1]$ replverify -t t1 -i 2 -f

The above output shows that the record having primary key value 1 has the value 10 in f2 at SITE1 and 15 in f2 at SITE2. The record having primary key value 5 has the value 55 in f2 at SITE1 and 50 in f2 at SITE2

csql’s cacheverify tool

Cacheverify tool displays the missing records in the specified cached table either in CSQL or in target database, if any. This tool should be used only when CACHE_TABLE
option is set in the csql.conf file.

Syntax:

$ cacheverify
[-U username]
[-P passwd]
-t tablename
[-p]
[-f]

-U username
Username for authentication
-P passwd
password for authentication also.
-t tablename
cached table name in csql from target db.
-p
verification at primary key field level
-f
verification at record level
?
help

Table t1 in Target database

sql> create table t1(f1 int,f2 int, primary key(f1));

sql> select * from t1;

CSQL supports Partial Table Caching

CSQL introduces two types of Partial Caching.

* Record Level (Horizontal Cache)
* Column Level (Vertical Cache)

Records in Target Database

CSQL>select * from t1;

————————————-

f1 f2

————————————-

1 Hello World
2 Hi
3 Ram
4 CSQL
5 Lakshya

Record Level Cache:

Use -c option to specify the condition for partial caching. Records, which satisfy the condition, will be cached in CSQL from the target database.

$cachetable -U root -P manager -t t1 -c “f1 < 3” CSQL>select * from t1;

CSQL>select * from t1;

———————————–

f1 f2

———————————–

1 Hello World
2 Hi

Column Level Cache:

Use -f option for specifying the field name list to retrieve only values of those fields from the original table

$cachetable –U root -P manager -t t1 -f “f1”

CSQL>select * from t1;

—————————————

f1

—————————————

1
2
3
4
5

It caches only record values for ‘f1’ field in table ‘t1’ from the target database.

Cachetable tools in CSQL

CSQL Cache enables customers to significantly Improve Application Response times and throughput. Based on the CSQL In-Memory DBMS, the CSQL Cache option delivers a real-time, dynamic, updatable cache for frequently accessed data in the Disk residence DBMSs (DRDBMS) like MySql, Postgres, Oracle, DB2, Sybase.

cachetable tool

The cachetable tool is used to cache table from target database with the specified mode.

Example:

$ cachetable -t Employee

In this example, cachetable is used with -t argument to cache the “Employee” table from target database. -t argument is mandatory for cachetable tool. Besides this one other
arguments are there, these can be chosen and used as per the application requirement. You can see all the options of cachetable tool using the help command given below. Except -t option all other options are optional from the list of options given below.

$ cachetable -?

Usage: cachetable [ -U <username> ] [ -P <password> ]
[ -t <tblName> [-D] [ -c “condition” ]
[-f “fieldListToCache”]
[ -p <fieldName> [-F] ]
[ -u [-s] ]
[ -s | -r | -E ] ]
[ -S | -R ]

U -> Username to connect with csql.
P -> Password for above username.
t -> Table name to be cached in csql from target db.
D -> Enable direct access option to target database.
c -> Conditional expression used in std SQL WHERE clause.
f -> List of field names to be cached. Comma separated.
p -> Not Null unique index field name for Bidirectional
caching on which trigger needs to be run.
F -> Forceful caching.
s -> Load only the records from target db.
Assumes table is already created in csql.
u -> Unload the table. If used with -s option,
removes only records and preserves the schema.
r -> Reload the table. Get the latest image of table from target db.
E -> Elevate table status from Replicated to Cached and Replicated.
S -> Cache Description for cached tables.
R -> Recover all cached tables from the target database.

Before using cachetable set the following environment variables in csql.conf

CACHE_TABLE=true

DS_CONFIG_FILE=/tmp/csql/csqlds.conf [this path you can set of your own, which is suitable to you]

TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf [this path you can set of your own, which is suitable to you]

Populate csqlds.conf file with <target db DSN> <username> <password> <target ddb name>

You can use csqlds to papulate csqlds.conf file also as follows

$ csqlds [-U username] [-P passwd] [-D tdb dsnname] [-N tdbname] [-a]

Now Use cachetable tool . The below are some examples given how to use cachetable tool with different options.

1. cache a table from target database

$cachetable -U root -P manager -t t1

2. Unload the cache table using -u option

$ cachetable -U root -P manager -t t1 -u

3. Cache records, which satisfy condition

$cachetable -U root -P manager -t t1 -c “f1<3”

4. Cache only specified fields

$cachetable -U root -P manager -t t1 -f “f1”

5. Reloading the Cache table

$cachetable -U root -P manager -t t1 -r

5. Caching table using Direct Option

$cachetable -U root -P manager -t t1 -D

7. Cache Table Information
The -S option is used to get the cache table information. If it is used with any specific table name, which has been cached, then only that table information will be displayed.

$cachetable -U root -P manager -S

$cachetable -U root -P manager -t <TableName> -S