CSQL Tool Reference

<< Database Recovery - TOC

CSQL provides certain tools to access data from CSQL database. These are

  • csql
  • catalog
  • csqldump

csql

The CSQL tool contains three argument and all are optional. If the user is typed the ‘csql’ in command prompt without user name and password, the tool responds

Arguments: 

[-u username]	Username of the user
[-p password]	Password of the user
[-s sqlfile]	This file should contain SQL statements to be executed
[-U] 			This option will stop generating undo-logs.

CSQL tool, which is a sub-shell used to access the CSQL database. It supports most of the standard SQL statements.

Make sure that csqlserver is running prior to running this tool. Run csql to get CSQL sub shell. 

$csql
CSQL>

catalog

This tool displays meta-data information stored in system and user database.

Arguments: 

-u username	username of the user 
-p password	password of the user
-l 	lists all the tables with field information 
-i		reinitialize catalog tables dropping all the tables
-d		prints the database usage statistics for system and user database
-T table	prints the table information
-I index	prints the index information
-D lock | trans | proc | chunk prints debug information for system tables.

If the username is not mentioned then it will list all the tables with only their names. If multiple options are specified then only the last option is considered for processing. Let us understand some of the outputs of the command.

Create two tables in the database as follows with the help of CSQL tool. 

$ csql
CSQL> create table t1(f1 int, f2 char(20), f3 float);
Statement Executed

CSQL>create table emp (eid int, name char (20), sal float);
Statement Executed

CSQL>quit;

The two tables, t1 and emp created. See how the catalog tool displays the details of the two tables. 

$ catalog -l
<TableNames>
<TableName> t1 </TableName>
<TableName> emp </TableName>
</TableNames>

This is a default behavior as mentioned before since there is no username provided.

Explanation for tool arguments:

  • List all the tables with field information and index information 
    $catalog -u root -p manager –l
  • This will print the database usage statistics 
    $catalog -u root -p manager –d
  • Field and Index information of the specified table 
    $catalog -u root -p manager -T <table-name>
  • This will list index information of the index specified. 
    $catalog -u root -p manager -I <index-name>
  • This will list process table information 
	
    $catalog -u root -p manager -D proc
  • This will list lock table information 
    $catalog -u root -p manager -D lock
  • This will list transaction table information 
    $catalog -u root -p manager -D trans
  • List all the chunk information for both system and user 
    $catalog -u root -p manager -D chunk
  • This is same as -d option 
			
    $catalog -u root -p manager –ild
  • This will drop all the tables from the database 
    $catalog -u root -p manager -i

csqldump

csqldump is a tool that generates a SQL file, which is a dump of all the tables with records in CSQL database.

Syntax: 

csqldump 

[-u username] 
[-p password] 
[-c]
[-n numberOfStmtPerCommit]
[-t <tableName>]

Usage:

[-u username] 

	This is a mandatory argument. Username is required for authentication.

[-p password 

	This is also a mandatory argument. 
        Password for the above mentioned username to connect to the database.

[-c] 

	It includes all the cache tables in the dump output.

[-n noOfStmtsPerCommit] 

	This option is worked for number of statements per commit. Default value is 100. 
        If system database size is bigger, then it shall be increased.

-t <table_name> 

	Will dump only the table specified with this option

Note: csqldump does not output cache tables by default. Use -c option to include cache tables.

Now create some tables and insert some of the tuples into those tables. Run the csqlserver in one terminal. Open another terminal, and run csql tool. 

$ csql
CSQL> set autocommit off;
AUTOCOMMIT Mode is set to OFF

CSQL> create table t1(f1 int, f2 char(30), primary key(f1));
Statement Executed

CSQL> insert into t1 values (1, 'Lakshya');
Statement Executed: Rows Affected = 1

CSQL> insert into t1 values (10, 'Uttara');
Statement Executed: Rows Affected = 1

CSQL> commit;

CSQL> create table emp (empId int, empName char (40), empSal float, primary key (empId));
Statement Executed

CSQL> insert into emp values (1001, 'Jitendra', 1000.00);
Statement Executed: Rows Affected = 1

CSQL> insert into emp values (1002, 'Dharmendra', 2000.00);
Statement Executed: Rows Affected = 1

CSQL> commit;
CSQL> quit;

$ csqldump

CREATE TABLE t1 (f1 INT NOT NULL, f2 CHAR (30)); 
CREATE INDEX t1_idx1_Primary on t1 (f1) UNIQUE; 
CREATE TABLE emp (empId INT NOT NULL, empName CHAR (40), empSal FLOAT); 

CREATE INDEX emp_idx1_Primary on emp (empId) UNIQUE; 
SET AUTOCOMMIT OFF; 
INSERT INTO t1 VALUES (1, 'Lakshya'); 
INSERT INTO t1 VALUES (10, 'Uttara'); 
COMMIT; 

INSERT INTO emp VALUES (1001, 'Jitendra', 1000.000000); 
INSERT INTO emp VALUES (1002, 'Dharmendra', 2000.000000); 
COMMIT;

csqldump displays records as SQL INSERT statements. For each record, which is present in CSQL database table, it displays INSERT statement with respective values of that record.

<< Database Recovery - TOC

Page last modified on September 26, 2009, at 01:04 AM