ALLBASE/SQL Reference Manual (36216-90216)

354 Chapter10
SQL Statements A - D
CREATE TABLE
CREATE TABLE
The CREATE TABLE statement defines a table. It also defines the locking strategy that
ALLBASE/SQL uses automatically when the table is accessed and in some cases
automatically issues a GRANT statement. It can also define the storage structure of the
table and restrictions or defaults placed on values which the table's columns can hold. You
can also use this statement to assign a table to a partition for audit logging purposes.
Scope
ISQL or Application Programs
SQL Syntax—CREATE TABLE
CREATE [PRIVATE
PUBLICREAD
PUBLIC
PUBLICROW ]TABLE [ Owner. ] TableName
[LANG = TableLanguageName ]
({ ColumnDefinition
UniqueConstraint
ReferentialConstraint
CheckConstraint }[,...])
[UNIQUE HASH ON ( HashColumnName [,...]) PAGES = PrimaryPages
HASH ON CONSTRAINT [ ConstraintID ] PAGES = PrimaryPages
CLUSTERING ON CONSTRAINT [ ConstraintID ]
[IN PARTITION { PartitionName
DEFAULT
NONE }]
[IN DBEFileSetName1 ]
Parameters—CREATE TABLE
PRIVATE enables the table to be used by only one transaction at a time. This is the
most efficient option for tables that do not need to be shared because
ALLBASE/SQL can spend less time managing locks.
This option is in effect by default; grants are not automatically issued.
PUBLICREAD enables the table to be read by concurrent transactions, but allows no more
than one transaction at a time to update the table.
This option automatically issues a statement GRANT SELECT ON
TableName TO PUBLIC. This gives any user with CONNECT authority
the ability to read the table. To change this grant, use the REVOKE
statement and the GRANT statement. The locking strategy remains
unchanged, even if you change the grant.
PUBLIC enables the table to be read and updated by concurrent transactions. In
general, a transaction locks a page in share mode before reading it and in
exclusive mode before updating it.
This option automatically issues the statement GRANT ALL ON
TableName