This is an old revision of the document!


DRAFT

Omnidex SQL: CREATE ENVIRONMENT

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 the data to be used for indexing and retrieval.

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 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

CREATE ENVIRONMENT 
    [ DATA_CACHE number_of_megabytes ] 
    [ MAX_THREADS thread_count ]
    [ NODE node_name 
        [ <PARTITIONED | UNPARTITIONED> ]
        [ HOST <LOCALHOST | host_name | host_ip_address>  
            [ PORT port_number ] ]
            [ ENVIRONMENT “filespec” ] ]
     [ SQL_SYNTAX < ORACLE | OMNIDEX > ]
     [ CENTURY_THRESHOLD int_year ]
    IN “filename”                             /* must be after all previous clauses */
    [WITH options]                            /* if used, must be the very last clause  */

The CREATE ENVIRONMENT statement is used in conjunction with the DROP ENVIRONMENT statement.

DROP ENVIRONMENT [IF EXISTS] 
     IN “filename”
     [WITH options] 

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.

The filespec can be specified either as an absolute or a relative file specification.

The IN filespec must be enclosed within double quotes.

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.

create environment 
  data_cache 1024               /* sets the data_cache to 1 gigabyte */
  in "sales.xml";

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.

So for an eight processor system, a setting of MAX_THREADS 6 or MAX_THREADS 7 could be used.

The default is the number of nodes specified in CREATE ENVIRONMENT NODE clauses.

NODE node_name

The NODE clause is used to specify a 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.

Nodes are used to specify Omnidex Grid information which facilitates retrieval performance by splitting large tables or files into smaller units for retrieval.

create environment          
  data_cache                  32
  max_threads                 2
  node                        "GRID01" partitioned
  node                        "GRID02" partitioned
  node                        "GRID03" partitioned
  node                        "GRID04" partitioned
  node                        "GRID05" unpartitioned
  in                          "list.xml";

PARTITIONED | UNPARTITIONED

Nodes are either PARTITIONED or UNPARTITIONED. An UNPARTITIONED Environment 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.

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 declared, it is presumed to be a partitioned node.

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.

create environment
  data_cache 32
  node "grid01" partitioned   host localhost port 7555
  node "grid02" partitioned   host server1 port 7555
  node GRID03   unpartitioned
  node grid04                 host server2 port 7555
  node "GRID05" unpartitioned
  in "list.xml"
  with delete;

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 <ORACLE | OMNIDEX >

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 default for this setting is OMNIDEX and the Oracle SQL extensions can be turned off programmicatally usng the SET statement.

SET SQL_SYNTAX OMNIDEX;

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 specified here has environment scope and is used for all retrievals unless the century threshold is specified at the column level.

If no CENTURY_THRESHOLD is specified, then the internal default of 50 is used.

WITH options

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.

CREATE ENVIRONMENT IN "odx.xml" WITH DELETE

Examples

Simple

CREATE ENVIRONMENT IN "C:\data\daily_trans.xml";

With DROP ENVIRONMENT to facilitate scripting

DROP ENVIRONMENT IF EXISTS daily_transactions IN "C:\data\daily_trans.xml";
CREATE ENVIRONMENT IN "C:\data\daily_trans.xml";

Or alternatively, use the WITH DELETE option.

CREATE ENVIRONMENT IN "C:\data\daily_trans.xml" WITH DELETE;
 
Back to top
dev/sql/statements/create_environment/home.1277849153.txt.gz · Last modified: 2016/06/28 22:38 (external edit)