Base tables are tables which contain the user data for a database. A base table is defined with a CREATE TABLE statement and stored permanently in the database until it is deleted with a DROP TABLE statement.
The ALTER TABLE statement allows you to add or delete table constraints to or from an existing base table (see section “Integrity constraint”), to add columns or indexes, or to modify or delete columns.
SESAM/SQL distinguishes the following types of base tables:
tables which can only be processed with SQL (SQL tables)
tables which only contain BLOB objects and can only be processed using SQL (BLOB tables)
tables which can only be processed with CALL DML (CALL DML only tables)
tables which can be processed with CALL DML and, to a limited extent, with SQL (CALL DML/SQL tables)
SQL tables, BLOB tables and CALL-DML/SQL tables can also be created as partitioned tables, see the section “Partitioned table”.
CALL DML only tables and CALL DML/SQL tables are referred to by the term CALL DML tables.
The table style is of significance in the context of the utility statement MIGRATE. The MIGRATE statement is used to convert databases produced with SESAM/SQL V1.1 or an earlier version to base tables of a SESAM/SQL database for the current version.
Certain points must be observed with CALL DML tables: Only the data types CHARACTER, NUMERIC, DECIMAL, INTEGER and SMALLINT are permitted for CALL DML tables. No default value can be defined for the column with DEFAULT.
The only default value which can be specified is the non-significant value in the CALL DML clause. CALL DML tables must have a primary key constraint (see "Integrity constraint") for a simple or compound primary key. The name of the primary key constraint is used as the name of the compound primary key. Other than this primary key constraint, no integrity constraints are permitted.
Storage structure of base tables
When a table is created using CREATE TABLE, storage space which is known as the contiguous area of the table is reserved. The records which are to be inserted are stored in this area. If a record is to be inserted or extended and there is no longer enough space in this area, relocation takes place, i.e. a free block is created. Logically this block belongs to the table, but it is no longer physically contained in the contiguous area of the table. The next time the user space is reorganized using the REORG SPACE statement all the existing tables and indexes are recovered in the user space. The relocations then also disappear.
Converting a base table to a partitioned table
A non-partitioned base table with primary key can be converted to a partitioned table using the utility statement ALTER PARTITIONING FOR TABLE, see section “Changing the partitioning of a base table” and the “ SQL Reference Manual Part 2: Utilities”.