Neoview SQL Reference Manual (R2.3)

• This statement fails with an error stating that only LARGEINT columns can be defined as
an IDENTITY column.
CREATE TABLE T (surrogate_key CHAR(64) GENERATED BY
DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL)
HASH PARTITION BY(surrogate_key);
• 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.
DISK POOL
The DISK POOL attribute allows disks to be divided into disk pools.
Considerations for DISK POOL
• 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.
Examples of CREATE TABLE
• This example creates a table. The clustering key is the primary key.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ordernum, partnum) );
• This example creates a table like the JOB table with the same constraints:
CREATE TABLE SAMDBCAT.PERSNL.JOB_CORPORATE
LIKE SAMDBCAT.PERSNL.JOB WITH CONSTRAINTS;
78 SQL Statements