This is an old revision of the document!
OdxSQL is a console based program used for testing installations, prototyping queries, and exporting data. OdxSQL supports many functions designed to assist in creating fully optimized installations and queries including benchmarking and for executing explain plans to determine query performance.
OdxSQL allows the interactive specification of Omnidex SQL statements as well as OdxSQL specific commands. OdxSQL is primarily used to:
* Test query results from a newly created Omnidex indexes with SELECT statements. * Profile performance of queries using the timer functions. * Run Explain Plans to determine usage of Omnidex indexes. * Perform data utility functions such as partitioning or analyzing data. * Test ODBC performance using OdxSQL's ODBC interface either on the server or across the network. * Test JDBC performance using OdxSQL;s JDBC interface either on the server or across the network. * Exporting data using SELECT statement operations.
OdxSQL connects to either an Omnidex XML Catalog or an Omnidex Environment Catalog which can either be specified as a run time argument or via the CONNECT command once OdxSQL is running.
Run OdxSQL from the command line with the following syntax:
odxsql [XML Catalog or Environment Catalog] | [-use=use_file]
XML Catalog or Environment Catalog The environment_name parameter is the environment catalog filespec that OdxSql will connect to. OdxSql will try to connect to the specified environment. If this filespec is not passed as a program argument, then a CONNECT statement must be performed to establish a connection to the environment catalog.
-use=use_file The -use=use_file parameter instructs OdxSql to process all the commands contained within the specified use file. The right hand side of the parameter, use_file, is the filespec of the file containing the commands.
Example
odxsql
odxsql orders.env
odxsql -use=commands.txt
Type help at the command prompt with no arguments to see an alphabetical list of ODXSQL commands.
>help
…
Type help with the argument 'syntax' at the command prompt to see a list of commands with their syntax.
>help syntax
…
Type help followed by a specific command to see additional information about that command.
>help connect CONNECT Connect to an environment file Syntax: Connect [TO] filename [AS [user]/[password]] [WITH options]
See HELP in the ODXSQL Commands section for more information.
Commands with an * (asterisk) before the description are also available for use in oaexecsql, ODBC and JDBC applications. All others are available for use ONLY in ODXSQL.
| Command | Description |
|---|---|
| ! (Exclamation Point) | Execute a system command |
| . (Period) | Re-Execute the last command |
| ;(Semi-Colon) | Comment line |
| ABORT | Cause software abort |
| ASSIGNVALUES | * Assign values for a $VALUES clause |
| ATTACH | * Attach an OST file as a table |
| BENCHMARK | Perform performance benchmarks |
| BIND | Bind a SELECT statement |
| CALC | Perform basic calculator functions |
| CD | Change directories |
| CLOSE CURSOR | * Close a cursor |
| COMMAND | * Execute a shell command via $COMMAND table |
| CONNECT | * Connect to an environment file |
| CONVERT | Export a file with data conversions |
| COPY | Copy a fileset |
| CREATE DIRECTORY | Create a directory |
| CREATE FILE | Create a file |
| CREATE TABLE | * Create a table |
| DATE | Show the current date and time |
| DELETE | * Issue an SQL delete statement |
| DELETEINDEX | * Delete values directly from Omnidex indexes |
| DESCRIBE | Describe a SELECT statement |
| DETACH | * Detach a previously attached OST file |
| DIR | Display a directory of files |
| DISCONNECT | * Disconnect from an environment file |
| DROP | * Drop a table |
| DUMP | Dump files, memory or caches |
| EDIT | Edit a previous command or file |
| ERROR | Display an error message |
| EVAL | Evaluate a condition |
| EXECDB | Execute statement through oaexecdb() |
| EXIT | Exit Omnidex SQL |
| EXPLAIN | Explain approach to retrieval |
| EXPORT | * Export records to a file |
| EXTRACT | Extract SQL statements from database |
| FETCH | Explicitly fetch data from a database |
| FETCHKEYS | Explicitly fetch keys from the indexes |
| FLIP | Flip data from one endian to another |
| FOR | Form a 'FOR n … END' construct |
| FORMAT | Format a file of ODXSQL statements |
| GETENV | Display an environment variable |
| HELP | Help on OdxSQL commands |
| HISTORY | Show history of ODXSQL commands |
| IF | Form an 'IF ELIF ELSE ENDIF' construct |
| INSERT | * Issue an SQL insert statement |
| INSERTINDEX | * Insert values directly into Omnidex indexes |
| IS | Check database conditions |
| JOIN | * Join tables using Omnidex |
| LOAD | Load indexes for TDF files |
| OPEN CURSOR | * Open a cursor |
| OXKERNEL | Execute an Omnidex kernel command |
| PARSE | Parse a statement |
| PARTITION | Partition a table |
| PWD | Print current working directory |
| QUALIFY | * Qualify rows using Omnidex |
| QUIT | Quit Omnidex SQL |
| REMOVE | Remove one or more files |
| RENAME | Rename a fileset |
| RESET HISTORY | Reset history of ODXSQL commands |
| RESET TIMER | Reset the cumulative timer |
| RESTORE SETTINGS | Restore settings, optionally from a file |
| SAVE HISTORY | Save commands to file |
| SAVE SETTINGS | Save settings, optionally to a file |
| SELECT | * Issue an SQL select statement |
| SET | Set an option |
| SETENV | Set an environment variable |
| SHOW | Show information on databases, tables, and columns |
| SIGNAL | Send a signal to a process |
| SORTMERGE | Sort/merge test system |
| UPDATE | * Issue an SQL UPDATE statement |
| UPDATE STATISTICS | Update statistics for a database or table |
| UPDATE TEXT | Enable misspelling searches on MDK indexed columns. |
| UPDATEINDEX | * Update values directly in Omnidex indexes |
| USE | Execute a file of ODXSQL commands |
| VALIDATE | Validate an environment |
| VERSION | * Display current software version information |
| VIEW | View a text file |
| n | Execute numbered command from the OdxSQL History stack |
| n: | Send command to OdxSQL subprocess identified by relative number of the process |
© Copyright Dynamic Information Systems - This document was last updated July 15, 2009.