DB SQL utilities list

ctpath - Change Internal (SQL) Database Paths

The ctpath utility allows you to adjust the path in the dictionary after extracting the SQL database directory from a backup and renaming it.

Changes the internal FairCom DB SQL dictionary paths of database locations.

Syntax

ctpath  [-s server] [-u user] [-p password] [-d database] [-v] from-path-prefix to-path-prefix

Options:

  • -s server - FairCom DB server name
  • -u user - user name
  • -p pw - user password
  • -d database - database name
  • -v - verbose
  • from-path-prefix - path prefix to be replaced
  • to-path-prefix - path prefix to use as replacement

Description

  • Command-line switches may not have optional spaces between the switch and the argument. Example: -s FAIRCOMS is not the same as -sFAIRCOMS.
  • Command-line switches may be entered in any order, but the from-path-prefix must appear before the to-path-prefix.
  • Command-line switches should start with a '-' or '/' character. Command line switches accept both lowercase and uppercase characters, e.g. -s or -S are the same.
  • ctpath returns 0 when the execution detected no errors. Non-zero values are returned when errors are detected. Error messages are written to stderr.
  • If you omit the -d database switch, all databases in the session will be scanned.
  • The -r switch indicates to repeat the substitution and replace all occurrences of the searched path and not just the first one.
  • The -v command-line switch indicates verbose output.

 

createfilesethost - FairCom DB SQL Fileset Host Utility

Opens a template table, extracts the definition, and creates a fileset host file.

Syntax

createfilesethost -t template [-n host name] [-u user] [-p pwd] [-s server]
  • u user name (default: admin)
  • p password (default: ADMIN)
  • s server name (default: FAIRCOMS)
  • n host name (default:host_TEMPLATENAME)

 

ctsqlbigint - FairCom DB SQL Big Integer Fix Utility

A BIGINT issue on HIGH LOW machines could result in improper values on heterogeneous systems. This utility is designed to detect and correct such issues should they occur. Contact FairCom should you require this utility.

Syntax

ctsqlbigint [-<sect>] [dbname [tblname]]

This utility to be compiled as single user with tranproc and run in the FairCom DB SQL operating directory.

  • <sect> the sector size
  • dbname is specified the engine converts the tables in that database. If dbname is not specified all databases will be converted.
  • tblname only the specified table in the given database is converted.

Running the conversion twice restores records to their original values.

 

ctsqlcdb - FairCom DB SQL Database Maintenance Utility

Operational Model:

  • Client

Usage

ctsqlcdb <command with args> [<servername>] [<user> <password>]]
 

Valid Commands:

  • -add <dbname>: Adds a reference to an existing database
  • -addctdb <dbname> <path>: Adds a reference to an existing CTDB database located at <path>
  • -create <dbname> [-casesensitive|-cs|-caseinsensitive|-ci]: Creates a new database
  • -create_preimage: Creates a preimage only database (supporting only atomicity; no transaction logs for durability.)
  • -drop <dbname>: Removes a reference to an existing database
  • -exist <dbname>: Returns 1 if the database exists, 0 if not, 2 on error
  • -list <servername>: Lists databases available from the server

Copy an existing database and add a reference to the new copy:

  • -copy <dbname> <newname> [<servername>]

-copy also includes non c-tree files when present in the database directory area.

where:

  • <dbname> is the database name, and
  • <servername> is the optional FairCom DB SQL name.

The ‑copy command supports virtual tables (called "Multi Record Tables" or "MRT tables").

This utility supports the use of an encoded password file. Encoded password files keep user IDs and passwords from plain view when the utility is used within a script file. They are created with the ctcmdset utility. The plain text form of the file should be:

; User Id
USERID ADMIN
; User Password
PASSWD <pass>

Use the -1 option to specify the name of the encoded file.

 

ctsqlimp - FairCom DB SQL Import Utility

See ctsqlimp. 

 

ctsqlutl - FairCom DB SQL Table Maintenance Utility

The FairCom DB SQL table maintenance utility, ctsqlutl, is available as a general purpose utility to perform maintenance on FairCom DB SQL databases. This includes renaming tables and columns.

The ctsqlutl utility syntax is as follows:

ctsqlutl [command] arg1 arg2 ... argn [options]

Valid Commands:

  • -rencol: rename column: arg1= Table name arg2= Current column name arg3= New column name

Renames a column within a specified table.

  • -rentbl: rename table: arg1= Current table name arg2= New table name

Renames a table.

Valid Options:

  • -o: Owner of table
  • -d: Database name (default: ctreeSQL)
  • -s: FairCom DB SQL Server name (default: FAIRCOMS)
  • -u: UserID for logging into FairCom DB SQL
  • -a: Password for authentication
  • -h: Display usage help

 

