Neoview SQL Reference Manual (R2.4 SP2)

• In this example, none of the sequence generator options are specified; the defaults values
for all options are used.
start value: 0 (zero)
increment: 1
min value: 0 (zero)
max value: 4294967295
NO CYCLE
CREATE TABLE tbl1 (
Id_col INTEGER UNSIGNED GENERATED BY DEFAULT AS IDENTITY NOT NULL,
Col2 INTEGER, NOT NULL, PRIMARY KEY(Id_col)
);
• This example shows that the IDENTITY column options can be specified in any order.
CREATE TABLE tbl1 (
Id_col INTEGER UNSIGNED GENERATED BY DEFAULT AS IDENTITY
(
START WITH 100
MAXVALUE 1000
INCREMENT BY 2
MINVALUE 50) NOT NULL,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
• This statement fails with an error stating that a table can have only one IDENTITY column.
CREATE TABLE T (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
name CHAR (256) NOT NULL,
order_number LARGEINT GENERATED BY DEFAULT AS
IDENTITY NOT NULL)
HASH PARTITION BY(surrogate_key);
Neoview SQL Extensions to CREATE TABLE
This statement is supported for compliance with ANSI SQL:1999 Entry Level. Neoview SQL
extensions to the CREATE TABLE statement are ASCENDING, DESCENDING, PARTITION,
MAXTABLESIZE, ATTRIBUTE, and DISK POOL clauses. CREATE TABLE LIKE is also an
extension.
Considerations for DISK POOL
The DISK POOL attribute allows disks to be divided into disk pools.
• The default number of pools for systems up to 256 disks is 1 and for systems beyond 256
disks, the default number is 2.
• Each disk pool has the same number of disks.
• The number of disks in a pool is the total disks divided by the number of pools.
• Disks within a pool are evenly distributed among segments.
• Distribution of disks to disk pools — For 2 pools with 2 disks per CPU, the first disk of every
CPU belongs to pool 1 and the second disk of every CPU belongs to pool 2.
• Tables can be allocated to disks that belong to the requested disk pool number.
• By default, tables are assigned to disk pools in a round robin fashion.
• The default number of partitions created are as many number of disks in a disk pool.
• A non partitioned table can be created within a disk pool using the NO PARTITION clause.
Restrictions for DISK POOL
DISK POOL cannot be used with volatile tables, materialized views, indexes, and CREATE
TABLE LIKE.
CREATE TABLE Statement 91