Create Statement

You use the create statement with Ingres Star to create new tables and views and add them to your distributed database.

The create table statement creates a local table and registers the table in your distributed database as native.
If you do not use the create table statement at the Ingres Star level, you first must create a table locally and then register it in your distributed database.
The create view statement creates a distributed view.

Create Table Statement

Use the create table statement to create new tables:

create table with with_clause
create table as subselect with with_clause

The create table statement creates an object of type table. The table is stored in a local database or the coordinator database, depending on the node_name specified in the with clause. By default, the table is stored in the coordinator database. The table is automatically registered in the Ingres Star catalogs as a native object. Tables registered as native are distinguished from tables registered as links by the value in the table_subtype column in the iitables catalog.

Create Table With Syntax

The create table with statement has the following format:

create table table_name
       (col_name format {, col_name format})
       [with
              [node =node_name,
              database = database_name]
              [, dbms = server_class]
              [, table =local_table_name]
              [, LDB with clauses]]

Create Table as Subselect With Syntax

The syntax for the create table as subselect with statement is:

create table table_name
       [(col_name {, col_name})]
       as subselect
       [with
              [node =node_name,
              database =database_name]
              [, dbms = server_class]
              [, table = local_table_name]
              [, LDB with clauses]]

Create Table Syntax Elements

The col_name, node_name, database_name, server_class, and local_table_name syntax elements are as described in previous statements. The remaining syntax elements are described in the table below and in the following section, LDB With Clauses.

table
The object identifier. This must be specified.
table_name
The name in the distributed database. It may be delimited with double quotes.

If the local_table_name is not specified the registered table_name is used. The table names referenced in the subselect clause are Ingres Star-level objects. They must be registered or created first through Ingres Star before being called by subselect
format
Formats refer to the data type of the column as well as how unspecified values (blanks and nulls) should be handled. For a full description of data formats, see the SQL Reference Guide.

LDB With Clauses

Ingres Star recognizes and processes with clauses for defining node_name, database_name, server_class, and local_table_name as shown in the syntax descriptions above.

Other with clauses (LDB with clauses) in the syntax descriptions above) are not supported or recognized by Ingres Star. For example, Ingres Star is not responsible for handling location, journaling and duplicates in the with clause. However, it is Ingres Star's responsibility to guarantee that these clauses are properly transmitted to the local DBMS for processing. These options are received and managed by each local DBMS.

For a complete list of the LDB with clauses, see the with clauses of the create table description in your query language reference guide.

Examples: Create Table

The following statement creates the table corp_dept in the coordinator database and registers it in the distributed database:

create table corp_dept
    (dno char(8),
    name char(10),
    budget integer);

Create the table department in the database west_usa on the node reno and register it in the distributed database corporateddb under the name corp_dept:

create table corp_dept
    (dno char(8),
    name char(10),
    budget integer)
  with node = reno,
  database = west_usa,
  table = department;

A table low_budgets could then be created in the coordinator database and registered in the distributed database by using a subselect from the table corp_dept:

create table low_budgets
as select * from corp_dept
where budget < 10000;

The following example shows a create table statement with an LDB with clause, journaling:

create table corpemployee
    (name char(20),
    sal money)
  with journaling;

Journaling is set at the local DBMS. Ingres Star does not register this attribute in the Ingres Star catalogs but passes it to the local DBMS to be processed.

If your Star database allows mixed-case delimited identifiers or if you wish your table or column names to include special characters such as spaces, use a delimited identifier:

create table "World Wide Sales" ("Region" char(20),
"Gross Sales in Millions" decimal(16,2));

Create View Statement

The create view statement creates a distributed view.

The created view is not a local database view. It is a distributed view known only to the distributed database.

Create View Syntax

The create view statement has the following format:

create view view_name
[(col_name {,col_name})] as subselect

view
Refers to the object identifier. This must be specified.
view_name
Defines the name of the view in the distributed database. It can be delimited with double quotes. This is an Ingres Star-level object as opposed to an object in a local database. The definition of this view is entered into the Ingres Star catalogs. The view can reference other tables and views.

For a description of the subselect and other syntax details of the create view statement, see the SQL Reference Guide.