HP 3000 Manuals HP 3000 Manuals
Details on Creating Tables (DBA)
[REV BEG]
Tables are created with the following statement syntax:
[PRIVATE ]
CREATE [PUBLICREAD] TABLE [Owner.]TableName [LANG = TableLanguageName]
[PUBLIC ]
[PUBLICROW ]
{ColumnDefinition }
( {[{UniqueConstraint } ]} [,...])
{[{ReferentialConstraint} [CONSTRAINT ConstraintID]]}
{[{CheckConstraint } ]}
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]
[HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages ]
[CLUSTERING ON CONSTRAINT [ConstraintID] ]
[IN DBEFileSetName]
[REV END]
Table Type
The first parameter in the CREATE TABLE statement specifies the table
type. If you do not specify a table type, the default is PRIVATE. The
table type option creates a table with the following implied attributes:
* The initial security level of the table:
* PRIVATE (the default type) - gives no authorities to
PUBLIC.
* PUBLICREAD - causes ALLBASE/SQL to perform an implicit
GRANT SELECT TO PUBLIC. This gives any user with CONNECT
authority the authority to look at the table.
[REV BEG]
* PUBLIC and PUBLICROW - causes ALLBASE/SQL to perform an
implicit[REV END] GRANT ALL TO PUBLIC. This gives any user
with CONNECT authority the authority to look at and modify
the table as well as alter the table and create indexes on
it.
* The locking mode of the table:
* PRIVATE (the default type) - causes ALLBASE/SQL to hold
exclusive (X) locks at the table level for both reads and
writes. These locks are easy for ALLBASE/SQL to manage and
unlikely to cause a deadlock condition because each table
is always accessed exclusively by one user. Tables that
must not be accessed by more than one user at a time should
be PRIVATE.
* PUBLICREAD - causes ALLBASE/SQL to hold share (S) locks at
the table level for reads and exclusive (X) locks at the
table level for writes. A table created PUBLICREAD can be
read by several users, which increases concurrency, but can
only be modified by one transaction at a time, which
increases data consistency. Tables that are rarely updated
should be PUBLICREAD.
* PUBLIC - causes ALLBASE/SQL to hold share (S) locks at the
page level for reads (SELECT) and hold exclusive (X) locks
at the page level for writes (INSERT, UPDATE, DELETE). When
locks are obtained at the page level for reads, an
intention share (IS) lock is obtained on the table, and an
S lock is obtained on the page. When locks are obtained at
the page level for reads with an intention to update or
write a row, an intention exclusive (IX) lock is obtained
on the table, and a share and intention exclusive (SIX)
lock is obtained on the page. If a page is actually
written to, the SIX lock must become an X lock.
PUBLIC mode provides higher concurrency than PUBLICREAD and
PRIVATE tables for both reads and writes because a user
does not have to wait for a locked table to get released.
Moderate to large size tables for which you want to
maximize concurrency should be PUBLIC.
[REV BEG]
* PUBLICROW - causes ALLBASE/SQL to hold share (S) locks at
the row level for reads and exclusive (X) locks at the row
level for writes. When locks are obtained at the row level
for reads, an intention share (IS) lock is obtained on the
table and on the page, and an S lock is obtained on the
row. When locks are obtained at the row level for reads
with an intention to update or write a row, an intention
exclusive (IX) lock is obtained on the table and on the
page, and a share and intention exclusive (SIX) lock is
obtained on the row. If a row is actually updated, the SIX
lock must become an X lock.
PUBLICROW provides the greatest concurrency for both reads
and writes because a user does not have to wait for a lock
on a page or table to get released. A side effect of
PUBLICROW mode is the large number of locks that must be
obtained when accessing data from moderate or large size
tables. Locks are obtained at table, page, and row levels
for PUBLICROW tables under normal circumstances. PUBLICROW
mode is often the best choice for a small table that you
expect to be accessed by a large number of concurrent
transactions.[REV END]
ALLBASE/SQL automatically uses the locking mode implicit in the table
type whenever you access that table. You can use the LOCK TABLE
statement to temporarily override this automatic locking behavior. With
LOCK TABLE, you can increase the granularity of locking from page to
table level or from row to table level. However, you cannot decrease the
granularity of locking[REV BEG] from table to page level or from page to
row level by using LOCK TABLE.[REV END][REV BEG]
Revoking and Granting Authorities on PUBLICROW and PUBLIC Tables. [REV
END]
The REVOKE and GRANT statements may be used by the DBA or the table's
owner to change the automatic grant implied at creation time; however,
the locking mode remains unchanged.
[REV BEG]
You can get PUBLIC, PUBLICROW, or PUBLICREAD locking on a table without
the security implications by creating the table PUBLIC, PUBLICROW, or
PUBLICREAD[REV END] and revoking the implied authority from PUBLIC:
isql=> CREATE PUBLIC TABLE SomeTable (SomeColumn...);
isql=> REVOKE ALL ON SomeTable FROM PUBLIC;
Likewise, you can have PRIVATE locking on a table and grant authorization
to PUBLIC:
isql=> CREATE PRIVATE TABLE SomeTable (SomeColumn...);
isql=> GRANT SELECT ON SomeTable TO PUBLIC;
Table locking strategy is discussed in more detail in the chapter
"Concurrency Control Through Locks and Isolation Levels" in the
ALLBASE/SQL Reference Manual.[REV BEG]
Altering Table Type.
You can alter a table's type (lock mode) by using the ALTER TABLE
statement. The following example shows how to change locking mode to
PUBLICROW without changing the authority scheme for the table:
isql=> ALTER TABLE PurchDB.Vendors
> SET TYPE PUBLICROW PRESERVE AUTHORITY;
isql=>
[REV END]