Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
dev:sql:statements:create_environment:home [2010/06/16 20:33]
tdo
dev:sql:statements:create_environment:home [2016/06/28 22:38] (current)
Line 1: Line 1:
 {{page>:​top_add&​nofooter&​noeditbtn}} {{page>:​top_add&​nofooter&​noeditbtn}}
-<​html><​div align="​center"><​span style="​color:red">​DRAFT</​span></​div></​html>​+^ Omnidex SQL Quicklinks ^^^^^ 
 +|[[dev:sql:​overview | Overview ]] | [[dev:​sql:​statements:​home | Statements ]] | [[ dev:​sql:​functions:​home | Functions ]] | [[ dev:​sql:​examples:​home | Examples ]] | [[dev:​sql:​home | Quick Reference ]] |
 ====== Omnidex SQL: CREATE ENVIRONMENT ====== ====== Omnidex SQL: CREATE ENVIRONMENT ======
-{{page>:​sql_bar&​nofooter&​noeditbtn}} 
 ===== Description ===== ===== Description =====
-The CREATE ENVIRONMENT statement creates an Omnidex Environment File stored in an operating system file in XML format.  This file contains Omnidex'​s view of a collection of data used for indexing and retrieval. ​+The CREATE ENVIRONMENT statement creates an Omnidex Environment File stored in an XML file.  This file contains Omnidex'​s view of the data to be used for indexing and retrieval. The Omnidex Environment File is then specified in the CONNECT statement to establish a connection to an Omnidex Environment from an ODBC or JDBC application.
  
-An Omnidex Environment may be a complete or partial mapping to one or more relational data bases or a mapping to one or more raw data files. ​ The CREATE ENVIRONMENT statement is used in conjunction with the CREATE DATABASE, the CREATE TABLE and the CREATE INDEX statements to specify the meta-data for an Omnidex Environment. +An Omnidex Environment ​File may contain ​a complete or partial mapping to one or more relational data bases or a mapping to one or more raw data files.  When using raw data files, several raw data files can be described just like a database, with datatypes, table relationships and indexing.  The CREATE ENVIRONMENT statement is used in conjunction with the CREATE DATABASE, the CREATE TABLE and the CREATE INDEX statements to specify the entire ​meta-data for an Omnidex Environment.
- +
-The CREATE ENVIRONMENT statement also has optonal clauses to configure global settings, as well as clauses to declare nodes in an Omnidex Grid.   +
- +
-The CREATE ENVIRONMENT statement is used to specify Omnidex Grid nodes for splitting data files into smaller partitions to facilitate query processing performance. +
- +
-Nodes are named in the CREATE ENVIRONMENT statement and then referenced by name in the CREATE DATABASE and CREATE TABLE statements. +
- +
- +
  
 +The CREATE ENVIRONMENT statement has optional clauses to configure global settings. ​ The CREATE ENVIRONMENT statement also is used to specify Omnidex Grid nodes for splitting data files into smaller partitions to facilitate query processing performance. ​ These nodes are then referenced by name in the CREATE DATABASE and CREATE TABLE statements.
 ===== Syntax ===== ===== Syntax =====
   CREATE ENVIRONMENT ​   CREATE ENVIRONMENT ​
Line 22: Line 15:
       [ NODE node_name ​       [ NODE node_name ​
           [ <​PARTITIONED | UNPARTITIONED>​ ]           [ <​PARTITIONED | UNPARTITIONED>​ ]
-          [ HOST <​LOCALHOST | host_name | host_ip_address> ​  +          [ CONNECTIONSTRING "filespec" ​] ] 
-              [ PORT port_number ] ] +       [ SQL_SYNTAX < OMNIDEX | ORACLE | SQLSERVER ​> ] 
-              [ ENVIRONMENT “filespec” ] ] +       [ CENTURY_THRESHOLD ​year ]
-       [ SQL_SYNTAX < ORACLE | OMNIDEX ​> ] +
-       [ CENTURY_THRESHOLD ​int_year ​]+
       IN “filename” ​                            /* must be after all previous clauses */       IN “filename” ​                            /* must be after all previous clauses */
       [WITH options] ​                           /* if used, must be the very last clause ​ */       [WITH options] ​                           /* if used, must be the very last clause ​ */
  
-The CREATE ENVIRONMENT statement ​is used in conjunction with the DROP ENVIRONMENT statement+===== Discussion ===== 
-<​code>​ +The CREATE ENVIRONMENT statement ​can be used in conjunction with the [[dev:​sql:​statements:​drop_environment:​home | DROP ENVIRONMENT]] statement ​but typically the WITH DELETE option ​is used instead of DROP ENVIRONMENT.
-DROP ENVIRONMENT [IF EXISTS]  +
-     IN “filename” +
-     [WITH options]  +
-</​code>​ +
-==== IN "​filespec"​ ==== +
- +
-The IN clause is required and is used to specify the physical XML Omnidex Environment file specification that will contain the metadata for this Omnidex Enviroment +
  
