This shows you the differences between two versions of the page.
|
dev:sql:statements:create_database:home [2010/06/04 21:17] 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”] | ||
| - | [PHYSICAL “physical”] | ||
| - | [USER “user”][PASSWORD “password”] | ||
| - | [INDEX_MAINTENANCE index_maintenance_type] | ||
| - | IN “filename” /* must be after all previous clauses */ | ||
| - | [WITH options] /* if used, must be the very last clause */ | ||
| - | </code> | ||
| - | |||
| - | ===== Discussion ===== | ||
| - | ==== DATABASE database_name ==== | ||
| - | The //database_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_name// must be unique within an Omnidex Environment. | ||
| - | |||
| - | //Database_name// is used to qualify table and index names when the same table or index name occurs more than once within an Omnidex Environment. | ||
| - | |||
| - | //Database_name// is also used as the prefix for index files located in the //index_directory// as specified in the INDEX_DIRECTORY setting. | ||
| - | <code> | ||
| - | CREATE DATABASE SALES IN "SALES.XML"; | ||
| - | ... | ||
| - | UPDATE INDEXES | ||
| - | exit | ||
| - | os> dir | ||
| - | SALES0001, SALES0002, SALES0003, SALES.XML | ||
| - | </code> | ||
| - | |||
| - | ==== TYPE type ==== | ||
| - | 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 specified. | ||
| - | |||
| - | === [VERSION “version”] === | ||
| - | The VERSION setting within the TYPE clause is used to by Omnidex to determine which syntax and api to use for Oracle access. Only the major Oracle version (8, 9, 10, 11) needs to be specified. If using Oracle SQLNET, the version should read SQLNET n, where n is the major Oracle version. | ||
| - | === [SUBTYPE subtype] === | ||
| - | The SUBTYPE setting within the TYPE clause is used to specify a subtype for ODBC databases. | ||
| - | Valid subtypes are: | ||
| - | * MYSQL | ||
| - | * SQLSERVER | ||
| - | | ||
| - | ==== [INDEX_DIRECTORY “directory”] ==== | ||
| - | 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 "dbms_string" ] ==== | ||
| - | The PHYSICAL "dbms_string" declares RDBMS connection information and is used primarily with relational databases and ODBC. | ||
| - | |||
| - | The "dbms_string" varies according to database type as follows: | ||
| - | |||
| - | * FLATFILES - ignored | ||
| - | * ORACLE with SQLNET - if using Oracle SQLNET, use the name of the SQLNET service and specify "SQLNET x" in the VERSION clause. | ||
| - | * ORACLE without SQLNET - The PYSICAL caluse is ignored and Oracle will use the default database for the specified user. | ||
| - | * SQLSERVER - The PHYSICAL clause contains the ODBC connection information. For FILE DSNs, use "FILEDSN=file_dsn_filespec". For System/User DSNs, use "DSN=dsn_name" or just "dsn_name" where dsn_name is the data source name set up in the Windows ODBC Administrator program. | ||
| - | * ODBC - The PHYSICAL clause contains the ODBC connection information. For FILE DSNs, use "FILEDSN=file_dsn_filespec". For System/User DSNs, use "DSN=dsn_name" or just "dsn_name" where dsn_name is the data source name set up in the Windows ODBC Administrator program. | ||
| - | |||
| - | ==== [USER "DEFAULT" | “user”] [PASSWORD “password”] ==== | ||
| - | The USER / PASSWORD clause is used to provide a valid RDBMS database user and password to be used for Omnidex to access the underlying RDBMS data source. | ||
| - | |||
| - | The USER / PASSWORD clause is required for ORACLE. It declares either or both the name and password of a valid database. All connections to the database will connect with this option. USER and PASSWORD can be filespecs where the contents of the file contain the user and password. | ||
| - | |||
| - | ==== [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}} | ||