Creating Tables

You can create a table using the following basic syntax:

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]

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 SECT "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 LongColumnType [IN DBEFileSetName2] } [LANG = ColumnLanguageName] [ [NOT] CASE SENSITIVE ] [ DEFAULT { Constant USER NULL CurrentFunction }] [ NOT NULL { UNIQUE PRIMARY KEY } [CONSTRAINT ConstraintID] REFERENCESRefTableName [(RefColumnName] [CONSTRAINTConstraintID] [...] CHECKSearchCondition [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.