OdxSQL Commands

OdxSQL:	Overview	Commands	Settings	Command Line	Quick Ref

Overview

OdxSQL is used to process a wide range of Omnidex SQL Statements and OdxSQL specific commands.

The OdxSQL Commands and Omnidex SQL Statements are organized for quick review as follows:

Connecting and Disconnecting
Omnidex SQL Data Definition Language (DDL) Statements
Omnidex SQL Data Manipulation Language (DML) Statements
Omnidex Environment Operation Commands
OdxSQl and Omnidex SQL Data Export and Manipulation
OdxSQL Program Operation Commands
OdxSQL Testing and Performance Commands
OdxSQL Operating System Commands
Omnidex Index Direct Maintenance Routines
Alphabetical List of OdxSQL Commands and Omnidex SQL Statements

OdxSQL Commands and Omnidex SQL Statements can be entered into OdxSQL in ether upper or lower case.

> connect myenv

> CONNECT myenv

The syntax for Omnidex SQL Statements entered into OdxSQL or processed in a script file is typically the exact same syntax and returns the exact same results.

There are a few exceptions such as CONNECT and VERSION.

In OdxSQL, CONNECT has two additional options of ON host:port and USING api that are not available in the Omnidex SQL CONNECT Statement. The api is not necessary has it is already known depending on the application type (ODBC or JDBC) and ON

In OdxSQL, the VERSION command displays the OdxSQL header and multiple lines of version information where the Omnidex SQL VERSION statement returns a single line.

The OdxSQL EXECSQL command can be used to verify if an Omnidex SQL Statement is processed exactly the same by OdxSQL as the Omnidex SQL parser.

> VERSION
ODXSQL - Omnidex SQL                                   Wed Dec 02 09:20:41 200
...
Omnidex Version                  5.0 Build 2B
Compilation Timestamp            Oct 13 2009  08:57:09
Earliest Compatible ODBC Client  4.3 Build 4B
Earliest Compatible JDBC Client  4.3 Build 4B
Earliest Compatible Odx Kernel   5.0 Build 1B

> EXECSQL VERSION
OmniAccess version 50002
>

Connecting and Disconnecting

OdxSQL works by first connecting to an Omnidex Environment or an ODBC or JDBC DataSource (DSN) that references an Omnidex Environment on a remote server. Although ODBC and JDBC applications use a connection call or object, OdxSQL uses Connect and Disconnect commands.

CONNECT	Both	Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN).
DISCONNECT	Statement	Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN).
ATTACH	Statement	Attach an OST file as a table to the currently connected Omnidex Environment.
DETACH	Statement	Detach a previously attached Omnidex Standalone Table (OST) file.

Omnidex SQL Data Manipulation Language (DML) Statements

OdxSQL can process all Omnidex SQL Data Manipulation Statements (SELECT, INSERT, UPDATE, and DELETE) as well as lower level Omnidex SQL routines.

SELECT	Statement	Issue an Omnidex SQL SELECT Statement.
INSERT	Statement	Issue an Omnidex SQL INSERT Statement.
UPDATE	Statement	Issue an Omnidex SQL UPDATE Statement.
DELETE	Statement	Issue the SQL DELETE Statement.

Omnidex SQL Data Definition Language (DDL) Statements

DDL Statement	Description
CREATE ENVIRONMENT	Statement	Create an Omnidex Environment.
CREATE DATABASE	Statement	Create ad Database within an Omnidex Environment.
CREATE TABLE	Statement	Create a Table.
CREATE INDEX	Statement	Create Index
CREATE INDEXES	Statement	Create Omnidex Indexes.
DROP ENVIRONMENT	Statement	Drop Environment.
DROP DATABASE	Statement	Drop Database
DROP TABLE	Statement	Drop a previously created temporary table.
DROP INDEX	Statement	Drop Index.
DROP INDEXES	Statement	Drop Indexes.

Additional Omnidex SQL Statements

