Maximize your Options with c-treeSQL Utilities

The c-treeSQL Server is a powerful tool to manage your data, and access your existing c-tree data for reporting. To ease the transition to the c-treeSQL Server a variety of tools are included, allowing you to import and export schemas and data, create new databases, and perform maintenance on existing tables. The following list of c-treeSQL utilities is available:

c-treeSQL Table Import Utility

c-treeSQL Interactive Utility, ISQL

c-treeSQL Load Utility, dbload

c-treeSQL Dump Utility, dbdump

c-treeSQL Schema Export Utility, dbschema.

c-treeSQL Database Create Utility, ctsqlcdb

c-treeSQL Maintenance Utility, ctsqlutl

You will find the Interactive c-treeSQL utility, iSQL located in the C:\FairCom\V8.14\ctreeSDK\ctreeISQL directory of your c-treeSQL Server installation. All of the other c-treeSQL utilities are found in the C:\FairCom\V8.14\ctreeTools\ctreeAdmin\Client directory of your c-tree Plus installation.

c-treeSQL Table Import Utility

The c-treeSQL Table Import Utility, ctsqlimp, links existing c-tree Plus files to a c-treeSQL database without modifying the table structure such that the files are still accessible by the original application and also accessible from c-treeSQL.

Syntax

ctsqlimp table_name [-i] [-d database_name] [-s server_name] [-u user_name]

                    [-a password]

Options

-d : database name (default: c-treeSQL)

-s : c-tree SQL Server name (default: FAIRCOMS)

-u : user name

-a : user password

-i : skip import of unsupported indexes

-h : display usage help

Example

To make existing c-tree files mydata.dat (with IFIL and DODA resources) and mydata.idx accessible via c-treeSQL, follow these steps:

Create a database named ctreeSQL.
Copy mydata.dat and mydata.idx into the ctreeSQL.dbs subdirectory of the server directory.
Make sure the c-treeSQL Server is running; the ctsqlimp is a c-treeSQL client application.
Run the ctsqlimp utility found in the ctreeServers\Utils\bin directory:


ctsqlimp mydata.dat