-The IN clause must be specified after all other clauses except the WITH clause.+  create environment in "odx.xml" with delete;
  
-The filespec can be specified either as an absolute or a relative file specification. 
  
-The IN filespec must be enclosed within double quotes.+=== DATA_CACHE number_of_megabytes === 
 +The DATA_CACHE clause declares the number of megabytes for Omnidex'​s internal hash tables used for high-speed table joins. ​ The default is 32 megabytes and can be changed by specifying a numeric value representing the number of megabytes of memory. A value of NONE can also be used to cause Omnidex to not use any internal cache.
  
-  CREATE ENVIRONMENT IN "​odx.xml"; ​               /* relative filespec for either Linux or Widnows */ 
-  CREATE ENVIRONMENT IN "​c:​\dev\odx.xml"; ​        /* Windows absolute filespec */ 
-  create environment in "/​usr/​opt/​dev/​odx.xml"; ​  /* Unix/Linus absolute filespec */ 
-==== DATA_CACHE number_of_megabytes ==== 
-The DATA_CACHE setting optionally sets the number of megabytes for Omnidex'​s internal hash tables used for internal join processing. ​ 
- 
-The default is 32 megabytes and can be changed by specifying a numeric value from 1 which represents 1 megabyte to the physical size of memory. 
- 
-A value of NONE can also be used to cause Omnidex to not use any internal cache. 
 <​code>​ <​code>​
 create environment ​ create environment ​
Line 60: Line 35:
   in "​sales.xml";​   in "​sales.xml";​
 </​code>​ </​code>​
 +=== MAX_THREADS thread_count ===
 +The MAX_THREADS setting optionally specifies the number of concurrent nodes that will be being processed concurrently during an Omnidex query. ​ Typically this value is set to one to two less than the number of processors. ​ For example, on an eight processor system, a setting of MAX_THREADS 6 or MAX_THREADS 7 would be appropriate. The default is the number of nodes specified in CREATE ENVIRONMENT NODE clauses.
  
-==== MAX_THREADS thread_count ​==== +<​code>​ 
-The MAX_THREADS setting optionally specifies the number of concurrent nodes that will be being processed concurrently during an Omnidex query. ​ Typically this value is set to one to two less than the number of processors.+create environment  
 +  max_threads 7 
 +  in "​sales.xml";​ 
 +</​code>​ 
 +=== NODE node_name ​===
  
-So for an eight processor system, ​setting of MAX_THREADS 6 or MAX_THREADS 7 could be used.+The NODE clause declares ​node in an Omnidex Grid.  Nodes can then be referenced in subsequent CREATE DATABASE and CREATE TABLE statements 
  
-The default is the number of nodes specified in CREATE ENVIRONMENT NODE clauses. +The //​node_name//​ uniquely identifies ​the node within this environment It may be up to 32 characters long, and can contain letters, digits and the following special characters: ! @ # $ % ^ _.  It must begin with letter and is case-insensitive. ​ The //node_name// must  ​be unique within the environment 
-==== NODE node_name ==== +
-The NODE clause is used to specify ​logical ​node_name ​for an Omnidex data node to be used by CREATE DATABASE and CREATE TABLE statements.+
  
-Further the NODE clause specifies partitioning information and connection information to remote servers for a particular node.+== <​PARTITIONED | UNPARTITIONED>​ ==
  
-Nodes are used to specify ​Omnidex ​Grid information which facilitates retrieval performance ​by splitting large tables or files into smaller units for retrieval.+Nodes are declared as either partitioned or unpartitioned.  ​Omnidex ​Grids are initially created ​by partitioning a database ​into nodes. ​ Each partitioned node contains a portion of the database, and the entire database then consists of the collection of partitioned nodes. ​ An Omnidex Grid may also have unpartitioned nodes. An unpartitioned node is a complete copy of the database. ​ Some queries will perform better when they are processed against a single node containing the entire database, rather than across many nodes containing individual partitions  ​
  
 <​code>​ <​code>​
 create environment ​         ​ create environment ​         ​
-  data_cache ​                 32 
-  max_threads ​                2 
   node                        "​GRID01"​ partitioned   node                        "​GRID01"​ partitioned
   node                        "​GRID02"​ partitioned   node                        "​GRID02"​ partitioned
Line 85: Line 62:
   in                          "​list.xml";​   in                          "​list.xml";​
 </​code>​ </​code>​
-=== PARTITIONED | UNPARTITIONED === 
-Nodes are either PARTITIONED or UNPARTITIONED. ​ An UNPARTITIONED partition contains the full table or files.  ​ 
  