JOIN	Statement	Join tables using Omnidex Indexes.
QUALIFY	Statement	Qualify rows using Omnidex criteria.
FETCH	Statement	Explicitly fetch data from the database.
FETCHKEYS	Statement	Explicitly fetch keys from the Omnidex Indexes.
OPEN CURSOR	Statement	Open a Cursor.
CLOSE CURSOR	Statement	Close a previously opened cursor.

OdxSQL Environment Operation Commands and Statements

OdxSQL has commands that operate and update information for an Omnidex Environment and the individual databases contained in the Omnidex Environment.

SHOW	Command	Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc.
VALIDATE	Command	Validate the Omnidex Environment's view of the underlying data.
PARTITION	Statement	Partition a table into smaller sub tables based on criteria.
UPDATE ROLLUP	Statement	Update Rollup Table with summarized information.
UPDATE ROLLUPS	Statement	Update all Rollup Tables for an Omnidex Database.
UPDATE STATISTICS	Statement	Update Statistics Information.
UPDATE TEXT	Statement	Update Textual Statistics.
LOAD OFX	Statement	Load Indexes for a table.

OdxSQL and Omnidex SQL Data Export and Manipulation

OdxSQL has commands that can be used to export and partition data. These can be used to easily pull data from RDBMS data stores into Omnidex Snapshots.

PARTITION	Statement	Partition a table into smaller sub tables based on criteria.
CONVERT	Command	Export a file with data conversion.
EXPORT	Statement	Export rows to a file.
EXTRACT	Command	Extracts SQL Statements from a table and writes them to a file.

OdxSQL Program Operation Commands

OdxSQL has several commands that facilitate the processing, editing, re-issue of previous commands and processing multiple command blocks.

USE	Read and process a file of OdxSQL Commands and Omnidex SQL Statements, optionally with WHERE criteria.
Redo Command ( . )	Execute the last command.
HISTORY	Show the history of OdxSQL Commands and Statements.
n	Execute the numbered OdxSQL History Command or Statement.
EDIT	Edit the current command, a previous command or file using Notepad, vi or a named editor.
SAVE HISTORY	Save the OdxSQL Command History to a file.
RESET HISTORY	Resets the OdxSQL History of Commands and Statements.
SET	Set an OdxSQL setting.
SAVE SETTINGS	Save OdxSQL Settings to a file for use with a later RESTORE SETTINGS Command.
RESTORE SETTINGS	Restore Settings, optionally from a file that was created with a previous SAVE SETTINGS command.
FOR	Execute the next one or more commands the specified number of times.
IF	Form an IF ELIF ELSE ENDIF Construct.
HELP	Show a list of commands help on individual commands, COMMANDS, SYNTAX and API.
IS	Check Table Conditions.
EVAL	Evaluate a condition.
DATE	Show the current Date and Time.
CALC	Perform basic calculator functions.
EXIT	Exit the OdxSQL program.
QUIT	Quit the OdxSQL program. Same as Exit.
Comment ( ; )	Use a semicolon as the first character of a line for a comment line.
VERSION	Display current Omnidex Software versions.

OdxSQL Testing and Performance Commands

OdxSQL contains several commands that facilitate the testing and optimization of an Omnidex Environment.

EXPLAIN	Explain the optimization approach to a previous retrieval.
VALIDATE	Validate the Omnidex Environment's view of the underlying data.
RESET TIMER	Reset the OdxSQL Cummulative Timer.
BENCHMARK	Execute Benchmarks to analyze performance.
EXTRACT	Extracts SQL Statements from a table and writes them to a file.
FORMAT	Format a file of Omnidex SQL Statements.
n:	Send the numbered OdxSQL History Command to a son process.
ERROR	Display the text of an error message from the Omnidex Error Message file.
MESSAGE	Display an Omnidex Environment Message.
EXECSQL
NETWORKACCESS	Execute an Omndiex Network Access Command.
SLEEP	Sleep for a specified number of seconds.
ABORT	Issues an Operating System Abort - used to test abnormal conditions.
SIGNAL	Sends a Signal to a process.
SLEEP	Sleep for a specified number of seconds.
DUMP	Dump files, memory or caches.
OXKERNEL	Execute an Omnidex Kernel Command. Should be used for Debugging with DISC Personnel.
PARSE	Parse an Omnidex SQL Statement to a file. This would only be used by DISC Personnel.
SORTMERGE	Internal: Activates the Sort/Merge Test System.

