This shows you the differences between two versions of the page.
|
programs:odxsql:overview [2009/12/08 02:57] tdo |
programs:odxsql:overview [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| - | ===== OdxSQL Overview ===== | ||
| - | {{page>:odxsql_bar&nofooter&noeditbtn}} | ||
| - | OdxSQL is a console program used for creating and maintaining Omnidex environments, 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 or script processing of OdxSQL commands and Omnidex SQL Statements. | ||
| - | |||
| - | OdxSQL allows the interactive specification of Omnidex SQL statements as well as OdxSQL specific commands. OdxSQL is primarily used to: | ||
| - | |||
| - | * Create and maintain Omnidex Environments. | ||
| - | * 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 the 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 Environment which can either be specified as a run time argument or via the CONNECT command once OdxSQL is running. | ||
| - | |||
| - | ===== Program Operation ===== | ||
| - | |||
| - | Run OdxSQL from the command line with the following syntax: | ||
| - | |||
| - | os>odxsql [Omnidex_Environment_filespec] | | ||
| - | [-help | -? ] | ||
| - | [ -version ] | ||
| - | [ -use=filespec ] | ||
| - | [ -cmd='cmd1;cmd2' ] | ||
| - | [ -where='criteria' ] | ||
| - | [ -exit ] | ||
| - | [ -exit_on_error ] | ||
| - | [ -nobanner ] | ||
| - | [ -cloa ] | ||
| - | [ -odbc ] | ||
| - | [ -jdbc ] | ||
| - | [ -odxnet | ||
| - | [=port] ] | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | ==== Getting Help ==== | ||
| - | 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 ===== | ||
| - | |||
| - | ===== 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. | ||
| - | |||
| - | <code> | ||
| - | > 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 | ||
| - | > | ||
| - | </code> | ||
| - | |||
| - | ===== Settings ===== | ||
| - | |||
| - | ^ Setting ^ Option ^ Description ^ | ||
| - | | CURSORDISPLAY | ON/OFF | | | ||
| - | |||
| - | >SET CURSORDISPLAY ON | ||
| - | |||
| - | >connect /users/garment.env | ||
| - | Connected to /users/garment.env on instance 1, cursor 1 | ||
| - | |||
| - | >connect / users/orders.env | ||
| - | Connected to /users/orders.env on instance 2, cursor 2 | ||
| - | |||
| - | SELECT COMPANY, CONTACT, PHONE FROM CUSTOMERS WHERE STATE='CO' ON CURSOR 2 | ||
| - | ... | ||
| - | INSERT INTO CUSTOMERS (COMPANY, CONTACT) VALUES ('New Wave', 'Mr. Smith') ON CURSOR 1 | ||
| - | ... | ||
| - | ===== OdxSQL Environment Variables ===== | ||
| - | |||
| - | Certain aspects of OdxSQL can be controlled through OS environment variables. | ||
| - | ^ Environment Variable ^ Description ^ | ||
| - | | ODXSQL_API | Specifies which API to use: OA (default), ODBC or JDBC. | | ||
| - | | OSXSQLHIST | Specifies a different history file for saving OdxSQL history. | | ||
| - | | ODXSQLINIT | Specifies a text file of OdxSQL initialization commands. | | ||
| - | |||
| - | == ODXSQL_API == | ||
| - | |||
| - | ODXSQL_API specifies which API to use, OA (default) or ODBC during this ODXSQL session. | ||
| - | |||
| - | If running ODXSQL on a client machine where the Omnidex server software has not been installed, this variable MUST be set to ODBC, since the OA API does not exist on the client. | ||
| - | |||
| - | Unix | ||
| - | |||
| - | export ODXSQL_API="OA" | ||
| - | |||
| - | Windows | ||
| - | |||
| - | set ODXSQL_API="ODBC" | ||
| - | |||
| - | == ODXSQLHIST == | ||
| - | |||
| - | ODXSQL_HIST_FILE points to an ODXSQL History File. By default, the ODXSQL command history is written to .odxsql_history. Use this variable to write the history to a different file. | ||
| - | |||
| - | Unix | ||
| - | |||
| - | export ODXSQLHIST="myhistoryfile.txt" | ||
| - | |||
| - | Windows | ||
| - | | ||
| - | set ODXSQLHIST="myhistoryfile.txt" | ||
| - | |||
| - | == ODXSQLINIT == | ||
| - | |||
| - | ODXSQLINIT points to an OdxSQL Init File. This file can contain initialization settings that will be set when OdxSQL starts. This is very useful for set options. | ||
| - | |||
| - | Unix | ||
| - | |||
| - | export ODXSQLINIT="init_file.txt" | ||
| - | |||
| - | Windows | ||
| - | |||
| - | set ODXSQLINIT="init_file.txt" | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||