Show page
Old revisions

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
programs:odxsql:home [2009/11/30 22:52]
els created 		programs:odxsql:home [2012/10/26 14:26] (current)
Line 1: Line 1:
-====== OdxSQL ====== +{{page>:​top_add&​nofooter&​noeditbtn}} 
-===== OdxSQL ​Overview ​===== +<​html><​div align="​center"><​span style="​color:​red">​DRAFT</​span></​div></​html>​ 
-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.+~~NOTOC~~ 
 +====== OdxSQL ​Quick Reference ​====== 
 +{{page>:​odxsql_bar&​nofooter&​noeditbtn}} 
 +===== OdxSQL ​Uses =====
  
-OdxSQL ​allows the interactive ​or script ​processing of OdxSQL commands and Omnidex SQL Statements.+OdxSQL ​is a console application for manually entering Omnidex SQL Statements and OdxSQL Commands ​or for processing ​scripts ​of Omnidex SQL statements.
  
-OdxSQL ​allows the interactive specification of Omnidex SQL statements as well as OdxSQL specific commands.  ​OdxSQL is primarily used to:+Use OdxSQL ​for processing ​Omnidex SQL Data Definition Scripts to create Omnidex Environments and Indexes.
  
-     * Create and maintain Omnidex Environments. +Use OdxSQL ​to test Omnidex ​SQL SELECTs and ODBC, JDBC and Network Services set up.
-     * 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.+Use OdxSQL to prototype ​Omnidex ​Queries and use the Explain Command to review optimization.
  
-===== Program Operation ===== +Use OdxSQL ​on a client computer to access an Omnidex Environment on a remote server via OdxNet to test network response times.
- +
-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] ]+
  
    
  
 +===== Common ways to run OdxSQL =====
  
-==== Getting Help ==== 
-Type help at the command prompt with no arguments to see an alphabetical list of ODXSQL commands. 
  
-     >help +  os> odxsql 
-...+  ​os>​ odxsql envfile 
 +  os> odxsql dsnfile -odxnet -odbc 
 +  os> odxsql -? 
 +  os> odxsql -use scriptfile
  
-Type help with the argument ​'syntax' ​at the command ​prompt to see a list of commands with their syntax.+Running OdxSQL Explained:  
 +| odxsql | Run OdxSQL. ​ Use the CONNECT command to connect to an Omnidex Environment | 
 +| odxsql envfile | Run OdxSQL and connect to the 'envfile' ​| 
 +| odxsql dsnfile -odxnet -odbc | Run OdxSQL and an OdxNET background process and connect to the DSN using ODBC | 
 +| odxsql -? | Displays OdxSQL ​command ​line options | 
 +| odxsql -use scriptfile | Run OdxSQL and read and process the scriptfile ​of OdxSQL Statements and Commands.| 
 +===== OdxSQL Important Information =====
  
-     >​help syntax +  * OdxSQL connects to an Omnidex Environment or ODBC or JDBC Omnidex File Datasource (DSN). 
-...+    * connect myenv.env  
 +    * connect mydsn.dsn using odbc  (needs OdxNet running) 
 +    * connect mydsn.dsn using jdbc  (needs OdxNet running)
  
-Type help followed by a specific command ​to see additional information about that command.+  * OdxSQL automatically terminates commands and SQL Statements with a return <​cr>​. 
 +    * Use the backslash %%[ \ ]%% statement continuation character for multi line statements.  
 +    * Use SET CONTINUATION OFF to use the semicolon character as the statement terminator.
  
-     >help connect +  * Use HELP <cr> to display a list of commands and SQL Statements. 
-     CONNECT +    * Use HELP <​command> ​to display the syntax for the command.
-     ​Connect ​to an environment file +
-     ​Syntax:​ +
-     ​Connect [TO] filename [AS [user]/​[password]] [WITH options]+
  
-See HELP in the OdxSQL ​Commands section for more information. +  * Use SET <cr> to show a list of the current ​OdxSQL ​settings. 
-===== Commands =====+    * Use SET ? to get a list of OdxSQL Set options.
  
 +  * Use SHOW <​table>​ to show information on a table with the tables'​s column layout.
 +    * Use SHOW ALL to see all tables, including internal system tables.
  
