This shows you the differences between two versions of the page.
dev:sql:statements:create_database:home [2010/05/24 06:34] tdo |
dev:sql:statements:create_database:home [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
- | ====== Omnidex SQL: CREATE DATABASE ====== | ||
- | {{page>:sql_bar&nofooter&noeditbtn}} | ||
- | ===== Description ===== | ||
- | 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. | ||
- | ===== Syntax ===== | ||
- | |||
- | <code> | ||
- | 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 */ | ||
- | </code> | ||
- | |||
- | ===== Discussion ===== | ||
- | ==== DATABASE database_name ==== | ||
- | 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. | ||
- | ==== TYPE type ==== | ||
- | The TYPE setting specifies the DATABASE TYPE and is a required clause. | ||
- | |||
- | Valid DATABASE types are: | ||
- | |||
- | * FLATFILE | ||
- | * ORACLE | ||
- | * SQLSERVER | ||
- | * ODBC | ||
- | * DB2 | ||
- | * UNION | ||
- | * INFORMIX | ||
- | |||
- | A database can be spread across multiple Omnidex data nodes and if so, each NODE must have a TYPE specified. | ||
- | |||
- | === [VERSION “version”] === | ||
- | The VERSION setting within the TYPE clause is used to specify the RDBMS database version and is required for Oracle databases. | ||
- | === [SUBTYPE subtype] === | ||
- | The SUBTYPE setting within the TYPE clause is used to specify a subtype for ODBC databases. | ||
- | Valid subtypes are: | ||
- | * ORACLE | ||
- | * INFORMIX | ||
- | * DB2 | ||
- | * SQLSERVER | ||
- | * ACCESS | ||
- | |||
- | ==== [INDEX_DIRECTORY “directory[\index_prefix]”] ==== | ||
- | 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. | ||
- | ==== [NODE node] ==== | ||
- | The optional NODE clause is used to spread a database across the various nodes in an Omnidex Grid Environmnet. | ||
- | |||
- | Each NODE name used in the CREATE DATABASE NODE clause must correspond to a node_name specified in the CREATE ENVIRONMENT statement. | ||
- | <code> | ||
- | 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"; | ||
- | </code> | ||
- | |||
- | ==== [PHYSICAL “physical”] ==== | ||
- | ==== [USER “user”] [PASSWORD “password”] ==== | ||
- | The USER / PASSWORD clause is used to specify the user and password to be used to access the underlying RDBMS. | ||
- | |||
- | ==== [INDEX_MAINTENANCE index_maintenance_type] ==== | ||
- | ==== IN “filespec” ==== | ||
- | 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. | ||
- | |||
- | ==== [WITH options] ==== | ||
- | There are currently no options for the CREATE DATABASE statement. | ||
- | |||
- | ===== Examples ===== | ||
- | ==== Simple ==== | ||
- | <code> | ||
- | create environment env1 in "env1.xml" with delete; | ||
- | create database db1 type flatfile in "env1.xml"; | ||
- | </code> | ||
- | ==== Two Databases within an Environment ==== | ||
- | <code> | ||
- | 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 | ||
- | </code> | ||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} |