HP 3000 Manuals HP 3000 Manuals
Creating Tables
You can create a table using the following basic syntax:
[PRIVATE ]
CREATE [PUBLICREAD] TABLE [Owner.]TableName
[PUBLIC ]
[PUBLICROW ]
{ColumnDefinition }
[LANG = TableLanguageName] ( {UniqueConstraint } [,...])
{ReferentialConstraint}
{CheckConstraint }
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]
[HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages ]
[CLUSTERING ON CONSTRAINT [ConstraintID] ]
[ {PartitionName}]
[IN PARTITION {DEFAULT }] [IN DBEFileSetName1]
[ {NONE }]
Refer to the CREATE TABLE statement in the ALLBASE/SQL Reference Manual
for complete syntax and semantics.
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.
* PUBLIC and PUBLICROW - causes ALLBASE/SQL to perform an
implicit 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.
* 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.
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 from table to page level or from page to row level
by using LOCK TABLE.
Revoking and Granting Authorities on PUBLICROW and PUBLIC Tables.
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.
You can get PUBLIC, PUBLICROW, or PUBLICREAD locking on a table without
the security implications by creating the table PUBLIC, PUBLICROW, or
PUBLICREAD 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.
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=>
Owner and Table Name
The next parameter in the CREATE TABLE statement, OwnerName, defaults to
the DBEUserID of the user who creates the table. DBA authority is
required to create tables with an owner name other than the default. You
can set the default owner's name to something other than the creator's
DBEUserID by using the ISQL SET OWNER command. Refer to the "Logical
Design" chapter for information on the different types of owners (class
names, DBEUserIDs, or authorization groups) and OWNER authority.
A table name may be up to 20 bytes long in any combination of letters,
digits, $, #, @, or underscore. The first character, however, cannot be
a decimal digit or an underscore. A table name can contain any
characters if it is enclosed in double quotes. However, the name must
then be enclosed in double quotes each time it is referenced, and the
first character following the first double quote cannot be a blank.
Specify the owner name any time you reference a table that is not your
own.
Use the LANG = TableLanguageName clause in the CREATE TABLE statement to
specify a language other than the DBEnvironment's language. For example,
you might wish to specify NATIVE-3000 (ASCII) for a certain table
although the DBEnvironment language is Japanese.
isql=> CREATE TABLE NewTable LANG = "NATIVE-3000"
> (Column1 char(20), Column2 char(10));
You must use double quotes around the name "NATIVE-3000" because it
contains a hyphen. Normally, native language names do not require
quotes.
Column Definition
ColumnName {ColumnDataType } [LANG = ColumnLanguageName]
{LongColumnType [IN DBEFileSetName2]}
[ {Constant }]
[[NOT] CASE SENSITIVE] [DEFAULT {USER }]
[ {NULL }]
[ {CurrentFunction}]
[NOT NULL [{UNIQUE } [CONSTRAINT ConstraintID]] ]
[ [{PRIMARY KEY} ] ]
[ ]
[REFERENCES RefTableName [(RefColumnName)] [CONSTRAINT ConstraintID]]
[[...] ]
[ ]
[CHECK (SearchCondition) [CONSTRAINT ConstraintID] ]
[[IN DBEFileSetName3] ]
[...]
The column definition includes the following parts, of which only the
column name and data type are required:
* Column name
* Data type
* Language clause
* DEFAULT clause
* Constraint definitions
Column Name and Data Type.
A table must have at least one column and each column must be given a
name and a data type. In addition, the NOT NULL attribute, which
disallows null values from being entered in the column, can be assigned.
Several columns in the sample DBEnvironment PartsDBE, including
PartNumber, VendorNumber and OrderNumber, are defined as NOT NULL and
consequently are required to contain data.
Language Clause.
Use the LANG = ColumnLanguageName clause in the CREATE TABLE statement to
specify a column with a language different from that of the default table
language. You can only specify NATIVE-3000 (ASCII) or the current native
language. Example:
isql=> CREATE TABLE NewTable
> (Column1 char(20) LANG = "NATIVE-3000",
> Column2 char(20));
DEFAULT Clause.
Use the DEFAULT clause to specify a default value for a column. Example:
isql=> CREATE TABLE Table5
> (Column1 char(20) DEFAULT 'Empty',
> (Column2 integer NOT NULL);
For further information on data types in creating tables, refer to the
chapters "Names" and "Data Types" in the ALLBASE/SQL Reference Manual.
Constraint Definitions
In creating a table definition, you can include the following types of
integrity constraints:
* Unique constraints
* Referential constraints
* Check constraints
Unique Constraints.
Use the UNIQUE clause to specify a unique constraint on a table. Use the
PRIMARY KEY clause to specify a unique constraint that also defines the
primary key for a table. Example:
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL PRIMARY KEY,
ClubPhone SMALLINT,
Activity CHAR(16))
IN RecFS;
Note that the primary key must be on a column that is NOT NULL.
The difference between a PRIMARY KEY and a UNIQUE constraint is that the
PRIMARY KEY designation lets you reference the key in a referenced table
without specifying column names.
Referential Constraints.
The REFERENCES clause lets you specify the manner in which a referencing
table points to a unique or primary key of another table. You use the
REFERENCES clause within a column definition to define a referential
constraint in which only that column references a key in another table.
Example:
CREATE PUBLIC TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL,
Club CHAR(15) NOT NULL REFERENCES RecDB.Clubs (ClubName),
MemberPhone SMALLINT)) IN RecFS;
This assumes that RecDB.Clubs has already been created with ClubName as a
unique or primary key.
You use REFERENCES along with the FOREIGN KEY clause to define a
referential constraint on multiple columns at the table level. In order
to illustrate this, it is necessary to show an alternate way of creating
the RecDB.Members table, in which MemberName and Club are defined as a
two-column primary key:
CREATE PUBLIC TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL,
Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
PRIMARY KEY (MemberName, Club),
FOREIGN KEY (Club) REFERENCES RecDB.Clubs (ClubName)) IN RecFS;
Based on this referenced table, we can define the RecDB.Events table as
follows with a two-column foreign key:
CREATE PUBLIC TABLE RecDB.Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE,
Time TIME,
Coordinator CHAR(20),
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members (MemberName, Club)) IN RecFS;
Note that since (MemberName, Club) is specified as the PRIMARY KEY for
RecDB.Members, the use of the column names in the REFERENCES clause of
the example is optional.
Check Constraints.
The following example shows a table created with a check constraint. The
check constraint ensures that the Date column will not be updated with a
date earlier than January 1, 1990.
CREATE PUBLIC TABLE RecDB.Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE,
Time TIME,
Coordinator CHAR(20),
CHECK (Date >= '1990-01-01') )
IN RecFS
For more information about integrity constraints, see the chapter
"Constraints, Procedures, and Rules" in the ALLBASE/SQL Reference Manual.
DBEFileSet Name
The last parameter in the CREATE TABLE statement specifies the DBEFileSet
with which the table and its indexes are associated. If you do not
specify a DBEFileSet and you have not assigned a default DBEFileSet with
the SET DEFAULT DBEFILESET statement, tables are created in the SYSTEM
DBEFileSet, which also contains the system catalog. It is recommended
practice to keep the system catalog apart from your data and index files.
Refer to the "Physical Design" chapter under "Grouping Tables in
DBEFileSets" for information on why you might wish to create separate
DBEFileSets for tables.
In addition, two parameters in the column definition syntax allow
specification of a DBEFileSet for long column data and for check
constraint sections.
Examining Table Attributes
After creating tables, you can query the SYSTEM.TABLE and SYSTEM.COLUMN
views to see how their definitions appear. The following query on
SYSTEM.TABLE will display information about all the tables in the PurchDB
database:
isql=> SELECT * FROM System.Table WHERE Owner='PURCHDB';
SELECT * FROM System.Table WHERE Owner='PURCHDB';
--------------------+--------------------+--------------------+------+--
NAME |OWNER |DBEFILESET |TYPE |RT
--------------------+--------------------+--------------------+------+--
INVENTORY |PURCHDB |WAREHFS | 0|
ORDERITEMS |PURCHDB |ORDERFS | 0|
ORDERS |PURCHDB |ORDERFS | 0|
PARTINFO |PURCHDB |SYSTEM | 1|
PARTS |PURCHDB |WAREHFS | 0|
REPORTS |PURCHDB |ORDERFS | 0|
SUPPLYPRICE |PURCHDB |PURCHFS | 0|
VENDORS |PURCHDB |PURCHFS | 0|
VENDORSTATISTICS |PURCHDB |SYSTEM | 1|
------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
You can also use the Static subsystem of SQLMON to see which tables are
contained in a DBEFileset. For more information on SQLMON, see the
ALLBASE/SQL Performance and Monitoring Guidelines.
You must use an additional query of the system catalog to display the
column definitions of particular tables:
isql=> select * from system.column
> where owner= 'PURCHDB' and tablename = 'PARTS';
The query result is as follows:
select * from system.column where owner= 'PURCHDB' and tablename = 'PARTS';
--------------------+--------------------+--------------------+-----------+
COLNAME |TABLENAME |OWNER |COLNUM |
--------------------+--------------------+--------------------+-----------+
PARTNUMBER |PARTS |PURCHDB | 1|
PARTNAME |PARTS |PURCHDB | 2|
SALESPRICE |PARTS |PURCHDB | 3|
---------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
You can also use the ISQL INFO command to display column definitions for
a table:
isql=> INFO PURCHDB.PARTS;
Column Name Data Type (length) Nulls Allowed Language
----------------------------------------------------------------------------
PARTNUMBER Char ( 16) NO NATIVE-3000
PARTNAME Char ( 30) YES NATIVE-3000
SALESPRICE Decimal ( 10, 2) YES
The CREATE TABLE statement only enters the table definition into the
system catalog. The table does not occupy storage in the DBEFileSet
until you insert a row.
All names that are stored as character strings in the system catalog are
upshifted when they are stored unless they are in double quotes. For
example, the statement
isql=> CREATE PUBLIC TABLE PurchDB.SomeTable
> (Column1 INTEGER, Column2 INTEGER) in FS;
would store PURCHDB as the owner and SOMETABLE as the table name in the
system catalog, whereas the statement
isql=> CREATE PUBLIC TABLE "PurchDB"."SomeTable"
> (Column1 INTEGER, Column2 INTEGER) in FS;
would store PurchDB as the owner and SomeTable as the table name. To
examine the attributes of the first, you would use the following
statement:
isql=> SELECT * FROM SYSTEM.TABLE
> WHERE NAME = 'SOMETABLE' AND OWNER = 'PURCHDB';
For the second, you would use lower case spelling:
isql=> SELECT * FROM SYSTEM.TABLE
> WHERE NAME = 'SomeTable' AND OWNER = 'PurchDB';
To eliminate any possible confusion, avoid using double quotes in
defining objects.
Defining Partitions and Tables
To create partitions, you use the CREATE PARTITION statement. Then, to
assign a table to a partition, you can use the IN PARTITION parameter of
the CREATE TABLE statement or the SET PARTITION parameter of the ALTER
TABLE statement. If you do not assign a table to a partition, the table
is assigned to the DEFAULT partition.
If you do not want audit logging done on the table, you can specify NONE
with either IN PARTITION or SET PARTITION; then, operations on the table
do not generate audit log records.
If you assign a table to a partition, and if you specified DATA AUDIT
ELEMENTS when you started the DBEnvironment (either explicitly or by
default), then any inserts, updates, or deletes you perform on the table
generate audit log records.
Partitions are dropped with the DROP PARTITION statement. Before you can
drop a partition, you must assign each of its tables to a new partition
with the statement ALTER TABLE SET PARTITION.
Example
In the following example, the IN PARTITION clause of the CREATE TABLE
statement is used to assign a table to the already created partition, P1:
CREATE PARTITION P1 with ID = 1;
CREATE PUBLIC TABLE table1 (Col1 integer not null,
Col2 char(12) not null,
Col3 integer not null)
IN PARTITION P1
IN FileSetA;
Refer to the CREATE TABLE statement and ALTER TABLE statement syntax in
the "SQL Statements" chapter of the ALLBASE/SQL Reference Manual.