-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. +  * Create scripts ​with an editor ​and process with the USE <​scriptfile> ​command ​or the OdxSQL ​-use scriptfile 
-^Command ^ Description ^ +     
-| ! (Exclamation Point) | Execute an operating system command | +  * Use EDIT to edit the last statement ​using Windows Notepad ​or Linux/Unix vi or emacs. 
-| . (Period) ​           | Re-Execute ​the last command ​| +    * Use SET EDIT to change ​the editor of choice. 
-| ;​(Semi-Colon) ​        | Comment - any text on the line will be ignored | +    * Use EDIT file to edit a script ​file without leaving ​OdxSQL and then USE file to run. 
-| ABORT                 | Cause OdxSQL ​to terminate | +    * Use EDIT n to edit a command ​in the Command ​History ​Stack.
-| [[programs:​odxsql:​commands:​assignvalues|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 previously opened cursor | +
-| COMMAND ​              | * Execute a shell command via $COMMAND table | +
-| CONNECT ​              | * Connect ​to an XML or an Environment Catalog | +
-| CONVERT ​              | Export a file with data conversions | +
-| COPY                  | Copy a fileset | +
-| CREATE DIRECTORY ​     | * Create an operating system directory | +
-| CREATE DATABASE ​      | * Create a database within an Omnidex Environment | +
-| CREATE ENVIRONMENT ​   | * Create an Omnidex Environment | +
-| CREATE FILE           | * Create an operating system file | +
-| CREATE INDEX          | * Create an index or a column or group of columsn | +
-| 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 from the operating system | +
-| DISCONNECT ​           | * Disconnect from an XML or Environment Catalog | +
-| DROP DATABASE | | +
-| DROP ENVIRONMENT | | +
-| DROP INDEX | | +
-| DROP TABLE | | +
-| 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 an RDBMS statement using the execdb() | +
-| EXIT                  | Exit Omnidex SQL | +
-| EXPLAIN ​              | Shows an Explain Plan on the retrieval | +
-| EXPORT ​               | * Export records using SELECT statement syntax ​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 bindary 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 to insert data | +
-| 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 SQL statement | +
-| PARTITION ​            | Partition a table | +
-| PWD                   | Print or show the current working directory | +
-| QUALIFY ​              |* Qualify rows using the Omnidex QUALIFY statement| +
-| QUIT                  | Quit Omnidex OdxSQL | +
-| REMOVE ​               | Remove one or more files from the operating system | +
-| RENAME ​               | Rename a file or fileset | +
-| RESET HISTORY ​        | Reset history of ODXSQL commands | +
-| RESET TIMER           | Reset the cumulative timer used to verify performance of queries | +
-| RESTORE SETTINGS ​     | Restore settings, optionally from a file | +
-| SAVE HISTORY ​         | Save previously entered ​OdxSQL ​commands to a file | +
-| SAVE SETTINGS ​        | Save settings, optionally to a file | +
-| SELECT ​               | * Issue an Omnidex SQL SELECT statement | +
-| SET command ​          | Set an OdxSQL option | +
-| SET statement ​        | Set and Omnidex SQL setting | +
-| SETENV ​               | Set an operating system environment variable | +
-| SHOW                  | Show information on databases, tables, and columns | +
-| SIGNAL ​               | Send an operating system signal to a process | +
-| SORTMERGE ​            | Sort/merge test system | +
-| UPDATE ​               | * Issue an Omnidex SQL UPDATE statement | +
-| UPDATE STATISTICS ​    | Update statistics for a database or table | +
-| UPDATE TEXT           | Enable misspelling searches on Text, FullText or MDK indexed columns. | +
-| UPDATEINDEX ​          | * Update values directly in Omnidex indexes | +
-| USE                   | Execute a file of OdxSQL commands | +
-| VALIDATE ​             | Validate an Omnidex Environment ​to determine correct mapping to an underlying database or file and verify index retrievals are working |  +
-| VERSION ​              ​| ​* Display current software version information | +
-| VIEW                  | View an external text file | +
-| n                     | Execute numbered ​command ​from the OdxSQL ​History ​stack | +
-| n:                    | Send command to OdxSQL subprocess identified by relative number of the process |+
  
-===== Settings =====+  * Edit previous commands by using the UP ARROW and scrolling through the commands. 
 +    * Use the Right and Left Arrow, Insert, Delete, Home, and End keys to edit.
  
-^ Setting ^ Option ^ Description ^ +  * Use HISTORY ​to display ​a numbered list of previous ​commands. 
-| CURSORDISPLAY | ON/OFF | | +    * Use the history number ​to re-execute ​the numbered command. 
- +    ​* ​Use a period ( . ) to execute the last command. 
-  >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"​+===== See Also =====
  
-Windows+    * [[programs:​odxsql:​overview | OdxSQL Overview ]] 
 +    * [[programs:​odxsql:​commands:​home |OdxSQL Commands ]] 
 +    * [[programs:​odxsql:​settings:​home |OdxSQL Settings ]] 
 +    * [[programs:​odxsql:​commandline |OdxSQL Command Line Options ]] 
 +    * OdxSQL Script Examples 
 +    * [[ dev:​sql:​statements:​select:​home | SQL SELECT ]] 
 +    * SQL SELECT Examples 
 +    * [[ dev:​sql:​statements:​home | Omnidex SQL Statements ]]
  
-  set ODXSQLINIT="​init_file.txt"​+{{page>:​bottom_add&​nofooter&​noeditbtn}}
 
Back to top
programs/odxsql/home.1259621541.txt.gz · Last modified: 2012/10/26 14:25 (external edit)
Sitemap Recent Changes