-An UNPARTITIONED node is used when it is desirable to create a node which contains the entire set of data.+== HOST < host_name | ip_address | LOCALHOST > PORT port_no ENVIRONMENT "​filename"​ ==
  
-A node can be partitioned,​ unpartitioned ​or have neither specified.  ​A partitioned node is one where one or more tables has been partitioned. ​ An unpartitioned node is one where no tables have been partitioned,​ meaning that the node contains a complete copy of the database. ​ If the partitioning is not declaredit is presumed to be a partitioned node. +Nodes can reside on the local server ​or can reside on remote servers.  ​Remote nodes require ​the declaration ​of the host name or IP addressan optional port number and the filename of the remote ​Omnidex Environment File.
-=== HOST < LOCALHOST | host_name | host_ip_address > ===  +
-Within ​the NODE clause, ​the HOST specifies either a host_name or host_ip_address for a remote ​server containing this particular NODE If no HOST/PORT information is specified, then this NODE is on the same server as the grid controller.  ​+
  
-LOCALHOST is used to specify the local server and is typically used for testing. 
 <​code>​ <​code>​
 create environment create environment
-  ​data_cache 32 +  node "​grid01"​ partitioned ​   
-  ​node "​grid01"​ partitioned ​  host localhost port 7555 +     host "localhost" ​port 7555 environment "​c:​\omnidex\list.xml"​ 
-  node "​grid02"​ partitioned ​  ​host ​server1 ​port 7555 +  node "​grid02"​ partitioned ​   
-  node GRID03 ​  ​unpartitioned +     host "​SERVER1" ​  port 7555 environment "​c:​\omnidex\list.xml"​ 
-  node grid04 ​                ​host server2 ​port 7555 +  node "​grid03"​ partitioned ​  ​ 
-  node "GRID05" unpartitioned+     host "​SERVER2" ​  port 7555 environment "​c:​\omnidex\list.xml"​ 
 +  node "grid04" partitioned ​   
 +     host "​SERVER2" ​  port 7555 environment "​d:​\omnidex\list.xml"​ 
 +  node "grid05" unpartitioned
   in "​list.xml"​   in "​list.xml"​
   with delete;   with delete;
 </​code>​ </​code>​
-=== PORT port_no ===  
-The PORT setting is the well known port_no that the OdxNet Network Services process uses to establish socket network connections. ​ The default port_no used by OdxNet is 7555.  
-=== ENVIRONMENT environment_filespec === 
-The environment_filespec is a file specification containing the Omnidex Environment File for the particular NODE. 
  
-The environment_filespec can be either relative or an absolute file specification. ​ Note that the relative file specification is relative to where the OdxNet process is running on the remote server so typically absolute file specifications are used.+=== SQL_SYNTAX <OMNIDEX | ORACLE | SQLSERVER > ===
  
-==== SQL_SYNTAX <ORACLE | OMNIDEX > ==== +Omnidex ​supports a limited number of specific ​vendor'​s ​SQL extensions such as some Orace functions and SQL Server'​s bracket syntax to make development easierNote that this support is limited to a subset of each databases'​s extensions and does not encompass ​the complete set of extensions 
-The SQL_SYNTAX setting determines whether ​Omnidex ​will allow Oracle database ​specific ​syntax in an SQL statement This setting can also be set programmicatally using the SET statement.+
  
-  SET SQL_SYNTAX ​ORACLE;+The SQL_SYNTAX ​clause determines whether Omnidex will allow these extensions when processing a SQL statement. This setting can also be set programatically using the Omnidex SET statement. The default for this setting is OMNIDEX, meaning that database extensions are not enabled. ​ Once database extensions are enabled, they can be disabled by reverting back to OMNIDEX.
  
-The default for this setting is OMNIDEX and the Oracle SQL extensions can be turned off programmicatally usng the SET statement.+<​code>​ 
 +create environment  
 +  sql_syntax ORACLE 
 +  in "sales.xml";​ 
 +</​code>​
  
-  SET SQL_SYNTAX OMNIDEX; 
  
-  
  
-==== CENTURY_THRESHOLD year ==== +=== CENTURY_THRESHOLD year === 
-The CENTURY_THRESHOLD setting determines whether a year that is expressed without the century (such as 12/31/80) is recognized as being in the current century (12/​31/​2080) or in the previous century (12/​31/​1980). ​ Years that are later than the century threshold are considered part of the last century while years that are earlier than the century threshold are considered part of the current century.+The CENTURY_THRESHOLD setting determines whether a year that is expressed without the century (such as 12/31/80) is recognized as being in the current century (12/​31/​2080) or in the previous century (12/​31/​1980). ​ Years that are later than the century threshold are considered part of the last century while years that are earlier than the century threshold are considered part of the current century. ​ If no CENTURY_THRESHOLD is specified, then the internal default of 50 is used. 
 +<​code>​ 
 +create environment  
 +  century_threshold 60 
 +  in "​sales.xml";​ 
 +</​code>​ 
 +=== IN "​env_filespec"​ ===
  
