Neoview SQL Reference Manual (R2.3)

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 be the schema owner or have the CREATE or CREATE_TABLE
privilege. You can also create tables on services but the owner of the table is the schema owner,
not services (unless services has the create privilege).
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-1 describes the block size, the size available to Neoview SQL, and the size available to
the user.
Table 2-1 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 85).
Calculating Row Size
The row size required for Neoview SQL tables is dependent on the number of variable length
columns and number of columns that can be null.
The following table has four nullable columns and one variable length column. The calculation
for the size of each of the fields is listed beside the column definition. In addition to the size of
each field, there is an additional four bytes added at the beginning of each row inserted for a
maximum size of 310 bytes.
Create Table CustOrder
(orderKey largeint not null, -- 8 bytes
itemNum smallint not null, -- 2 bytes
orDate date not null, -- 4 bytes
shipDate date, -- 6 bytes
shipMode char(12), -- 14 bytes
rtnDate date, -- 6 bytes
rtnReason varchar(256), -- 266 bytes
CREATE TABLE Statement 75