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/30 00:05]
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 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. 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.
Line 9: Line 9:
  
 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. 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 16: Line 15:
       [ NODE node_name ​       [ NODE node_name ​
           [ <​PARTITIONED | UNPARTITIONED>​ ]           [ <​PARTITIONED | UNPARTITIONED>​ ]
-          [ HOST <​LOCALHOST | host_name | host_ip_address> ​ [ PORT port_number ] ] +          [ CONNECTIONSTRING "filespec" ​] ]
-          [ ENVIRONMENT “filespec” ] ]+
        [ SQL_SYNTAX < OMNIDEX | ORACLE | SQLSERVER > ]        [ SQL_SYNTAX < OMNIDEX | ORACLE | SQLSERVER > ]
-       [ CENTURY_THRESHOLD ​int_year ​]+       [ CENTURY_THRESHOLD ​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” +  ​create environment in "​odx.xml"​ with delete; 
-     [WITH options] ​ +
-</​code>​+
  
-==== DATA_CACHE number_of_megabytes ​====+=== 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. 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.
  
Line 38: Line 35:
   in "​sales.xml";​   in "​sales.xml";​
 </​code>​ </​code>​
-==== MAX_THREADS thread_count ​====+=== 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. 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.
  
Line 46: Line 43:
   in "​sales.xml";​   in "​sales.xml";​
 </​code>​ </​code>​
-==== NODE node_name ​====+=== NODE node_name ===
  
 The NODE clause declares a node in an Omnidex Grid.  Nodes can then be referenced in subsequent CREATE DATABASE and CREATE TABLE statements.  ​ The NODE clause declares a node in an Omnidex Grid.  Nodes can then be referenced in subsequent CREATE DATABASE and CREATE TABLE statements.  ​
  
-=== <​PARTITIONED | UNPARTITIONED> ​===+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 a letter and is case-insensitive. ​ The //​node_name//​ must  be unique within the environment. ​  
 + 
 +== <​PARTITIONED | UNPARTITIONED>​ ==
  
 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. ​   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. ​  
- 
-=== HOST <​host_name | ip_address>​ PORT port ENVIRONMENT "​filename"​ === 
- 
-Nodes can reside on the local server or can reside on remote servers. ​ Remote nodes require the declaration of the host name or IP address, an optional port number and the filename of the remote Omnidex Environment File. 
- 
-=== Examples === 
- 
-This example shows a variety of nodes, all of which reside on the local server and defaults to the the same Environment. 
  
 <​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 74: Line 63:
 </​code>​ </​code>​
  
-This example shows a variety of nodes, some of which reside on remote servers.+== HOST < host_name | ip_address | LOCALHOST > PORT port_no ENVIRONMENT "​filename"​ == 
 + 
 +Nodes can reside on the local server or can reside on remote servers.  Remote nodes require the declaration of the host name or IP address, an optional port number and the filename of the remote Omnidex Environment File.
  
 <​code>​ <​code>​
 create environment create environment
-  data_cache 32 
   node "​grid01"​ partitioned ​     node "​grid01"​ partitioned ​  
-     host "​localhost"​ port 7555 environment "d:​\omnidex\list.xml"​+     host "​localhost"​ port 7555 environment "c:​\omnidex\list.xml"​
   node "​grid02"​ partitioned ​     node "​grid02"​ partitioned ​  
-     host "server1" ​  port 7555 environment "​c:​\omnidex\list.xml"​+     host "SERVER1" ​  port 7555 environment "​c:​\omnidex\list.xml"​
   node "​grid03"​ partitioned ​     node "​grid03"​ partitioned ​  
-     host "server2" ​  port 7555 environment "e:​\omnidex\list.xml"​+     host "SERVER2" ​  port 7555 environment "c:​\omnidex\list.xml"​
   node "​grid04"​ partitioned ​     node "​grid04"​ partitioned ​  
-     host "server3" ​  port 7555 environment "f:​\omnidex\list.xml"​ +     host "SERVER2" ​  port 7555 environment "d:​\omnidex\list.xml"​ 
-  node "GRID05" unpartitioned+  node "grid05" unpartitioned
   in "​list.xml"​   in "​list.xml"​
   with delete;   with delete;
 </​code>​ </​code>​
-==== SQL_SYNTAX <OMNIDEX | ORACLE | SQLSERVER > ====+ 
 +=== SQL_SYNTAX <OMNIDEX | ORACLE | SQLSERVER > === 
 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 easier. Note that this support is limited to a subset of each databases'​s extensions and does not encompass the complete set of extensions.  ​ 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 easier. Note that this support is limited to a subset of each databases'​s extensions and does not encompass the complete set of extensions.  ​
  
Line 104: Line 96:
  
  
-==== 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. ​ If no CENTURY_THRESHOLD is specified, then the internal default of 50 is used. 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>​ <​code>​
Line 111: Line 103:
   in "​sales.xml";​   in "​sales.xml";​
 </​code>​ </​code>​
 +=== IN "​env_filespec"​ ===
  
-==== IN "​filename"​ ====+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.
  
-The IN clause ​is required ​and specifies the location of the physical XML Environment File.+//​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.
  
-The IN clause must be specified after all other clauses except the WITH clause. +The //​env_filespec// ​must be enclosed within double quotes.
- +
-The filespec can be specified either as an absolute or a relative file specification. ​ When using a relative file specification,​ it will be relative to the current working directory for the program that is being run. +
- +
-The IN filespec ​must be enclosed within double quotes.+
  
 <​code>​ <​code>​
Line 132: Line 121:
     in "/​usr/​opt/​dev/​odx.xml";​ /* UNIX/Linus absolute filespec */     in "/​usr/​opt/​dev/​odx.xml";​ /* UNIX/Linus absolute filespec */
 </​code>​ </​code>​
-==== WITH options ​==== + 
-=== DELETE ​===+=== 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. 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.
  
Line 145: Line 135:
 ==== 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>​
- 
-==== 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.1277856314.txt.gz · Last modified: 2016/06/28 22:38 (external edit)