Precedence and Default Behavior

In Dynamic Server, you do not have to specify the lock mode each time you create a new table. You can globally set the locking granularity of all new tables in the following environments:

The LOCK MODE setting in a CREATE TABLE statement takes precedence over the settings of the IFX_DEF_TABLE_LOCKMODE environment variable and the DEF_TABLE_LOCKMODE configuration parameter.

If CREATE TABLE specifies no lock mode setting, the default mode depends on the setting of the IFX_DEF_TABLE_LOCKMODE environment variable or the DEF_TABLE_LOCKMODE configuration parameter. For information about IFX_DEF_TABLE_LOCKMODE, refer to the IBM Informix Guide to SQL: Reference. For information about the DEF_TABLE_LOCKMODE configuration parameter, refer to the IBM Informix Administrator's Reference.

OF TYPE Clause (IDS)

Use the OF TYPE clause to create a typed table for an object-relational database. A typed table is a table to which you assign a named ROW data type.

OF TYPE Clause:
 
|--OF TYPE--row_type-------------------------------------------->
 
>--+------------------------------------------------------+----->
   |    .-,------------------------------------------.    |
   |    V                                       (1)  |    |
   '-(----| Multiple-Column Constraint Format |------+--)-'
 
                                      (2)
>--+-------------------+--| Options |---------------------------|
   '-UNDER--supertable-'

Notes:

See page Multiple-Column Constraint Format
See page Options Clauses

Element	Description	Restrictions	Syntax
row_type	Name of the ROW type on which this table is based	Must be a named ROW data type registered in the local database	Data Type, p. Data Type; Identifier, p. Identifier
supertable	Name of the table from which this table inherits its properties	Must already exist as a typed table	Database Object Name, p. Database Object Name

If you use the UNDER clause, the row_type must be derived from the ROW type of the supertable. A type hierarchy must already exist in which the named ROW type of the new table is a subtype of the named ROW type of the supertable.

Jagged rows are any set rows from a table hierarchy in which the number of columns is not fixed among the typed tables within the hierarchy. Some APIs, such as ESQL/C and JDBC, do not support queries that return jagged rows.

When you create a typed table, CREATE TABLE cannot specify names for its columns, because the column names were declared when you created the ROW type. Columns of a typed table correspond to the fields of the named ROW type. The ALTER TABLE statement cannot add additional columns to a typed table.

For example, suppose you create a named ROW type, student_t, as follows:

CREATE ROW TYPE student_t
   (name       VARCHAR(30),
    average     REAL,
    birthdate   DATETIME YEAR TO DAY)

If a table is assigned the type student_t, the table is a typed table whose columns are of the same name and data type, and in the same order, as the fields of the type student_t. For example, the following CREATE TABLE statement creates a typed table named students whose type is student_t:

CREATE TABLE students OF TYPE student_t

The students table has the following columns:

name      VARCHAR(30)
average   REAL
birthdate DATETIME

For more information about ROW types, refer to the CREATE ROW TYPE statement on page 1-194.