Follow the prompts regarding padding and index importing. Generally, the defaults are acceptable values. You will want to answer `yes' to updating the pad resource if asked.
You can now start the isql utility and issue the following c-treeSQL command to query the data in the table:


SELECT * FROM mydata;

c-treeSQL Interactive Utility, ISQL

The c-treeSQL Interactive SQL Utility (often referred to as ISQL) is a utility supplied with c-treeSQL Server giving you an ability to issue SQL statements directly from a command line with immediate results displayed at the terminal. Please consult the c-treeSQL ISQL and Tools Reference Guide, Chapter 3, for complete details.

Start ISQL by issuing the isql command at the command prompt. c-treeSQL invokes ISQL and displays the ISQL prompt:

C:\FairCom\V8.14\ctreeSDK\ctreeISQL> isql -u ADMIN -a ADMIN sampledb

                              FairCom/isql Version v8.14.872(Build-041010)

                FairCom Corporation             (C) 1992-2003.

                Dharma Systems Inc              (C) 1988-2003.

                Dharma Systems Pvt Ltd          (C) 1988-2003.

ISQL>

Issue c-treeSQL statements at the ISQL> prompt and terminate them with a semicolon. You can continue statements on multiple lines. ISQL automatically prompts for continuation lines until you terminate the statement with a semicolon.

To execute host operating system commands from the ISQL prompt, type HOST followed by the operating system command. After completion of the HOST statement, the ISQL> prompt returns. To execute SQL scripts from ISQL, type @ followed by the name of the file containing SQL statements.

To exit from interactive SQL, type EXIT or QUIT.

You can supply optional switches and arguments to the isql command.

Syntax

isql [-s script_file] [-u user_name] [-a password] [connect_string]

Options

-s script_file
The name of an SQL script file that c-treeSQL executes when it invokes ISQL.

Note: For Windows platforms, if the file name has a space, such as:
test script.sql
The file name must be enclosed in doubles quotes, such as:
isql -s "test script.sql" testdb

-u user_name
The user name c-treeSQL uses to connect to the database specified in the connect_string . c-treeSQL verifies the user name against a corresponding password before it connects to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_USER environment variable specifies the default user name. If DH_USER is not set, the value of the USER environment variable specifies the default user name.)

-a password
The password c-treeSQL uses to connect to the database specified in the connect_string . c-treeSQL verifies the password against a corresponding user name before it connects to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_PASSWD environment variable specifies the default password.)

connect_string
A string that specifies which database to connect to. The connect_string can be a simple database name or a complete connect string.

Example

To connect to a local database named myDatabase, you would use the following syntax:

>isql -u ADMIN -a ADMIN myDatabase

To connect to a remote database named c-treeSQL, you would use the 6597@remotehost:database syntax as follows:

>isql -u ADMIN -a ADMIN  6597@hotdog.faircom.com:ctreeSQL

See the CONNECT statement in the c-treeSQL Reference Manual for details on how to specify a complete connect string. If omitted, the default value depends on the environment. (On UNIX, the value of the DB_NAME environment variable specifies the default connect string.)

Execute a c-treeSQL SELECT command with the utility as follows:

ISQL>SELECT column1, column2 FROM table1 WHERE column3 > column4;

c-treeSQL Load Utility, dbload

The c-treeSQL Load Utility, dbload, imports data from another file into a c-treeSQL file. The dbload command accepts a commands file name, the database name, and a list of command options. Note that dbload does not directly specify an input file, but instead names a commands file that in turn specifies data input files. Please consult the c-treeSQL ISQL and Tools Reference Guide, Chapter 4, for complete details regarding the syntax of the commands_file.

Syntax

dbload -f commands_file [options] database_name

Options

-f commands_file
Specifies the file containing dbload commands.

-u user_name
The user name c-treeSQL uses to connect to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_USER environment variable specifies the default user name. If DH_USER is not set, the value of the USER environment variable specifies the default user name.)

-a password
The password c-treeSQL uses to connect to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_PASSWD environment variable specifies the default password.)

-z maximum multiple inserts
The maximum number of records to be inserted at one time in each bulk insert (used to improve performance)

-l logfile
Specifies the file into which the error logging is done. stderr is the default. dbload also writes statistics to the file:

Number of records read
Number of records skipped
Number of records loaded
Number of records rejected

-b badfile
The file into which the bad rows that were not loaded, are written. By default badfile is put in the current directory.

-c commit_frequency
Store the specified number of records before committing the transaction. The default frequency is 100 records.

-e maxerrs
The maximum number of tolerable errors. The default number is 50 errors.

-s skipcount
Skip the specified number of rows in the first data file. If multiple files are specified, the rows are skipped only in the first file. The default number is zero rows.

-m maxrows
Stop storing rows at the specified number.

-n
Parse the commands file and display errors, if any, without doing the database load. If the parsing is successful a message, ""No errors in the commands file"". displays on stdout .

database
Name of the database.

Example

To load external data to a c-treeSQL database execute the dbload command as follows:

>dbload -f mycommands -u ADMIN -a ADMIN ctreeSQL

The database ctreeSQL will be populated with data as instructed from the mycommands file.

c-treeSQL Dump Utility, dbdump

The c-treeSQL Dump Utility, dbdump , writes data from a c-treeSQL file to an output file. The format of the exported data is specified by the record description given in an input command file to dbdump . Please consult the c-treeSQL ISQL and Tools Reference Guide, Chapter 5, for complete details regarding this utility.

Syntax

dbdump -f commands_file [-u user_name] [-a password] [-n] database_name

Options

-f commands_file
Specifies the file containing dbdump commands.

-n
Parse the commands file and display errors, if any, without exporting data. If the parsing is successful a message, "No errors in the commands file". displays on stdout .

database_name
Name of the database.

Example

To dump data from a c-treeSQL database to an external file execute the dbdump command as follows:

>dbdump -f mycommands -u ADMIN -a ADMIN ctreeSQL

The database ctreeSQL data will be output as instructed from the mycommands file.

c-treeSQL Schema Export Utility, dbschema

The c-treeSQL Schema Export Utility, dbschema, generates c-treeSQL statements to recreate the specified database elements and data. This can be a great tool for backing up database schema designs, and building scripts to recreate databases.

If the dbschema statement omits all arguments, it displays definitions for all elements (tables, views, indexes, procedures, and triggers) for the default database on the screen. Please consult the c-treeSQL ISQL and Tools Reference Guide, Chapter 6, for complete details.

Syntax

dbschema [ -h ] [ -d ] [-u user_name ] [-a password ] [ -o outfile ]

         [ -p [ user_name.]procedure_name [ , ... ] ]

         [ -t [ user_name.]table_name [ , ... ] ]

         [ -T [ user_name.]trigger_name [ , ... ] ]

         [ database_name ]

Options

-h
Displays brief online help of dbschema syntax and options.

-d
In conjunction with the -t option, specifies that dbschema generates SQL INSERT statements for data in the tables, in addition to CREATE statements. The output of the dbschema command invoked with the -d option can be directed to a command file and executed in interactive SQL to duplicate and load table definitions and their content.

-u user_name
The user name c-treeSQL uses to connect to the database. c-treeSQL verifies the user name against a corresponding password before it connects to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_USER environment variable specifies the default user name. If DH_USER is not set, the value of the USER environment variable specifies the default user name.)

-a password
The password c-treeSQL uses to connect to the database. c-treeSQL verifies the password against a corresponding user name before it connects to the database. If omitted, the default value depends on the environment. (On UNIX, the value of the DH_PASSWD environment variable specifies the default password.)

-o outfile
Redirects the output to the specified file. The default is standard output.

-t [ user_name.]table_name [ , ... ]
A comma-separated list of tables and views for which definitions should be generated. Specify a list of specific tables, or use the % to generate definitions for all tables. (Note that, in the -t option, the % character is not a true wildcard character. It substitutes for the entire table_name argument and cannot be used for pattern matching within in a character string. This differs from the behavior of the % in the -p and -T options.)

By default, dbschema generates definitions for tables owned by the current user. Use the optional user_name qualifier to specify a table owned by a different user.

-p [ user_name.]procedure_name [ , ... ]
A comma-separated list of stored procedures for which definitions should be generated. The table names in the list can include the % and underscore ( _ ) characters, which provide pattern-matching semantics:

The % matches zero or more characters in the procedure name
The underscore ( _ ) matches a single character in the procedure name

By default, dbschema generates definitions for procedures owned by the current user. Use the optional user_name qualifier to specify a procedure owned by a different user.

-T [ user_name.]trigger_name [ , ... ]
A comma-separated list of triggers for which definitions should be generated. The table names in the list can include the % and underscore (_) characters, which provide pattern-matching semantics:

The % matches zero or more characters in the trigger name
The underscore ( _ ) character matches a single character in the trigger name

By default, dbschema generates definitions for triggers owned by the current user. Use the optional user_name qualifier to specify a trigger owned by a different user.

database_name
The database for which dbschema should generate definitions. If you omit database_name , dbschema uses the default database, if specified. (How you define the default database varies between operating systems. On UNIX, the value of the DB_NAME environment variable specifies the default database.)

Example

To export a database schema execute dbschema as follows:

>dbschema -u ADMIN -a ADMIN -o myschema.txt ctreeSQL

The exported table schema of the ctreeSQL database will be found in the myschema.txt text file.

c-treeSQL Database Create Utility, ctsqlcdb

The c-treeSQL Database Create Utility, ctsqlcdb, creates a new, adds an existing, or drops a c-treeSQL database from the c-treeSQL Server. Use ctsqlcdb whenever you need to create a new database on-the-fly. This utility avoids the cumbersome process of downing the server, editing the ctsrvr.cfg file with a new database entry, and restarting.

Syntax

usage: ctsqlcdb <command> <dbname> [<servername>]

where <command> is one of the following:

-add          Adds a reference to an existing database

-create       Creates a new database

-drop         Removes a reference to an existing database

Options

command
The command to execute on the database.

dbname
The database name.

servername
The optional c-treeSQL Server name.

Example

To create a new database named "my_new_database" execute ctsqlcdb as follows:

>ctsqlcdb -create my_new_database FAIRCOMS@localhost

The c-treeSQL Server will create a new directory for the database and tables, and initialize the system tables for the new database.

c-treeSQL Maintenance Utility, ctsqlutl

The c-treeSQL Maintenance Utility, ctsqlutl , is a general purpose program to perform maintenance on the c-treeSQL Server. At this time only the "rename column" (-rencol ) command has been implemented. Please refer to the V8.14 Update Guide, Section 14.7, for complete details.

Syntax

The ctsqlutl utility syntax is as follows:

ctsqlutl [options] -rencol table_name column newcolumn

table_name
Name of the table

column
Current name of the column you are going to rename

newcolumn
Name of the columns after renaming

Options

-o owner_name
Owner of table

-d database_name
Database name (default: ctreeSQL)

-s server_name
c-treeSQL Server name (default: FAIRCOMS)

-u userid
userid for logging onto the c-treeSQL Server

-a password
Password for authentication

-h
Display usage help

Example

To rename a column in a c-treeSQL database use ctsqlutl as follows:

>ctsqlutl -u ADMIN -a ADMIN -s FAIRCOMS -d ctreeSQL -rencol t1 oldname newname

\| Home \| About FairCom \| Products \| Support \| Sales \| Contact Info \| Site Map \| Download \| Newsletter \|