This shows you the differences between two versions of the page.
|
dev:sql:statements:create_database:home [2010/05/24 06:48] 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 ORACLE database version and is required with 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 “filespec” | "string" ] ==== | ||
| - | The PHYSICAL "filespec" declares the name and location of the database's root file or system dictionary and is used primarily with relational databases and ODBC. | ||
| - | |||
| - | The "filespec" or "string" may vary according to database type. | ||
| - | |||
| - | ==== [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] ==== | ||
| - | The INDEX_MAINTENANCE clause is an optional clause that specifies how Omnidex indexes will be updated. | ||
| - | |||
| - | The index_maintenance_type can be one of either API or DBMS. | ||
| - | |||
| - | API - The indexes will be updated by Omnidex automatically as the database data is updated. | ||
| - | |||
| - | DBMS - The indexes will be updated by some other means, ODXAIM for example, in a separated process. | ||
| - | |||
| - | This setting applies to the entire index installation on this database. It can be over-ridden on a table-by-table basis by explicitly defining the index_maintenance setting on an individual table. | ||
| - | |||
| - | ==== 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" /* Note the db1 database qualification */ | ||
| - | ( pk integer, | ||
| - | mytext char(60) quicktext | ||
| - | ) | ||
| - | in "env1.xml"; | ||
| - | |||
| - | create table db2.table1 physical "db2table1" /* Note the db2 database qualification */ | ||
| - | ( 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}} | ||