You use CREATE TABLE to create a base table in which the data is permanently stored.
SESAM/SQL distinguishes between
SQL tables that can only be processed with SQL
BLOB tables that only contain BLOBs
CALL DML/SQL tables that can be processed with CALL DML and to some extent with SQL
CALL DML only tables that can only be processed with CALL DML. These CALL DML tables cannot be created with CREATE TABLE. They are created with the MIGRATE statement (see the “ SQL Reference Manual Part 2: Utilities”).
SQL tables, BLOB tables and CALL-DML/SQL tables can also be created as partitioned tables. A partitioned table is a base table whose data is stored in a number of spaces. The table data contained in a single space is referred to as a partition. In SESAM/SQL the data is distributed row by row to the partitions, and the assignment criterion is the primary key value. See also the section "Special features for partitioned tables". The partitioning can be changed with the utility statement ALTER PARTITIONING FOR TABLE, see the “ SQL Reference Manual Part 2: Utilities”.
CALL DML only tables and CALL DML/SQL tables are referred to by the term CALL DML tables.
The restrictions that apply when you use CREATE TABLE to create CALL DML tables are described in the section "Special considerations for CALL DML tables".
The structure of BLOB tables is described in the section "Special considerations for BLOB tables".
The current authorization identifier must own the schema. If you specify the space for the base table, the current authorization identifier must own the space.
CREATE [CALL DML] TABLE table
{ ( declaration ...) | OF BLOB ( blob-declaration ) }
[USING { SPACE space | PARTITION BY RANGE partition ,..., last_partition }]
declaration ::=
{ column_definition | [CONSTRAINT integrity_constraint_name ] table_constraint }
blob-declaration ::=
{ mime_clause [, usage_clause ][, alphanumeric_literal ] |
usage_clause [, alphanumeric_literal ] |
alphanumeric_literal }
mime_clause ::= MIME( alphanumeric_literal )
usage_clause ::= USAGE( alphanumeric_literal )
partition ::= PARTITION partno VALUE {< | <=} ( column_value ,...) ON SPACE space
last_partition ::= PARTITION partno [VALUE <=( )] ON SPACE space
partno ::= unsigned_integer
column_value ::=
{ alphanumeric_literal |
national_literal |
numeric_literal |
time_literal }
CALL DML
Creates a CALL DML table.
You can only process CALL DML tables with SESAM CALL DML. The column definitions and integrity conditions must observe certain restrictions (see "Special considerations for CALL DML tables").
CALL DML omitted:
An SQL or BLOB table is created.
SQL tables can only be processed with SQL. BLOB tables can only be processed with SESAM CLI calls (see chapter "SESAM-CLI").
TABLE table
Name of the new base table. The unqualified table name must be different from all the base table names and view names in the schema. You can qualify the table name with a database and schema name.
If you use the CREATE TABLE statement in a CREATE SCHEMA statement, you can only qualify the table name with the database and schema name from the CREATE SCHEMA statement.
column_definition
Defines columns for the base table.
You must define at least one column. A base table can have up to 26 134 columns of any data type except VARCHAR and NVARCHAR and up to 1000 columns of the data type VARCHAR and/or NVARCHAR.
The current authorization identifier is granted all table privileges for the defined columns.
CONSTRAINT integrity_constraint_name
Assigns an integrity constraint name to the table constraint. The unqualified name of the integrity constraint must be unique within the schema. You can qualify the name of the integrity constraint with a database and schema name. The database and schema name must be the same as the database and schema name of the base table for which the integrity condition is defined.
CONSTRAINT integrity_constraint_name omitted:
The integrity constraint is assigned a name according to the following pattern:
UN integrity_constraint_number
PK integrity_constraint_number
FK integrity_constraint_number
CH integrity_constraint_number
where UN stands for UNIQUE, PK for PRIMARY KEY, FK for FOREIGN KEY and CH for CHECK. integrity_constraint_number is a 16-digit number.
table_constraint
Defines an integrity constraint for the base table.
OF BLOB
Creates a BLOB table.
mime_clause
Allows you to define the MIME type. For instance, the MIME type of a Microsoft TM Word document is “application/msword”. If the BLOB table is defined without mime_clause, the default MIME type “application/octet-stream” is set. You must ensure that only permitted MIME types are specified in mime_clause. A list of the most important MIME types can be found under http://www.iana.org/assignments/media-types/index.html.
usage_clause
Allows you to define comments for BLOBs (see example at the end of this section). The default value is a blank.
alphanumeric_literal
In addition to the format described in the appendix, alphanumeric_literal must be in XML format (see examples).
USING clause
The USING clause defines whether a non-partitioned (USING SPACE) or a partitioned (USING PARTITION BY RANGE) table is created.
USING SPACE space
Name of the space in which that table is to be stored. The space must already be defined for the database to which the table belongs. You can qualify the space name with the database name. This database name must be the same as the database name of the base table.
USING PARTITION BY RANGE partition, ... ,last_partition
Specifies that a partitioned table is to be created. The table must consist of at least 2 and at most 16 partitions. All partitions in a table must be located in different spaces, and all spaces must already be defined for the database. The table must have a primary key; this can be a single column or a combination of multiple columns.
partition clause
Defines a partition’s properties.
partno is a an unsigned integer from 1 ... 16 and is the partition’s current number partno must be assigned in ascending order for the individual partitions. If less than 16 partitions are defined, the series of numbers can contain gaps, and the first partition need not begin with 1.
( column_value,....) is a sequence of column values which defines the upper limit of the primary key interval for the partition concerned. You must always specify at least one column value, but at most as many column values as columns are contained in the primary key. The data type and value of column_value must match the data type of the corresponding column of the primary key; the same rules apply as for default values (see the section "Default values for table columns").
The upper limit is either included or excluded by the preceding comparison operator:
<= | Records whose primary key value is column_value,... or whose primary |
< | Records whose primary key value is equal to column_value,... or whose |
The lexicographical rules apply for the comparison, see the section
"Comparison rules".
The upper limits specified must be strictly in ascending order for the individual partitions.
The lower limit for the partition results implicitly from the upper limit of the preceding partition or from the lowest pimary key value in the table (in the first partition). All records from the primary key interval defined in this way belong to this partition.
space specifies the name of the space in which this partition is stored. The space must exist and the space owner must also be the schema owner. The spaces of a partitioned table must be disjunctive, i.e. a space may not be used for two partitions of the same table.
last_partition clause
The same conditions apply for the last partition as for partition.
Only the upper limit may not be specified since it is determined here from the highest primary key value. The VALUE clause can therefore also be omitted.
USING omitted:
A non-partitioned table is created in the current schema owner’s default space and stored on the storage group D0STOGROUP.
The default space is D0authorization_identifier with the first 10 characters of the authorization identifier. If this space does not yet exist, it is created if the current authorization identifier has been granted the special privilege USAGE for the storage group D0STOGROUP.
Special considerations for CALL DML tables
The CREATE TABLE statement for CALL DML tables must take the following restrictions into account:
Only the data types CHAR, NUMERIC, DECIMAL, INTEGER and SMALLINT are permitted.
No default value can be defined for the column with DEFAULT.
A column that is not a primary key must have a CALL DML clause.
The table must contain exactly one primary key restraint as the column or table constraint.
The table constraint defines a compound primary key and must be given a name that corresponds to the name of the compound primary key in SESAM/SQL V1.x.
The column name must be different from the integrity constraint name of the table constraint since this name is used as the name of the compound primary key.
The following rules apply for the SAN (symbolic attribute name):
precisely 3 characters
first character: alphabetic character; second and third characters: alphabetic or numeric characters
not allowed: 0, I, O;
the combinations NAM and END are likewise not allowed.
Special considerations for BLOB tables
In SESAM/SQL, BLOB tables are used as storage locations for BLOBs (Binary Large Objects). BLOB objects are byte chains of variable length, up to a maximum of 2 31-1 bytes. With the help of SESAM CLI calls, BLOB values are stored piecemeal in several rows of the BLOB table. The structure of this table will have already been defined using the statement CREATE TABLE table OF BLOB. Columns cannot be defined at this point.
A BLOB table consists of the following columns:
The OBJ_NR column is of data type INTEGER and contains the serial number of the BLOB within the table.
The SLICE_NR column is of data type INTEGER and contains the serial number of a particular segment.
The SLICE_VAL column is of type VARCHAR(31000). It contains the individual components of the BLOB value. Beginning with slice number 1, the BLOB value is specified in segments of 31 KB in length. Obviously, the last segment may be shorter than this. The row containing slice number 0 is used to store administrative information on the BLOB. The default settings for this column are defined in the attributes of the OF BLOB clause. In addition to these, they also include the CREATED and UPDATED attributes. This attributes specify the date on which the BLOB was created and last updated.
The OBJ_REF column is of type CHAR(237). In the row containing slice number 0, it specifies the REF value of the BLOB. Otherwise, the column value is NULL. By default, this column is assigned the REF value for this table’s class and is defined with the UNIQUE constraint.
The OBJ_NR and SLICE_NR columns together form the primary key of a BLOB table. For this primary key constraint, names generated internally are assigned as normal and must not be used elsewhere in the same schema.
It is possible for the user to append columns using ALTER TABLE. (However, it must be ensured that the default value for these additional columns is the NULL value.)
The mime_clause, usage_clause and alphanumeric_literal in the CREATE TABLE...OF BLOB statement are used to add attributes that describe the BLOB. The total length of all attributes must not exceed 256 bytes.
BLOB values can be incorporated in regular base tables with the help of the REF column (see section "Column definitions").
Special features for partitioned tables
A partitioned table behaves largely like a non-partitioned table, i.e. the columns, constraints, indexes, and default values relate to all partitions.
As the partition limits are defined with the aid of the primary key, you should observe the following when you create the partitioned table:
You can change the partition limits of a partitioned table after it has been created using ALTER PARTITIONING FOR TABLE. You can also use the utility statements EXPORT TABLE and IMPORT TABLE to create a table with modified partition limits.
After a record has been inserted in a partitioned table it is no longer possible to change its primary key value with the UPDATE statement. However, the record can be deleted and reinserted with a new primary key value.
For BLOBs the primary key consists of the OBJ_NR and SLICE_NR columns. The object number is generated in the CLI call SQL_BLOB_OBJ_CREATE or SQL_BLOB_OBJ_CREAT2. These two calls have different characteristics:
With SQL_BLOB_OBJ_CREATE ( "SQL_BLOB_OBJ_CREATE - SQLbocr") the object number is assigned in ascending serial order.
With SQL_BLOB_OBJ_CREAT2 ( "SQL_BLOB_OBJ_CREAT2 - SQLboc2") you specify an object number range. The BLOB’s object number is then assigned by SESAM/SQL within this range and also distributed equally within this range. It therefore makes sense to match the partition limits to the object number ranges.
Further information on partitioned tables and usage scenarios is provided in the “ Core manual”.
Examples
This example shows the CREATE TABLE statement for the non-partitioned table ORDERS of the demonstration database.
CREATE TABLE orders
(order_num INTEGER CONSTRAINT order_num_primary PRIMARY KEY,
cust_num INTEGER CONSTRAINT o_cust_num_notnull NOT NULL
CONSTRAINT o_cust_num_ref_customers
REFERENCES customers(cust_num),
contact_num INTEGER
CONSTRAINT contact_num_ref_contacts
REFERENCES contacts(contact_num),
order_date DATE DEFAULT CURRENT_DATE,
order_text CHARACTER (30),
actual DATE,
target DATE,
order_stat INTEGER DEFAULT 1 CONSTRAINT order_stat_notnull NOT NULL
CONSTRAINT order_stat_ref_ordstat
REFERENCES ordstat(ord_stat_num)
)
USING SPACE tablespace
This example shows a corresponding CREATE TABLE statement for the ORDERS table of the demonstration database as a partitioned table.
CREATE TABLE orders
(order_num INTEGER CONSTRAINT order_num_primary PRIMARY KEY,
cust_num INTEGER CONSTRAINT o_cust_num_notnull NOT NULL
CONSTRAINT o_cust_num_ref_customers
REFERENCES customers(cust_num),
contact_num INTEGER
CONSTRAINT contact_num_ref_contacts
REFERENCES contacts(contact_num),
order_date DATE DEFAULT CURRENT_DATE,
order_text CHARACTER (30),
actual DATE,
target DATE,
order_stat INTEGER DEFAULT 1 CONSTRAINT order_stat_notnull NOT NULL
CONSTRAINT order_stat_ref_ordstat
REFERENCES ordstat(ord_stat_num)
)
USING PARTITION BY RANGE
PARTITION 02 VALUE <= (299) ON SPACE tablespace,
PARTITION 03 VALUE <= (399) ON SPACE tablesp002,
PARTITION 09 ON SPACE tablesp003
This example shows the CREATE TABLE statement for the partitioned table ADDRESS. The data is split lexicographically into 5 partitions: A through D, E through K, L through O, P through SCH and SCI through Z. The primary key consists of three columns, only the first column being used to determine the partition limits.
CREATE TABLE address
(name CHARACTER (40), first_name CHARACTER (40), pers_no INTEGER, ...
PRIMARY KEY (name, first_name, pers_no))
USING PARTITION BY RANGE
PARTITION 01 VALUE < ('E') ON SPACE adr01,
PARTITION 02 VALUE < ('L') ON SPACE adr02,
PARTITION 03 VALUE < ('P') ON SPACE adr03,
PARTITION 04 VALUE < ('SCI') ON SPACE adr04,
PARTITION 05 ON SPACE adr05
This example shows the CREATE TABLE statement for the CALL DML table COMPANY in the COMPANYSCH schema of the CALLCOMPANY database (see the “ CALL-DM Applications” manual).
CREATE CALL DML TABLE callcompany.companysch.company
(pkey CHARACTER(006) PRIMARY KEY,
aname CHARACTER(015) CALL DML ' ' AA8,
aprice NUMERIC(05,02) CALL DML -0 AB6,
astock NUMERIC(04) CALL DML -0 AC4,
clastname CHARACTER(015) CALL DML ' ' AD2,
cfirstname CHARACTER(012) CALL DML ' ' AEZ,
cstreet CHARACTER(015) CALL DML ' ' AFX,
czip CHARACTER(005) CALL DML ' ' AGV,
ccity CHARACTER(015) CALL DML ' ' AHT,
ksince CHARACTER(006) CALL DML ' ' AJR,
krabatt NUMERIC(04,02) CALL DML 0 AKP,
...
psalary(010) NUMERIC(07,02) CALL DML 0 AT5)
USING SPACE CALLCOMPANY.COMPANY
The tables IMAGES and DESCRIPTIONS are defined in the ADDONS schema. Both tables are stored in the space BLOBSPACE. While the BLOB table contains images in gif format, the DESCRIPTIONS table contains texts for these images in the form of Word documents.
CREATE TABLE addons.images OF BLOB
MIME('image / gif'),
USAGE ('images for parts.item_cat.image'),
'<Photographer>Hans Sesamer</Photographer>')
USING SPACE blobspace
CREATE TABLE descriptions OF BLOB
( MIME ('application / msword'),
USAGE ('word documents for parts.item_cat.desc'),
'<AUTHOR>Herta Sesamer</AUTHOR>')
USING SPACE blobspace
This example shows the CREATE TABLE statement for the partitioned BLOB table BILL. This table contains bills in the form of Word files. The bills are distributed over the individual partitions according to the quarters of a year.
CREATE TABLE bill OF BLOB (MIME ('application/msword'),
USING PARTITION BY RANGE
PARTITION 01 VALUE <= (1000000) ON SPACE quarter01,
PARTITION 02 VALUE <= (2000000) ON SPACE quarter02,
PARTITION 03 VALUE <= (3000000) ON SPACE quarter03,
PARTITION 04 ON SPACE quarter04
A bill is generated with the CLI function SQL_BLOB_OBJ_CREAT2. Here the object number range of the bill (min_no, max_no) is selected in such a way that the bill is stored in the quarter associated with the partition:
SQL_BLOB_OBJ_CREAT2(&ref, &catalogId, &minObjNr, &MaxObjNr, &SQLdiag);
This example shows the CREATE TABLE statement for the MANUALS table in the sample
CREATE TABLE manuals (ord_num INTEGER, language NCHAR(20), title NCHAR(30) )
See also
ALTER TABLE, CREATE SCHEMA, CREATE SPACE