-The century threshold specified here has environment scope and is used for all retrievals unless ​the century threshold is specified ​at the column level.+The IN clause is required ​and specifies ​the location of the physical XML Environment File.  The IN clause must be specified ​after all other clauses except ​the WITH clause.
  
-If no CENTURY_THRESHOLD is specifiedthen the internal default of 50 is used.+//​Env_filespec//​ can be either an absolute or relative path.  When using a relative file path it will be relative to the current working directory for the program that is being run. Typically in a production environment,​ absolute paths are used.  When using Omnidex'​s Network Services and the OdxNet process, absolute paths should always be used.
  
-==== WITH options ==== +The //​env_filespec//​ must be enclosed within double quotes.
-=== DELETE === +
-The WITH DELETE option will purge an existing Omnidex Environment along with the underlying physical operating system file so that a new Omnidex Environment file can be created.+
  
-The WITH DELETE option will only delete files that are valid Omnidex Environment ​files.+<​code>​ 
 +  CREATE ENVIRONMENT  
 +    IN "​odx.xml"; ​             /* relative filespec for either Linux or Windows */ 
 + 
 +  CREATE ENVIRONMENT  
 +    IN "​c:​\dev\odx.xml"; ​      /* Windows absolute filespec */ 
 + 
 +  create environment  
 +    in "/​usr/​opt/​dev/​odx.xml";​ /* UNIX/Linus absolute filespec */ 
 +</​code>​ 
 + 
 +=== WITH options === 
 +== DELETE == 
 +The DELETE option removes an existing Omnidex Environment File so that a new Omnidex Environment File can be created. ​ The DELETE option will only delete files that are valid Omnidex Environment ​Files.
  
   CREATE ENVIRONMENT IN "​odx.xml"​ WITH DELETE   CREATE ENVIRONMENT IN "​odx.xml"​ WITH DELETE
Line 141: Line 130:
  
 ===== Examples ===== ===== Examples =====
-==== Simple ​====+==== Minimal Working Statement ​====
  
   CREATE ENVIRONMENT IN "​C:​\data\daily_trans.xml";​   CREATE ENVIRONMENT IN "​C:​\data\daily_trans.xml";​
- 
 ==== With DROP ENVIRONMENT to facilitate scripting ==== ==== With DROP ENVIRONMENT to facilitate scripting ====
 <​code>​ <​code>​
-DROP ENVIRONMENT IF EXISTS ​daily_transactions ​IN "​C:​\data\daily_trans.xml";​+DROP ENVIRONMENT IF EXISTS IN "​C:​\data\daily_trans.xml";​
 CREATE ENVIRONMENT IN "​C:​\data\daily_trans.xml";​ CREATE ENVIRONMENT IN "​C:​\data\daily_trans.xml";​
 </​code>​ </​code>​
- 
-Or alternatively,​ use the WITH DELETE option. 
- 
-  CREATE ENVIRONMENT IN "​C:​\data\daily_trans.xml"​ WITH DELETE; 
- 
- 
  
  
 +==== With All Clauses ====
 +<​code>​
 +create environment
 +  data_cache ​                 32
 +  max_threads ​                2
 +  sql_syntax ​                 oracle
 +  century_threshold ​          80
 +  node "​grid01"​ partitioned ​  
 +     host "​localhost"​ port 7555 environment "​c:​\omnidex\list.xml"​
 +  node "​grid02"​ partitioned ​  
 +     host "​SERVER1" ​  port 7555 environment "​c:​\omnidex\list.xml"​
 +  node "​grid03"​ partitioned ​  
 +     host "​SERVER2" ​  port 7555 environment "​c:​\omnidex\list.xml"​
 +  node "​grid04"​ partitioned ​  
 +     host "​SERVER3" ​  port 7555 environment "​c:​\omnidex\list.xml"​
 +  node "​grid05"​ unpartitioned
 +  in "​list.xml"​
 +  with delete;
 +</​code>​
  
 +====== Additional Resources ======
 +See also: 
 +  * [[dev:​sql:​statements:​create_database:​home|CREATE DATABASE]]
 +  * [[dev:​sql:​statements:​create_table:​home|CREATE TABLE]]
 +  * [[dev:​sql:​statements:​create_index:​home|CREATE INDEX]]
 +  * [[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]]
  
 +Articles:
  
 +  * [[admin:​features:​grids:​home|Omnidex Grids]]
  
  
 
Back to top
dev/sql/statements/create_environment/home.1276720402.txt.gz · Last modified: 2016/06/28 22:38 (external edit)