ctsqlmdd - FairCom DB SQL Merge Database Utility

It is necessary at times to move a FairCom DB SQL database from one system to another. This can involve path changes which need to be reflected in the session and database dictionaries. The FairCom DB SQL Move Database Utility can be used to simplify this process. This utility allows you to specify target and destination FairCom DB SQL from which the database dictionary will be moved and/or updated.

Usage

ctsqlmdd svn [-u uid] [-p upw] [-S svn] [-U uid] [-P upw] [-n sect]

Where:

  • svn: source FairCom DB SQL name (may be "local" for direct access)
  • -u uid: user name on source server
  • -p upw: user password on source server
  • -S svn: destination FairCom DB SQL Server name
  • -U uid: user name on destination server
  • -P upw: user password on destination

FairCom DB SQL uses the c-treeDB API database API to generate and maintain the session and database dictionaries necessary to maintain FairCom DB SQL relational databases. (The c-treeDB API functions, ctdbMergeSessionDictionary() and ctdbMergeDatabaseDictionary(), provide the core functionality for this utility.)

The source code for this utility, ctsqlmdd.c, demonstrates a generic implementation of the activities needed to achieve a session and/or database merge between systems. For precise applications, or to embed these activities into an application, the user is invited to view and inspect this source code example.

 

cttrnmod - Change Transaction Mode Utility

cttrnmod allows an advanced user to change the transaction status of a FairCom DB data file and its associated index files. The utility can also be used to display the transaction status of a c-tree data file and its associated indexes.

It is expected only advanced database administrators will run this utility.

Operational Model:

  • Client

Usage

cttrnmod (set <tranmode>|get) (-d <database>|-f <filelist>)
         [-u <userid>] [-p <password>] [-s <servername>] [-n <sect>]

Where

  • set <tranmode> - Set the transaction mode to one of the following:
    • T - Full Transaction Control
    • P - Partial Transaction Control (No Recoverability)
    • N - No Transaction Control (No Recoverability)
      • repl=on - Enable replication (requires full transaction control)
      • repl=off - Disable replication
  • The following extended header attributes may also be set:
    • {+,-}R - {Enable,Disable} Restorable deletes
    • {+,-}C - {Enable,Disable} Transaction controlled deletes
    • {+,-}A - {Enable,Disable} Auto transaction switching
  • get - Display the current transaction mode
  • -d or -f - Operate on all files in the database or all listed files:
    • -d <database> - Operate on all files in the c-tree database <database>
    • -f <filename> - Operate on all files listed in the file <filelist>
  • -u <userid> - Specify c-tree user ID
  • -p <password> - Specify c-tree user password
  • -s <servername> - Specify FairCom Server name to connect to. Default: FAIRCOMS
  • -n <sect> - Specify node sector size. Default: 64 (PAGE_SIZE=8192)

The files to change are specified by either the -d <database> option or the -f <filelist> option. The -d <database> option specifies the name of a c-tree database -- when this option is specified, the utility operates on all files referenced in that database (excluding SQL system data and index files). The -f <filelist> option specifies the name of a text file containing names of c-tree data files, one per line -- when this option is specified, the utility operates on all files specified in that text file.

Note: Indexes created with ctPREIMG or ctTRNLOG are physically structured differently than indexes that do not support transactions. Thus a non-tran index cannot be converted to transaction control, and must be rebuilt after the conversion. If an index file is created ctPREIMG or ctTRNLOG, it can be accessed in all transaction and non-transaction access modes.

Important Performance Considerations

When turning transaction processing off for a file, it is possible to take an even larger performance hit under specific FairCom Server configurations. Be sure to remove or comment out the line COMPATIBILITY FORCE_WRITETHRU from your FairCom Server configuration file ctsrvr.cfg. While this option provides only the safest of data integrity for your non-transaction processing controlled files, it forces an enormous performance penalty for doing so. This keyword has historically been included by default with most FairCom Server installations.

Examples

The following example demonstrates turning off transaction control for all c-tree data files and their associated index files in the rdsdb database:


# cttrnmod set N -d rdsdb
Setting transaction mode to NON_TRAN for files in database rdsdb...
 
  Tranmode  Filemode  Filename
  --------  --------  --------
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_deptbl.dat
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_deptbl.idx
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_dept_multi_ndx.idx
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_dept_ndx.idx
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_emptbl.dat
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_emptbl.idx
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_emp_no_ndx.idx
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_emptbl1.dat
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_emptbl1.idx
  NON-TRAN    0x0000  .\rdsdb.dbs\admin_emp_no_ndx1.idx
 
VERIFYING No Transaction Control...
VERIFY succeeded
 
   3 Data Files Updated
   0 Errors
 

The following example demonstrates reading the transaction status of the data and index files in the rdsdb database:


