Neoview SQL Reference Manual (R2.4 SP2)

15 1
16 1
In the next example, table t3 provokes an error because you tried to create a column with the
GENERATED ALWAYS AS IDENTITY column, which is not allowed.
CREATE TABLE t3 (a LARGEINT GENERATED ALWAYS AS IDENTITY NOT NULL,
b INT, PRIMARY KEY(a)) AS SELECT a,b FROM t1;
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot
accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
Reserved Table Names
Table names prefixed by the name of a user metadata table are reserved. You cannot create tables
with such names. For example, you cannot create tables that are prefixed by these names:
• HISTOGRAMS
• HISTOGRAM_INTERVALS
• MVS_TABLE_INFO_UMD
• MVS_UMD
• MVS_USED_UMD
Authorization and Availability Requirements
To create a table, you must own the schema or be granted the CREATE or CREATE_TABLE
privilege for the schema. To create a constraint on the table that refers to a column in another
table, you must have REFERENCES privileges on that column and access to the table that contains
the column. If the constraint refers to the other table in a query expression, you must also have
SELECT privileges on the other table.
Limits for Tables
You can create tables and indexes with rows that are 32,708 bytes in size and blocks that are 32K
bytes in size. The default block size for all create table and index operations is 32768 (32K) byte
block size (in all cases).
There are no tools provided for explicitly migrating an existing 4KB block size table to a 32KB
block size table. If you want a larger block size, a new table must be created and data copied
from the old table to the new table. Table 2-2 describes the block size, the size available to Neoview
SQL, and the size available to the user.
Table 2-2 Maximum Row Sizes Available
Max row size available to usersMax row size available to SQLBlock size
403640404096
327083271232768
Tables and Triggers
If you plan to create triggers on a table, its primary key length cannot exceed 2032 bytes. A table
which will not have triggers can have a primary key of 2048 bytes. For details about this limit,
see “Triggers and Primary Keys” (page 98).
Calculating Row Size
The row size on disk depends upon the following:
• Size of columns based on type.
• Number of variable length columns.
86 SQL Statements