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/16 06:18] tdo |
programs:odxsql:overview [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ^[[:home|home]] [[overview:nopage | Overview]]^[[nopage|Topics]]^[[nopage | Administrators]] ^ [[nopage | Development]] ^ [[nopage | General]] ^ | + | {{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 ====== | + | 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. |
| - | ===== Overview ===== | + | OdxSQL allows the interactive or script processing of OdxSQL commands and Omnidex SQL Statements. |
| - | 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: | 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. | * 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 16: | Line 19: | ||
| * Exporting data using SELECT statement operations. | * 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. | + | 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 ===== | ===== Program Operation ===== | ||
| Line 22: | Line 25: | ||
| Run OdxSQL from the command line with the following syntax: | Run OdxSQL from the command line with the following syntax: | ||
| - | odxsql [XML Catalog or Environment Catalog] | [-use=use_file] | + | os>odxsql [Omnidex_Environment_filespec] | |
| + | [-help | -? ] | ||
| + | [ -version ] | ||
| + | [ -use=filespec ] | ||
| + | [ -cmd='cmd1;cmd2' ] | ||
| + | [ -where='criteria' ] | ||
| + | [ -exit ] | ||
| + | [ -exit_on_error ] | ||
| + | [ -nobanner ] | ||
| + | [ -cloa ] | ||
| + | [ -odbc ] | ||
| + | [ -jdbc ] | ||
| + | [ -odxnet | ||
| + | [=port] ] | ||
| - | ==== Run Time Arguments ==== | ||
| - | 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 67: | Line 63: | ||
| See HELP in the OdxSQL Commands section for more information. | See HELP in the OdxSQL Commands section for more information. | ||
| - | |||
| ===== Commands ===== | ===== 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 | ||
| - | 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. | + | > EXECSQL VERSION |
| - | ^Command ^ Description ^ | + | OmniAccess version 50002 |
| - | | ! (Exclamation Point) | Execute an operating system command | | + | > |
| - | | . (Period) | Re-Execute the last command | | + | </code> |
| - | | ;(Semi-Colon) | Comment - any text on the line will be ignored | | + | |
| - | | ABORT | Cause OdxSQL to terminate | | + | |
| - | | [[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 FILE | Create an operating system 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 from the operating system | | + | |
| - | | DISCONNECT | * Disconnect from an XML or Environment Catalog | | + | |
| - | | 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 | Set an OdxSQL option | | + | |
| - | | 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 170: | Line 130: | ||
| INSERT INTO CUSTOMERS (COMPANY, CONTACT) VALUES ('New Wave', 'Mr. Smith') ON CURSOR 1 | INSERT INTO CUSTOMERS (COMPANY, CONTACT) VALUES ('New Wave', 'Mr. Smith') ON CURSOR 1 | ||
| ... | ... | ||
| + | ===== OdxSQL Environment Variables ===== | ||
| - | ===== Environment Variables ===== | + | Certain aspects of OdxSQL can be controlled through OS environment variables. |
| - | ODXSQL Environment Variables | + | ^ Environment Variable ^ Description ^ |
| - | The following variables can be set before running ODXSQL. | + | | 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. | | ||
| - | The required variables (above) must also be set prior to running ODXSQL. | + | == ODXSQL_API == |
| - | | ||
| - | |||
| - | **ODXSQL_API** | ||
| ODXSQL_API specifies which API to use, OA (default) or ODBC during this ODXSQL session. | ODXSQL_API specifies which API to use, OA (default) or ODBC during this ODXSQL session. | ||
| Line 185: | Line 145: | ||
| Unix | Unix | ||
| - | export ODXSQL_API="OA" | ||
| - | + | export ODXSQL_API="OA" | |
| Windows | Windows | ||
| - | set ODXSQL_API="ODBC" | ||
| - | + | set ODXSQL_API="ODBC" | |
| + | |||
| + | == ODXSQLHIST == | ||
| - | **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. | 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 | Unix | ||
| - | export ODXSQLHIST="myhistoryfile.txt" | + | |
| + | export ODXSQLHIST="myhistoryfile.txt" | ||
| Windows | Windows | ||
| - | set ODXSQLHIST="myhistoryfile.txt" | + | |
| + | set ODXSQLHIST="myhistoryfile.txt" | ||
| - | + | == ODXSQLINIT == | |
| - | **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. |
| - | 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 | Unix | ||
| - | export ODXSQLINIT="init_file.txt" | + | |
| + | export ODXSQLINIT="init_file.txt" | ||
| Windows | Windows | ||
| - | set ODXSQLINIT="init_file.txt" | + | |
| + | set ODXSQLINIT="init_file.txt" | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | ===== OdxSQL Uses ===== | ||
| + | |||
| + | OdxSQL is a console application for manually entering Omnidex SQL Statements and OdxSQL Commands or for processing scripts of Omnidex SQL statements. | ||
| + | |||
| + | Use OdxSQL for processing Omnidex SQL Data Definition Scripts to create Omnidex Environments and Indexes. | ||
| + | |||
| + | Use OdxSQL to test Omnidex SQL SELECTs and ODBC, JDBC and Network Services set up. | ||
| + | |||
| + | Use OdxSQL to prototype Omnidex Queries and use the Explain Command to review optimization. | ||
| + | |||
| + | Use OdxSQL on a client computer to access an Omnidex Environment on a remote server via OdxNet to test network response times. | ||
| + | |||
| + | |||
| + | ===== Common ways to run OdxSQL ===== | ||
| + | |||
| + | |||
| + | os> odxsql | ||
| + | os> odxsql envfile | ||
| + | os> odxsql dsnfile -odxnet -odbc | ||
| + | os> odxsql -? | ||
| + | os> odxsql -use scriptfile | ||
| + | |||
| + | 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 ===== | ||
| + | |||
| + | * OdxSQL connects to an Omnidex Environment or ODBC or JDBC Omnidex File Datasource (DSN). | ||
| + | * connect myenv.xml | ||
| + | * connect mydsn.dsn using odbc (needs OdxNet running) | ||
| + | * connect mydsn.dsn using jdbc (needs OdxNet running) | ||
| + | |||
| + | * OdxSQL requires a semicolon terminator for SQL Statements and will prompt for additional statement information until a semicolon is found. | ||
| + | * Use SET TERMINATION OFF and OdxSQL will not required the semicolon termination character and will end the statement automatically. | ||
| + | * With SET TERMINATION OFF, use the backslash %%[ \ ]%% statement continuation character for multi line statements. | ||
| + | * Use HELP <cr> to display a list of commands and SQL Statements. | ||
| + | * Use HELP <command> to display the syntax for the command. | ||
| + | |||
| + | |||
| + | * 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. | ||
| + | |||
| + | * Create scripts with an editor and process with the USE <scriptfile> command or the OdxSQL -use scriptfile | ||
| + | |||
| + | * Use EDIT to edit the last statement using Windows Notepad or Linux/Unix vi or emacs. | ||
| + | * Use SET EDIT to change the editor of choice. | ||
| + | * Use EDIT file to edit a script file without leaving OdxSQL and then USE file to run. | ||
| + | * Use EDIT n to edit a command in the Command History Stack. | ||
| + | |||
| + | * 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. | ||
| + | |||
| + | * Use HISTORY to display a numbered list of previous commands. | ||
| + | * Use the history number to re-execute the numbered command. | ||
| + | * Use a period ( . ) to execute the last command. | ||
| + | |||
| + | |||
| + | ===== See Also ===== | ||
| + | |||
| + | * [[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 ]] | ||
| + | |||
| + | ===== OdxSQL Internal SET Commands ===== | ||
| + | |||
| + | <code> | ||
| + | Internal | ||
| + | x [AUTOPSEUDOCOLUMNS <ON | OFF>] | ||
| + | x [AUTORECONNECT <ON | OFF>] | ||
| + | x [AUTOSUBPROCESS <n | NONE> | ||
| + | x [STATISTICS_COMMENT "string"] | ||
| + | x [PREPROCESSOR <ON | OFF>] | ||
| + | x [PROGRESS <ON | OFF>] | ||
| + | x [MEMORY_BASELINE] | ||
| + | x [FILE_BASELINE] | ||
| + | </code> | ||
| + | |||
| + | ===== Level 2 Headline ===== | ||
| + | |||
| + | <code> | ||
| + | SET DEBUG … | ||
| + | SET OPTIMIZATION … | ||
| + | SET REQUIRED_OPTIMIZATION | ||
| + | SET CHAR … | ||
| + | SET TIMEOUT … | ||
| + | SET REDEFROWIDS … | ||
| + | SET PSEUDOCOLUMNS … | ||
| + | SET QUALIFY_MODE <ODXFIND | PARSER > | ||
| + | SET AUTOPSEUDOCOLUMNS … | ||
| + | SET PREPROCESSOR … | ||
| + | SET SQL_LOGGING … | ||
| + | SET SQL_LOGFILE … | ||
| + | </code> | ||
| + | |||
| + | |||
| + | The syntaxes of the SQL Logging SET commands are shown below: | ||
| + | |||
| + | set sql_logging <on | off> | ||
| + | |||
| + | This command activates SQL Logging for this process. By default, statements are logged to the $SQL_STATEMENTS file along with statistics about the execution of the command. The $SQL_STATEMENTS file is named “sqllog.tdf”, and is located in the same directory as the compiled environment file. This file is a standard tab-delimited file, and may be easily imported into other applications. The file may be flushed by simply removing the file; the file will be automatically recreated the next time logging is activated. | ||
| + | |||
| + | set sql_logfile <filename> | ||
| + | |||
| + | This command redirects the SQL logging for this process to a text file that only contains the statement, and does not contain any statistical information. If the file already exists, it will be appended to rather than overwritten. | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| + | |||
| - | ====== Quick Links ====== | + | {{page>:bottom_add&nofooter&noeditbtn}} |
| - | {{page>:quicklinks&nofooter&noeditbtn}} | + | |