OdxSQL Operating System Commands

OdxSQL contains several commands that access the underlying operating to facilitate the Omnidex Administrator or Developer staying inside of OdxSQL for many tasks.

! <shell command>	Run an OS command in the OS Shell and return or just an ( ! ) for an interactive shell.
COMMAND	Execute a shell command via the $COMMAND table.
CD Change Directories	Change Directories in the underlying Operating System.
CREATE DIRECTORY	Create a directory in the underlying Operating System.
PWD	Print (display) the current working directory from the underlying Operating System.
DIR	Display a directory of files from the current directory in the underlying Operating System.
EDIT	Edit a previous command or file by running Notepad or vi or named editor.
COPY	Copy a file or fileset.
CREATE FILE	Create a File in the underlying Operating System.
REMOVE	Remove (delete or purge) one or more files or directories from the underlying Operating System.
RENAME	Rename a file or fileset in the underlying Operating System.
VIEW	View a text file.
SETENV	Set an Operating System Environment Variable.
GETENV	Display an Operating System Environment Variable.
ABORT	Issues an Operating System Abort - used to test abnormal conditions.

Omnidex Index Direct Maintenance Routines

INSERTINDEX	Insert values directly into the Omnidex Indexes.
UPDATEINDEX	Update values directly in the Omnidex Indexes.
DELETEINDEX	Delete values directly from the Omnidex Indexes.

Alphabetical List of OdxSQL Commands and Omnidex SQL Statements

The following is a complete list of all OdxSQL Commands and all Omnidex SQL Statements that can be entered through OdxSQL.

