Omnidex SQL: CREATE DATABASE

The CREATE DATABASE statement names a logical database_name which contains one or more RDBMS tables or files, all of a particular type such as Oracle, SQLServer, or FLATFILE.

The CREATE DATABASE does not create any type of physical database and only works on the Omnidex metadata.

Multiple databases can be created within a single Omnidex Environment using multiple CREATE DATABASE statements. The database logical name must be unique within the Environment file.

An Omnidex database is the logical index collection and is also the unit for indexed optimized joins between tables.

  CREATE DATABASE database_name
      [NODE node_name]
      TYPE type
         [VERSION “version”]
         [SUBTYPE subtype]
      [INDEX_DIRECTORY “directory[\index_prefix]”]
      [PHYSICAL “physical”]
      [USER “user”][PASSWORD “password”]
      [INDEX_MAINTENANCE index_maintenance_type]
      IN “filename”                                /^ must be after all other clauses */
      [WITH options]                               /* must be the very last clause    */

The datbase_name is up to a 32 character case-insensitive name and can contain letters, digits and the following special characters:

 ! @ # $ % ^ _ 

The first character of the database_name must be a letter.

Database_names must be unique within an Omnidex Environment.

Database_names are used to qualify table and index names when the same table or index name occurs more than once within an Omnidex Environment.

The database_name is also used as the default Omnidex index_prefix if no index_prefix component is specified in the INDEX_DIRECTORY setting.

The TYPE setting specifies the DATABASE TYPE and is a required clause.

Valid DATABASE types are:

• FLATFILE
• ORACLE
• SQLSERVER
• ODBC

A database can be spread across multiple Omnidex data nodes and if so, each NODE must have a type TYPE specified.

The VERSION setting within the TYPE clause is used to specify the RDBMS database version.

The SUBTYPE setting within the TYPE clause is used to specify the

The INDEX_DIRECTORY setting determines the operating system location of the Omnidex index files and optionally the index file prefix.

The directory portion can either be an absolute or relative location. Typically relative locations are used.

The index_prefix portion is used by Omnidex for creating the physical file names of the index files. Index files will be the index_prefix followed by four digits.

If no index_prefix is specified, the database_name is used as an index prefix.

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


create database              "LIST"
 node "GRID01"
  type                       FLATFILE
  index_directory            "idx\LIST1_"
 node "GRID02"
  type                       FLATFILE
  index_directory            "idx\LIST2_"
 node "GRID03"
  type                       FLATFILE
  index_directory            "idx\LIST3_"
 node "GRID04"
  type                       FLATFILE
  index_directory            "idx\LIST4_"
 node "GRID05"
  type                       FLATFILE
  index_directory            "idx\LIST5_"
 in                          "list.xml";

The USER / PASSWORD clause is used to specify the user and password to be used to access the underlying RDBMS.

The IN “filespec” clause specifies the physical file name and directory containing the metadata for an Omnidex Environment.

The IN “filespec” can be specified either as a relative or absolute file specification and much be enclosed in double quotes.

There are currently no options for the CREATE DATABASE statement.

create environment env1 in "env1.xml" with delete;
create database db1 type flatfile in "env1.xml";

create environment env1 in "env1.xml" with delete;

create database db1 type flatfile in "env1.xml";
create database db2 type flatfile in "env1.xml";

create table db1.table1 physical "db1table1"
  ( pk integer,
    mytext char(60) quicktext
  )
  in "env1.xml";

create table db2.table1 physical "db2table1"
  ( pk integer,
    mytext char(60) quicktext
  )
  in "env1.xml";

create file db1table1;
create file db2table1;

connect env1

insert into db1.table1 values (1, 'db1 row 1');
insert into db1.table1 values (2, 'db1 row 2');

select * from db1.table1;

insert into db2.table1 values (1, 'db2 row 1');
insert into db2.table1 values (2, 'db2 row 2');

select * from db2.table1;

select * from db1.table1
union
select * from db2.table1;

disconnect