# cttrnmod get -d rdsdb
Reading transaction mode for files in database rdsdb...
 
  Tranmode  Filemode  Filename
  --------  --------  --------
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_deptbl.dat
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_deptbl.idx
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_dept_multi_ndx.idx
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_dept_ndx.idx
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_emptbl.dat
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_emptbl.idx
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_emp_no_ndx.idx
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_emptbl1.dat
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_emptbl1.idx
  ctTRNLOG    0x0031  .\rdsdb.dbs\admin_emp_no_ndx1.idx
 

ctTRANMODE Control (FairCom Database Engine V11 and later)

When using the Transaction Control utility, cttrnmod, to disable transaction support on a file with extended file mode ctTRANMODE, the utility could report that after successfully disabling ctTRNLOG, the file still has ctTRNLOG set. This is expected for a file with the ctTRANMODE bit set when using a TRANPROC c-tree application.

cttrnmod has been updated to disable ctTRANMODE and ctPIMGMODE bits when it sets a file to no-transaction support. It was also modified to support explicitly enabling or disabling one of these bits (depending on the file mode that is in effect at the time).

Replication

New replication actions have been added to the cttrnmod utility for flexible control of replication attributes.

  • cttrnmod now displays replication state for a data file
  • cttrnmod can change a file's replication state with the repl option

Note: Replication requires that the data file has a unique index and that the data and index files are using full (ctTRNLOG) transaction control.

Examples

  1. Enable full transaction logging on files:
    1. 
# cttrnmod set T -f files.txt -u ADMIN -p ADMIN -s FAIRCOMS



Setting transaction mode to ctTRNLOG for files listed in file files.txt...



  Replicate  Tranmode  Filemode  Filename

  ---------  --------  --------  --------

         NO  ctTRNLOG    0x0032  ctreeSQL.dbs\admin_t.dat

             ctTRNLOG    0x0032  ctreeSQL.dbs\admin_t.idx

             ctTRNLOG    0x0032  ctreeSQL.dbs\admin_t_ti.idx
    2. Note the "Replicate" column for current replication state information.
  2. Enable replication on files:
    1. # cttrnmod set repl=on -f files.txt -u ADMIN -p ADMIN -s FAIRCOMS

Enabling replication for files listed in file files.txt...



  Replicate  Tranmode  Filemode  Filename

  ---------  --------  --------  --------

        YES  ctTRNLOG    0x0032  ctreeSQL.dbs\admin_t.dat

             ctTRNLOG    0x8032  ctreeSQL.dbs\admin_t.idx

             ctTRNLOG    0x8032  ctreeSQL.dbs\admin_t_ti.idx

Note: If cttrnmod is used to disable full transaction logging for a file, it also disables replication for that file.

 

fkverify

It was possible that foreign key constraints could be violated in some versions of FairCom DB (V8.27 prior to revision 8662). A The Foreign Key Constraint Verification utility, fkverify, is available to check all Foreign Key constraints in a database to insure that referential integrity is maintained, and log any exceptions to a file.

This utility connects directly to FairCom DB SQL through the direct link ODBC interface thus is cross platform, and requires no additional components.

Syntax

fkverify [-s server][-p port][-d database]-a password

Where

  • -s Machine name (default: localhost)
  • -p Port (default: 6597)
  • -d Database (default: ctreeSQL)
  • -a Admin Password

 

sqlverify

The sqlverify utility can be used to diagnose low lying FairCom DB SQL issues and relational integrity. This utility connects to FairCom DB SQL databases and runs multiple tests and internal consistency checks. If problems or unusual results are detected they are logged to verify.log. The syntax for using this utility is as follows:

sqlverify [-s address][-p port][-d database][-n name][-f][-v] -a password

Valid Options:

  • -s : Machine name(default: localhost)
  • -n : Server name (default: FAIRCOMS)
  • -p : SQL Port (default: 6597)
  • -d : Database (default: ctreeSQL)
  • -f : Fix certain problems
  • -v : verbose
  • -a : Admin Password

An example verify.log file might look like the following:

Info: Table admin_custmast index 6
Info: Full Key Distinct Count was 81917, now 46218
Info: Table admin_custmast index 7
Info: Full Key Distinct Count was 81917, now 48939
Error: table admin_ordritem, index 0 holds 0 keys. Expected 1(1). Rebuild
 
List of inconsistencies:
 
    table | index | field | errorcd | errormsg
1 - syscolumns |  | logicalid | 4019 | Mismatch column nullflag

This example demonstrates an error as shown on line 5 of the output. In this case a rebuild of the index is indicated. Consult with FairCom's support team should you have any questions about the output of this utility against your database. The last section of inconsistencies should always be reported to FairCom's support team for investigation.

sql database

Was this article helpful?

Give feedback about this article

Related Articles