This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
programs:odxsql:overview [2009/11/28 22:00] tdo |
programs:odxsql:overview [2012/10/26 14:26] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== OdxSQL ====== | + | {{page>:top_add&nofooter&noeditbtn}} |
| + | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| ===== OdxSQL Overview ===== | ===== 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 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: | OdxSQL allows the interactive specification of Omnidex SQL statements as well as OdxSQL specific commands. OdxSQL is primarily used to: | ||
| Line 8: | Line 13: | ||
| * Test query results from a newly created Omnidex indexes with SELECT statements. | * Test query results from a newly created Omnidex indexes with SELECT statements. | ||
| * Profile performance of queries using the timer functions. | * Profile performance of queries using the timer functions. | ||
| - | * Run Explain Plans to determine usage of Omnidex indexes. | + | * Run Explain Plans to determine the usage of Omnidex indexes. |
| * Perform data utility functions such as partitioning or analyzing data. | * 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 ODBC performance using OdxSQL's ODBC interface either on the server or across the network. | ||
| Line 20: | Line 25: | ||
| Run OdxSQL from the command line with the following syntax: | Run OdxSQL from the command line with the following syntax: | ||
| - | os>odxsql [Omnidex Environment filespec] | | + | os>odxsql [Omnidex_Environment_filespec] | |
| - | [-help | -? ] Help | + | [-help | -? ] |
| - | -version Program version | + | [ -version ] |
| - | -use=filespec Execute statements in named file | + | [ -use=filespec ] |
| - | -cmd='cmd1;cmd2' Execute specified statements | + | [ -cmd='cmd1;cmd2' ] |
| - | -where='criteria' Apply criteria to use statement | + | [ -where='criteria' ] |
| - | -exit Exit after completion of use file | + | [ -exit ] |
| - | -exit_on_error Exit if an error occurs | + | [ -exit_on_error ] |
| - | -nobanner Do not print banner | + | [ -nobanner ] |
| - | -cloa Run odxsql using the CLOA client interface | + | [ -cloa ] |
| - | -odbc Run odxsql using the ODBC client interface | + | [ -odbc ] |
| - | -jdbc Run odxsql using the JDBC client interface | + | [ -jdbc ] |
| - | -odxnet[=port] Launch an OdxNet listener for this session | + | [ -odxnet |
| + | [=port] ] | ||
| - | ==== Run Time Arguments ==== | ||
| - | | -? | Help | | ||
| - | | -help | Help | | ||
| - | | -version | Program version | | ||
| - | | -use=filespec | Execute statements in named file | | ||
| - | | -cmd='cmd1;cmd2' | Execute specified OdxSQl commands | | ||
| - | | -where='criteria' | Apply criteria to use statement | | ||
| - | | -exit | Exit after completion of use file | | ||
| - | | -exit_on_error | Exit if an error occurs | | ||
| - | | -nobanner | Do not print banner | | ||
| - | | -cloa | Run odxsql using the CLOA client interface | | ||
| - | | -odbc | Run odxsql using the ODBC client interface | | ||
| - | | -jdbc | Run odxsql using the JDBC client interface | | ||
| - | | -odxnet[=port] | Launch an OdxNet listener for this session | | ||
| - | |||
| - | 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. | ||
| - | |||
| - | |||
| - | Examples: | ||
| - | |||
| - | Running OdxSQL without command line arguments - use the CONNECT command to connect to an XML or Environment Catalog. | ||
| - | os> odxsql | ||
| - | |||
| - | Running OdxSQL with in Environment Catalog. | ||
| - | os> odxsql orders.env | ||
| - | |||
| - | Running OdxSQL with a command file of OdxSQL commands. | ||
| - | os> odxsql -use=commands.txt | ||
| - | |||
| - | |||
| ==== Getting Help ==== | ==== Getting Help ==== | ||
| Line 93: | Line 65: | ||
| ===== Commands ===== | ===== Commands ===== | ||
| + | ===== Overview ===== | ||
| - | 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. | + | OdxSQL is used to process a wide range of Omnidex SQL Statements and OdxSQL specific commands. |
| - | ^Command ^ Description ^ | + | |
| - | | ! (Exclamation Point) | Execute an operating system command | | + | The OdxSQL Commands and Omnidex SQL Statements are organized for quick review as follows: |
| - | | . (Period) | Re-Execute the last command | | + | |
| - | | ;(Semi-Colon) | Comment - any text on the line will be ignored | | + | * [[#Connecting and Disconnecting ]] |
| - | | ABORT | Cause OdxSQL to terminate | | + | * [[#Omnidex SQL Data Definition Language (DDL) Statements ]] |
| - | | [[programs:odxsql:commands:assignvalues|ASSIGNVALUES]] | * Assign values for a $VALUES clause | | + | * [[#Omnidex SQL Data Manipulation Language (DML) Statements ]] |
| - | | ATTACH | * Attach an OST file as a table | | + | * [[#Omnidex Environment Operation Commands ]] |
| - | | BENCHMARK |Perform performance benchmarks | | + | * [[#OdxSQl and Omnidex SQL Data Export and Manipulation ]] |
| - | | BIND | Bind a SELECT statement | | + | * [[#OdxSQL Program Operation Commands ]] |
| - | | CALC | Perform basic calculator functions | | + | * [[#OdxSQL Testing and Performance Commands ]] |
| - | | CD | Change directories | | + | * [[#OdxSQL Operating System Commands ]] |
| - | | CLOSE CURSOR | * Close a previously opened cursor | | + | * [[#Omnidex Index Direct Maintenance Routines ]] |
| - | | COMMAND | * Execute a shell command via $COMMAND table | | + | * [[#Alphabetical List of OdxSQL Commands and Omnidex SQL Statements ]] |
| - | | CONNECT | * Connect to an XML or an Environment Catalog | | + | |
| - | | CONVERT | Export a file with data conversions | | + | OdxSQL Commands and Omnidex SQL Statements can be entered into OdxSQL in ether upper or lower case. |
| - | | COPY | Copy a fileset | | + | |
| - | | CREATE DIRECTORY | * Create an operating system directory | | + | > connect myenv |
| - | | CREATE DATABASE | * Create a database within an Omnidex Environment | | + | |
| - | | CREATE ENVIRONMENT | * Create an Omnidex Environment | | + | > CONNECT myenv |
| - | | CREATE FILE | * Create an operating system file | | + | |
| - | | CREATE INDEX | * Create an index or a column or group of columsn | | + | 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. |
| - | | CREATE TABLE | * Create a table | | + | |
| - | | DATE | Show the current date and time | | + | There are a few exceptions such as CONNECT and VERSION. |
| - | | DELETE | * Issue an SQL delete statement | | + | |
| - | | DELETEINDEX |* Delete values directly from Omnidex indexes | | + | 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 |
| - | | DESCRIBE | Describe a SELECT statement | | + | |
| - | | DETACH |* Detach a previously attached OST file | | + | 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. |
| - | | DIR | Display a directory of files from the operating system | | + | |
| - | | DISCONNECT | * Disconnect from an XML or Environment Catalog | | + | 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. |
| - | | DROP DATABASE | | | + | |
| - | | DROP ENVIRONMENT | | | + | <code> |
| - | | DROP INDEX | | | + | > VERSION |
| - | | DROP TABLE | | | + | ODXSQL - Omnidex SQL Wed Dec 02 09:20:41 200 |
| - | | DROP | * Drop a table | | + | ... |
| - | | DUMP | Dump files, memory or caches | | + | Omnidex Version 5.0 Build 2B |
| - | | EDIT | Edit a previous command or file | | + | Compilation Timestamp Oct 13 2009 08:57:09 |
| - | | ERROR | Display an error message | | + | Earliest Compatible ODBC Client 4.3 Build 4B |
| - | | EVAL | Evaluate a condition | | + | Earliest Compatible JDBC Client 4.3 Build 4B |
| - | | EXECDB | Execute an RDBMS statement using the execdb() | | + | Earliest Compatible Odx Kernel 5.0 Build 1B |
| - | | EXIT | Exit Omnidex SQL | | + | |
| - | | EXPLAIN | Shows an Explain Plan on the retrieval | | + | > EXECSQL VERSION |
| - | | EXPORT | * Export records using SELECT statement syntax to a file | | + | OmniAccess version 50002 |
| - | | EXTRACT | Extract SQL statements from database | | + | > |
| - | | FETCH | Explicitly fetch data from a database | | + | </code> |
| - | | 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 ===== | ===== Settings ===== | ||
| Line 246: | Line 175: | ||
| set ODXSQLINIT="init_file.txt" | set ODXSQLINIT="init_file.txt" | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | {{page>:bottom_add&nofooter&noeditbtn}} | ||