Differences

This shows you the differences between two versions of the page.

--- dev:sql:statements:create_database:home [2010/07/02 22:21]
tdo
+++ dev:sql:statements:create_database:home [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
+^ Omnidex SQL Quicklinks ^^^^^
+|[[dev:sql:overview | Overview ]] | [[dev:sql:statements:home | Statements ]] | [[ dev:sql:functions:home | Functions ]] | [[ dev:sql:examples:home | Examples ]] | [[dev:sql:home | Quick Reference ]] |
 ====== Omnidex SQL: CREATE DATABASE ======
-{{page>:sql_bar&nofooter&noeditbtn}}
 ===== Description =====
 The CREATE DATABASE statement declares a database in an Omnidex Environment File.  Once one or more databases and their respective tables have been declared in an environment, they can be indexed and retrieved using SQL statements.
-An Omnidex database can point to an underlying Oracle, SQL Server or MySQL database, or any other database that can be accessed using ODBC.  An Omnidex database can also point to a collection of raw data files that combine together to form a database.  These databases are accessible using the Omnidex SQL engines, available through standard interfaces like ODBC and JDBC.
+An Omnidex database can point to an underlying Oracle, SQL Server or MySQL database, or any other database that can be accessed using ODBC.  An Omnidex database can also point to a collection of raw data files that combine together to form a database.  These databases are accessible using the Omnidex SQL engines, available through ODBC and JDBC interfaces.
-The CREATE DATABASE statement records information about the underlying database in the Omnidex Environment Catalog, but it does not actually create an underlying relational database.  The physical database should be created using the relational database's tools.  In the case of raw data files, the files are usually received from a data provider, and application or an ETL tool.  The CREATE DATABASE statement simply stores meta-data about the database in the Omnidex Environment File.
+The CREATE DATABASE statement records information about the underlying database in the Omnidex Environment File, but it does not actually create an underlying relational database.  The physical database should be created using the relational database's tools.  In the case of raw data files, the files are usually received from a data provider, and application or an ETL tool.  The CREATE DATABASE statement simply stores meta-data about the database in the Omnidex Environment File.
 Omnidex allows multiple databases to be declared in an environment.  When an application connects to an Omnidex Environment File, then SQL statements can be issued that access all of the databases in the environment.  Databases can be of different types as well, and this allows applications to issue SELECT statements that join tables from different database types, including raw data files.
@@ Line 16: / Line 17: @@
 <code>
   CREATE DATABASE database_name
-      [NODE node_name]
+      [NODE grid_node]
-          TYPE type
+          TYPE database_type
-          [VERSION “version”]
+              [SUBTYPE odbc_subtype]
-          [SUBTYPE subtype]
           [INDEX_DIRECTORY “directory”]
-          [PHYSICAL “physical”]
+          [VERSION “rdbms_version”]
-          [USER “user” PASSWORD “password”]
+          [PHYSICAL “rdbms_string”]
+          [USER “rdbms_user” PASSWORD “user_password”]
           [INDEX_MAINTENANCE index_maintenance_type]
-      IN “filename”                             /* must be after all previous clauses */
+      IN “environment_filespec”                        /* must be after all previous clauses */
-      [WITH options]                            /* if used, must be the very last clause  */
 </code>
 ===== Discussion =====
+=== DATABASE database_name ===
-==== DATABASE database_name ====
+The //database_name// uniquely identifies the database within this environment.  It may be up to 32 characters long, and can contain letters, digits and the following special characters: ! @ # $ % ^ _.  It must begin with a letter and is case-insensitive.  The //database_name// must  be unique within the environment.  If //database_name// is a SQL reserved word, enclose it in double quotation marks.
-The //database_name// uniquely identifies the database within this environment.  It may be up to 32 characters long, and can contain letters, digits and the following special characters: ! @ # $ % ^ _.  It must begin with a letter and is case-insensitive.  The //database_name// must  be unique within the 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 used to qualify table and index names when the same table or index name occurs more than once within an Omnidex Environment File.
 //Database_name// is also used as the prefix for index files located in the //index_directory// as specified in the INDEX_DIRECTORY setting.
@@ Line 46: / Line 45: @@
 </code>
-==== NODE node ====
+=== NODE grid_node ===
 The optional NODE clause is used to configure an Omnidex Grid.  Nodes that were previously declared in the CREATE ENVIRONMENT statement can be referenced here, and the database configuration can be named for each node.  The NODE clause is only required for databases within an Omnidex Grid that are partitioned and distributed across multiple nodes.  If not using an Omnidex Grid, the NODE clause can be skipped and the underlying configuration can be specified for the entire database.
 For simple environments, the database configuration does not require NODE clauses:
@@ Line 57: / Line 56: @@
 create database              "LIST"
- type                        FLATFILE
+ type                        FILE
- index_directory             "idx\LIST_"
+ index_directory             "idx"
  in                          "list.xml";
 </code>
@@ Line 76: / Line 75: @@
 create database              "LIST"
  node "GRID01"
-  type                       FLATFILE
+  type                       FILE
-  index_directory            "idx\LIST1_"
+  index_directory            "idx\grid01"
  node "GRID02"
-  type                       FLATFILE
+  type                       FILE
-  index_directory            "idx\LIST2_"
+  index_directory            "idx\grid02"
  node "GRID03"
-  type                       FLATFILE
+  type                       FILE
-  index_directory            "idx\LIST3_"
+  index_directory            "idx\grid03"
  node "GRID04"
-  type                       FLATFILE
+  type                       FILE
-  index_directory            "idx\LIST4_"
+  index_directory            "idx\grid04"
  node "GRID05"
-  type                       FLATFILE
+  type                       FILE
-  index_directory            "idx\LIST5_"
+  index_directory            "idx\grid05"
  in                          "list.xml";
 </code>
-==== TYPE type ====
+=== TYPE database_type ===
-The TYPE clause specifies the type of database being declared.  The //type// is always required.
+The TYPE clause specifies the type of database being declared.  The //database_type// is  required.
 Valid database types are:
-  * FLATFILE
+  * FILE
   * ORACLE
   * SQLSERVER
   * ODBC
-When a database is spread across multiple nodes of an Omnidex Grid, the node is declared for each time.  At present, the type must be the same across all nodes; however, support for different database types for each node may be supported in the future.
+When a database is spread across multiple nodes of an Omnidex Grid, the PHYSICAL clause is declared for each node.  At present, the //database_type// must be the same across all nodes; however, support for different database types for each node may be supported in the future.
-=== SUBTYPE subtype ===
+=== SUBTYPE odbc_subtype ===
 The SUBTYPE setting within the TYPE clause is used to specify a subtype for ODBC databases.
@@ Line 114: / Line 113: @@
 Omnidex can access other databases through ODBC, even if the subtype is not declared.  Note that there can be differences in the way that each underlying database implements ODBC.  Because of this, some Omnidex functionality may not be available.
+=== INDEX_DIRECTORY “directory” ===
-==== VERSION “version” ====
+The INDEX_DIRECTORY is an optional clause specifies the operating system location of the Omnidex index files.  The directory can either be an absolute or relative path.  Relative paths are relative to the location of the Omnidex Environment File.  Relative locations are recommended when feasible.
+If no //directory// is declared, then the Omnidex index files will reside in the same directory as the Omnidex Environment File.
+The //directory// can also be specified with an environment variable using curly braces.
+<code>
+create database              "LIST"
+  type                       FILE
+  index_directory            {$LIST_DIR}
+</code>
+=== VERSION “rdbms_version” ===
 The VERSION clause is used to by Omnidex to determine which syntax and API to use with the underlying relational database.
@@ Line 133: / Line 144: @@
 | 9 | All versions of SQL Server 2005 |
-==== INDEX_DIRECTORY “directory” ====
-The INDEX_DIRECTORY clause specifies the operating system location of the Omnidex index files and optionally the index file prefix.
+=== PHYSICAL "rdbms_string" ===
+The PHYSICAL //rdbms_string// declares RDBMS connection information and is only used with relational databases and ODBC.  The //rdbms_string// varies according to database type as follows:
+^Database Type  ^Description  ^
+| FILE | ignored |
+| ORACLE | The PHYSICAL clause is ignored and Oracle will use the default database for the specified user. |
+| ORACLE w/SQLNET | if using Oracle SQLNET, use the name of the SQLNET service and specify "SQLNETx" in the VERSION clause. |
+| SQLSERVER | The PHYSICAL clause contains the ODBC connection information.  For File DSNs, use "FILEDSN=file_dsn_filespec".  For System and 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 and 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. |
-The directory portion can either be an absolute or relative path.  Relative paths are relative to the location of the Omnidex Environment File.  Relative locations are recommended when feasible.
+===  USER “rdbms_user” PASSWORD “user_password” ===
-If no //index_directory// is declared, then the Omnidex index files will reside in the same directory as the Omnidex Environment File.
-==== PHYSICAL "dbms_string" ====
-The PHYSICAL "dbms_string" declares RDBMS connection information and is only used 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 "SQLNETx" in the VERSION clause.
-  * ORACLE without SQLNET - The PHYSICAL clause 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 and 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 and 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 “user” PASSWORD “password” ====
 The USER and PASSWORD clauses are used to provide a valid RDBMS database user and password to be used for Omnidex to access the underlying RDBMS data source.  The USER and PASSWORD clauses are required for Oracle, and are otherwise optional.
-All connections to the underlying database will use this //user// and //password//.  Omnidex does not have a supplemental security facility that allows sending different users and passwords.
+All connections to the underlying database will use this //rdbms_user// and //user_password//.  Omnidex does not have a supplemental security facility that allows sending different users and passwords.
-==== INDEX_MAINTENANCE index_maintenance_type ====
+=== 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 either API or DBMS.
@@ Line 162: / Line 167: @@
 This setting applies to the entire index installation for this database. It can be overridden on a table-by-table basis by explicitly defining the //index_maintenance// setting on an individual table.
-====      IN “env_filespec” ====
+===      IN “environment_filespec” ===
-The IN clause is required and specifies the location of the physical XML Environment File.  The IN clause must be specified after all other clauses except the WITH clause.
-//Env_filespec// can also include either an absolute or relative path.  When using a relative file path , it will be relative to the current working directory for the program that is being run. Typically in a production environment, absolute paths are used.  When using Omnidex's Network Services and the OdxNet process, absolute paths should always be used.
+The IN clause is required and specifies the location of the physical XML Environment File.  The IN clause must be specified after all other clauses.
-The //env_filespec// must be enclosed within double quotes.
+//Environment_filespec// can also include either an absolute or relative path.  When using a relative file path , it will be relative to the current working directory for the program that is being run. Typically in a production environment, absolute paths are used.  When using Omnidex's Network Services and the OdxNet process, absolute paths should always be used.
-==== WITH options ====
+The //environment_filespec// must be enclosed within double quotes.
-There are currently no options for the CREATE DATABASE statement.
 ===== Examples =====
-==== Simple ====
+==== Single Database with no Nodes ====
 <code>
 create environment in "env1.xml" with delete;
 create database db1 type flatfile in "env1.xml";
 </code>
 ==== Two Databases within an Environment ====
 <code>
@@ Line 220: / Line 223: @@
 disconnect
 </code>
+====== Additional Resources ======
+See also:
+  * [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]]
+  * [[dev:sql:statements:create_table:home|CREATE TABLE]]
+  * [[dev:sql:statements:create_index:home|CREATE INDEX]]
+  * [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]]
+Articles:
+  * [[admin:features:grids:home|Omnidex Grids]]
+  * [[dev:appendix:articles:dbspecific:home|Database-specific issues]]
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/statements/create_database/home.1278109292.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes