Tools for Cache
<< Working with CSQL Gateway - TOC - Overview of CSQL Cache System >>
The tool cachetable is used when table is cached from target database into CSQL. This needs to be invoked when the csqlserver process is running. Before cachetable tool is used, data sources should be added using csqlds tool.
The tool cacheverify is used to display the missing records in the specified cached table either in CSQL or in target database, if any.
csqlds
Csqlds tool is used to add data sources to CSQL, which is used at the time of caching a table from target DB.
Example:
csqlds -U <UserName> -P <PasssWd> -D <DS_Name> -N <Target_DB_Name> -a csqlds -U root -P root123 -D myodbc3 -N mysql -a
Note : Data source name should be unique
Syntax:
csqlds [-U username] [-P passwd] [-D DsnName] [-N Target DB name] [-a] [-r] -U username The entries for this argument will be DS’s user name. It can be NULL. -P password The entries for this argument will be DS’s password. It can be NULL. -D dsnname This option accepts the DS name. -N Target DB name This option accepts the Target DB name from which the DS belongs. Valid values are mysql, postgres, oracle, db2, sybase -a This argument helps to add the data source. -r To delete the existing data source name .
Adding 'myodbc3' DS.
csqlds -U root -P root123 -D myodbc3 -N mysql -a
After executing this command ‘myodbc3’ data source will be added. Note that ‘-a’ option is used to add entries.
Add Entries for ‘psql’ DS without username and password.
csqlds -D psql -N postgres -a
After execution of this command, the data source ‘psql’ DS without user name and password is added
Delete the “myodbc3” DS from the csqlds.conf file.
csqlds -D myodbc3 -r
After execution of this command, ‘myodbc3’ data source will be deleted.
Note: that ‘-r ’ is used to delete the existing data source.
cachetable
The cachetable tool is used to cache table from target database with the specified mode.
cachetable tool works at command prompt
cachetable is a tool to cache the table from the target database into CSQL. This needs to be invoked when the csqlserver process is running.
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. Except this one all other arguments are optional, these can be chosen and used as per the application requirement.
Syntax :
cachetable [-U username] [-P passwd] -t tablename [-d ds name] [-c "condition"] [-f "field names"] [-p fieldname] [-D] [-R] [-s] [-r] [-F] [-S] [-?] -U username This is a mandatory argument. Username is required for authentication. -P passwd This is also a mandatory argument. Both username and password would be used for authentication. -t tablename The specify table name will be cached with this -t option . -d ds Name It accepts the DS name, which is not mentioned in csql.conf file and mentioned in csqlds.conf file. -c “condition” At the time of cache a table the condition value cab be used. This is optional argument. -f “fieldnames” Field name list for doing partial caching with specific fields -D Enable direct access option to target database -R Recover all cached tables from the target database. -s Load only the records from target db. Assumes table is already created in csql. -r Reload the table. Get the latest image of table from target db. -u Unload the table. If used with -s option, removes only records and preserves the schema. -F Forcefully cache table. Used together with -p option. -S Displays all the information related to cache table. -? Help
cache a table from target database
$cachetable -U root -P manager -t t1
Unload the cache table using -u option
$ cachetable -U root -P manager -t t1 -u
Cache records, which satisfy condition
$cachetable -U root -P manager -t t1 -c "f1<3"
Cache only specified fields
$cachetable -U root -P manager -t t1 -f "f1"
Reloading the Cache table
$cachetable -U root -P manager -t t1 -r
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
SQL Statements for Cache
In the above section, 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” ;
cacheverify
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
Verification based on number of records
$cacheverify -t t1 Number of Records: -------------------+-------------------+----------+ Data | CSQL | TargetDB | -------------------+-------------------+----------+ No. Of Records | 4 | 4 | -------------------+-------------------+----------+
Find missing records
$cacheverify -t t1 -p Number of Records: -------------------+-------------------+---------------+ Data | In CSQL | In TargetDB | -------------------+-------------------+---------------+ No. Of Records | 3 | 3 | -------------------+-------------------+---------------+ Primary key field name is 'f1' Missing Records: Marked by 'X' -------------------+-------------------+----------------+ Primary Key | In CSQL | In Target DB | -------------------+-------------------+----------------+ 1 | X | | 4 | | X | -------------------+-------------------+----------------+
The above output shows that the record having value 1 is missing from CSQL and the record having value 4 is missing from Target DB.
Find mismatching field values
$ cacheverify -t t1 -f Number of Records: -------------------+-------------------+-----------------+ Data | In CSQL | In TargetDB | -------------------+-------------------+-----------------+ No. Of Records | 3 | 3 | -------------------+-------------------+-----------------+ Primary key field name is 'f1' Missing Records: Marked by 'X' -------------------+-------------------+-----------------+ Primary Key | In CSQL | In Target DB | -------------------+-------------------+-----------------+ 1 | X | | 4 | | X | -------------------+-------------------+-----------------+ Inconsistent Records for the same key: --------------+------------+--------+---------------+ Primary Key | Field Name | CSQL | Trgt DB | --------------+------------+--------+---------------+ 2 | t1.f2 | World | Hi All | 3 | t1.f2 | Hi | India | --------------+------------+--------+---------------+
This tool is useful at the time of cache. cacheverify tool directly helps the users to detect the missing records as it is discussed in above example.
<< Working with CSQL Gateway - TOC - Overview of CSQL Cache System >>