Command	Description
! <shell command>	Command	Run the command in the OS Shell and return or ! for interactive shell
. Redo Command	Command	Execute the last command.
n	Command	Execute the numbered OdxSQL History Command or Statement.
ATTACH	Statement	Attach an OST file as a table to the currently connected Omnidex Environment.
BENCHMARK	Command	Execute Benchmarks to analyze performance.
CALC	Command	Perform basic calculator functions.
CD	Command	Change Directories in the underlying Operating System.
CLOSE CURSOR	Statement	Close a previously opened cursor.
CONNECT	Both	Connect to an Omnidex Environment or ODBC/JDBC Datasource File (DSN).
CONVERT	Command	Export a file with data conversion.
Comment ( ; )	Command	Use a semicolon as the first character of a line for a comment line.
COPY	Command	Copy a fileset.
CREATE DATABASE	Statement	(V5.1) Create ad Database within an Omnidex Environment.
CREATE DIRECTORY	Statement	Create a directory in the underlying Operating System.
CREATE ENVIRONMENT	Statement	(V5.1) Create an Omnidex Environment.
CREATE FILE	Command	Create a File in the underlying Operating System.
CREATE INDEX	Statement	(V5.1 Create Index
CREATE INDEXES	Statement	Create Omnidex Indexes.
CREATE TABLE	Statement	(V5.1) Create a Table.
DATE	Command	Show the current Date and Time.
DELETE	Statement	Issue the SQL DELETE Statement.
DELETEINDEX	Statement	Delete values directly from the Omnidex Indexes.
DETACH	Statement	Detach a previously attached Omnidex Standalone Table (OST) file.
DIR	Statement	Display a directory of files from the current directory in the underlying Operating System.
DISCONNECT	Statement	Disconnect from a previously Connected Omnidex Environment or ODBC or JDBC Datasource (DSN).
DROP DATABASE	Statement	Drop Database(V5.1)
DROP ENVIRONMENT	Statement	Drop Environment. (V5.1)
DROP INDEX	Statement	Drop Index. (V5.1)
DROP INDEXES	Statement	Drop Indexes.
DROP TABLE	Statement	Drop a previously created temporary table. Expaned in (V5.1)
EDIT	Command	Edit a previous command or file.
ERROR	Command	Display the text of an error message from the Omnidex Error Message file.
EXECSQL	Command	Execute an Omnidex SQL Statement directly with the oaexecsql routine without OdxSQL doing any processing.
EXIT	Command	Exit the OdxSQL program.
EXPLAIN	Command	Explain the optimization approach to a previous retrieval.
EXPORT	Statement	Export rows to a file.
EXTRACT	Command	Extracts SQL Statements from a table and writes them to a file.
FETCH	Command	Explicitly fetch data from the database.
FETCHKEYS	Command	Explicitly fetch keys from the Omnidex Indexes.
FOR	Command	Execute the next one or more commands the specified number of times.
FORMAT	Command	Format a file of Omnidex SQL Statements.
GETENV	Command	Display an Operating System Environment Variable.
HELP	Command	Help on COMMANDS, SYNTAX and API.
HISTORY	Command	Show the history of OdxSQL Commands and Statements.
IF	Command	Form an IF ELIF ELSE ENDIF Construct.
INSERT	Command	Issue an Omnidex SQL INSERT Statement.
INSERTINDEX	Statement	Insert values directly into the Omnidex Indexes.
JOIN	Statement	Join tables using Omnidex Indexes.
LOAD OFX	Statement	Load Indexes for a table.
MESSAGE	Command	Display an Omnidex Environment Message.
OPEN CURSOR	Statement	Open a Cursor.
PARTITION	Statement	Partition a table into smaller sub tables based on criteria.
PWD	Command	Print (display) the current working directory from the underlying Operating System.
QUALIFY	Statement	Qualify rows using Omnidex criteria.
QUIT	Command	Quit the OdxSQL program. Same as Exit.
REMOVE	Command	Remove (delete or purge) one or more files or directories from the underlying Operating System.
RENAME	Command	Rename a file or fileset in the underlying Operating System.
RESET HISTORY	Command	Resets the OdxSQL History of Commands and Statements.
RESET TIMER	Command	Reset the OdxSQL Cummulative Timer.
RESTORE SETTINGS	Command	Restore Settings, optionally from a file that was created with a previous SAVE SETTINGS command.
SAVE HISTORY	Command	Save the OdxSQL Command History to a file.
SAVE SETTINGS	Command	Save OdxSQL Settings to a file for use with a later RESTORE SETTINGS Command.
SELECT	Statement	Issue an Omnidex SQL SELECT Statement.
SET	Both	Set an OdxSQL setting.
SETENV	Command	Set an Operating System Environment Variable.
SHOW	Command	Show Omnidex Environment information including Databases, Tables, Column layouts, Query Statistics, etc.
SIGNAL	Command	Sends a Signal to a process.
SLEEP	Command	Sleep for a specified number of seconds.
UPDATE	Statement	Issue an Omnidex SQL UPDATE Statement.
UPDATE INDEXES	Statement	Update Omnidex Indexes
UPDATE ROLLUP	Statement	Update Rollup Table with summarized information.
UPDATE ROLLUPS	Statement	Update all Rollup Tables for an Omnidex Database.
UPDATE STATISTICS	Statement	Update Statistics Information.
UPDATE TEXT	Statement	Update Textual Statistics.
UPDATEINDEX	Statement	Update values directly in the Omnidex Indexes.
USE	Command	Read and process a file of OdxSQL Commands and Omnidex SQL Statements, optionally with WHERE criteria.
VALIDATE	Command	Validate the Omnidex Environment's view of the underlying data.
VERSION	Both	Display current Omnidex Software versions.
VIEW	Command	View a text file.

Back to top

programs/odxsql/commands/home.1260236620.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes