You can use CREATE TABLE to create untyped tables (traditional relational- database tables), typed tables (object-relational tables), typed tables with inheritance, or temporary tables.

Syntax

Element	Purpose	Restrictions	Syntax
table name	The name assigned to the table. Every table must have a name.	Name must be unique within a database. It must not be used for any other tables or for any views or synonyms within the current database.	Identifier, p. 1-966

Syntax Clauses for Typed and Untyped Tables

Element	Purpose	Restrictions	Syntax
row type name	The name of row type used as this table's type	This type must already exist and must be a named row type.	Data type, p.1-859 Identifier, p. 1-966 Informix Guide to SQL: Syntax: Chapter 3, "Environment Variables"
supertable name	The name of the parent table of which this table is a child.	This parent table must already exist as a typed table. A type hierarchy must already exist in which the named row type of this table is a subtype of the named row type of the supertable.

Usage

ANSI

In an ANSI-compliant database, the combination owner.tablename must be unique within the database.

DB

In DB-Access, using the CREATE TABLE statement outside the CREATE SCHEMA statement generates warnings if you set DBANSIWARN.

ESQL

The CREATE TABLE statement generates warnings if you use the -ansi flag or set DBANSIWARN environment variable.

For information about DBANSIWARN environment variable, refer to the Informix Guide to SQL: Syntax.

Typed and Untyped Tables

Important: Informix recommends that you use the BLOB or CLOB data types instead of the TEXT or BYTE data types when you create a typed table that contains columns for large objects. For backward compatibility, you can create a named row type that contains TEXT or BYTE fields and use that type to recreate an existing (untyped) table as a typed table. However, although you can use a row type that contains BYTE or TEXT fields to create a typed table, you cannot use such a row type as a column. You can use a row type that contains CLOB or BLOB fields in both typed tables and columns.

Typed Tables

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)

The following CREATE TABLE statement creates a typed table named students whose type is student_t:

CREATE TABLE students OF TYPE student_t

name			VARCHAR(30)
average			REAL
birthdate			DATETIME

Important: Typed tables do not take the default values or null/not null specification of the row type whose type they adopt.

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

Typed Tables with Inheritance

Continuing the example shown in "Typed Tables", the following statements created a typed table, grad_students, that inherits all of the columns of the students table and in addition has columns for adviser and field_of_study:

CREATE ROW TYPE grad_student_t 
	(adviser				CHAR(25),
	 field_of_study				CHAR(40))
	 UNDER student_t;

CREATE TABLE grad_students OF TYPE grad_student_t 
	UNDER students;

• All columns in the immediate supertable
• All constraint definitions defined on its supertable
• Fragmentation. If a subtable does not define fragments, and if its supertable has fragments defined, then the subtable inherits the fragments of the supertable.
• All indexes defined by its supertable
• Referential integrity
• The access method
• The WITH options
• The storage option
• All triggers defined on the supertable

Tip: Any heritable attributes that are added to a supertable after subtables have been created will automatically be inherited by existing subtables. It is not necessary to add all heritable attributes to a supertable before creating its subtables.

Constraints, indices, and triggers are recorded in the system catalog for the supertable, but not for subtables that inherit them. Fragmentation information is recorded for both supertables and subtables.

No two tables in a table hierarchy can have the same type. For example, the final line of the following code sample is illegal because the tables tab2 and tab3 cannot have the same row type (rowtype2):

			create row type rowtype1 (...);
			create row type rowtype2 (...) under rowtype1;
			create table tab1 of type rowtype1;
			create table tab2 of type rowtype2 under tab1;
Illegal -->			create table tab3 of type rowtype2 under tab1;

Untyped Tables

The following CREATE TABLE statement creates an untyped table:

CREATE TABLE students
	(name			VARCHAR(30),
	 average			REAL, 
	 birthdate			DATETIME YEAR TO DAY)

CREATE TEMP TABLE transient
	(col1		integer,
	 col2		char(20))

Temporary tables that you create with the CREATE TEMP TABLE statement are explicit temporary tables. You can also create explicit temporary tables with the SELECT ... INTO TEMP statement. Temporary tables that the database server creates as a part of processing are called implicit temporary tables. Implicit temporary tables are discussed in the INFORMIX-Universal Server Administrator's Guide.

When an application creates an explicit temporary table, the table exists until one of the following situations occur:

• The application terminates.
• The application closes the database where the table was created. In this case, the table is dropped only if the database does transaction logging, and the temporary table was not created with the WITH NO LOG option.
• The application closes the database where the table was created and opens a database in a different database server.

DB

You cannot use the INFO statement and the Info Menu Option with temporary tables.

Temporary table names must be different from existing table, view, or synonym names in the current database. However, they need not be different from other temporary table names used by other users.

You can specify where temporary tables are created with the CREATE TEMP TABLE statement, environment variables, and ONCONFIG parameters. If you do not specify a storage location, the temporary tables are created in the same dbspace as the database. The database server stores temporary tables in the following order:

1. The IN dbspace clause

You can specify the dbspace where you want the temporary table stored with the IN dbspace clause of the CREATE TABLE statement.

2. The dbspaces you specify when you fragment temporary tables

Use the FRAGMENT BY clause of the CREATE TABLE statement to fragment regular and temporary tables.

3. The DBSPACETEMP environment variable

The DBSPACETEMP environment variable lists dbspaces where temporary tables can be stored. This list can include standard dbspaces, temporary dbspaces, or both. If the environment variable is set, the database server assigns each temporary table to a dbspace in round-robin sequence.

4. The ONCONFIG parameter DBSPACETEMP

You can specify a location for temporary tables with the ONCONFIG parameter DBSPACETEMP.

Tip: Use the PUT clause to specify a separate storage area for smart large objects.

For additional information about the DBSPACETEMP environment variable, see Chapter 3 in the Informix Guide to SQL: Syntax. For additional information about the ONCONFIG parameter DBSPACETEMP, see the INFORMIX-Universal Server Administrator's Guide.

Differences Among Tables

Privileges on Tables

ANSI

In an ANSI-compliant database, no default table-level privileges exist. You must grant these privileges explicitly.

When set to yes, the environment variable NODEFDAC prevents default privileges on a new table in a database that is not ANSI compliant from being granted to PUBLIC. For information about preventing privileges from being granted to PUBLIC, see the NODEFDAC environment variable in the Informix Guide to SQL: Syntax.

System Catalog Information

The systabauth, syscolauth, sysfragauth, sysprocauth, sysusers, and sysxtdtypeauth tables contain information about the privileges required for various CREATE TABLE options. The systables, sysxtdtypes, and sysinherits system catalog tables provide information about table types.

For information about the system catalog tables, refer to the Informix Guide to SQL: Syntax. For information about sysmaster database, refer to the INFORMIX-Universal Server Administrator's Guide.

Column Definition Clause

Element	Purpose	Restrictions	Syntax
column name	The name of a column in the permanent table	Name must be unique within a table, but you can use the same names in different tables in the same database.	Identifier, p. 1-966

The Untyped Table clause on page 1-213 refers to the Column Definition Clause.

DEFAULT Clause

Element	Purpose	Restrictions	Syntax
literal	A literal term that defines alphabetic or numeric constant characters to be used as the default value for the column	Term must be appropriate type for the column. See "Literal Terms as Default Values".	Expression, p. 1-880

The column definition clause on page 1-222 refers to DEFAULT clause.

Important: If you use a named row type as one of the columns in an untyped table, the table does not adopt any constraints of the named row.

Literal Terms as Default Values

Characters must be enclosed in quotation marks. Date literals must be of the format specified with the DBDATE environment variable. If DBDATE is not set, the format mm/dd/yyyy is assumed.

Opaque data types support only string literals for default values. The default value must be specified at the column level and not at the table level.

For information on using a literal INTERVAL, refer to the Literal INTERVAL segment on page 1-998. For more information on using a literal DATETIME, refer to the Literal DATETIME segment on page 1-995.

NULL as the Default Value

If you designate NULL as the default value for a column, you cannot specify a not null constraint as part of the column definition.

If the column is TEXT or BYTE data type, you can designate only NULL as the default value.

Data Type Requirements for Certain Columns

Limitations on Default Values

You cannot designate a server-defined function (that is, CURRENT, USER, TODAY, SITENAME or DBSERVERNAME) as the default value for opaque or distinct data types.

You cannot designate NULL as a default value for a column that is part of a primary key.

Examples of Default Values in Column Definitions

CREATE TABLE accounts (

	acc_num INTEGER DEFAULT 0001,

	acc_type CHAR(1) DEFAULT 'A',

	acc_descr CHAR(20) DEFAULT 'New Account',

	acc_id CHAR(8) DEFAULT USER)

CREATE TABLE newitems (

	newitem_num			INTEGER,

	manucode			CHAR(3) NOT NULL,

	promotype			INTEGER,

	descrip			CHAR(20)) 

Constraints

Important: In a database without logging, detached checking is the only kind of constraint checking available. Detached checking means that constraint checking is performed on a row-by-row basis.

Limits on Constraint Definitions

You cannot place a constraint on a violations or diagnostics table. For further information on violations and diagnostics tables, see the START VIOLATIONS TABLE statement on page 1-748.

Restrictions for Unique Constraints

When you define a unique constraint (UNIQUE or DISTINCT keywords), a column cannot appear in the constraint list more than once. You cannot place a unique constraint on a column on which you have already placed a primary-key constraint. You cannot place a unique constraint on a BYTE or TEXT column.

Opaque types support a unique constraint only where there is a secondary access method that supports the uniqueness for that type. The built-in (default) secondary access method is a generic B-tree, which supports the equal() function. Therefore, if the definition of the opaque type includes the equal() function, a column of that opaque type can have a unique constraint.

Restrictions for Primary-Key Constraints

You cannot define a primary-key constraint on a BYTE or TEXT column.

Opaque types support a primary key constraint only where there is a secondary access method that supports the uniqueness for that type. The built-in secondary access method is a generic B-tree, which supports the equal() function. Therefore, if the definition of the opaque type includes the equal() function, a column of that opaque type can have a primary key constraint

Restrictions for Referential Constraints

You must have the REFERENCES privilege to create a referential constraint.

Adding or Dropping Constraints

Enforcing Primary-Key, Unique, and Referential Constraints

• Creates a unique, ascending index for a unique or primary-key constraint
• Creates a nonunique, ascending index for the columns specified in the referential constraint

Because these constraints are enforced through indexes, you cannot create an index (using the CREATE INDEX statement) for a column that is of the same type as the constraint placed on that column. For example, if a unique constraint exists on a column, you can create neither an ascending unique index for that column nor a duplicate ascending index.

Constraint Names

The database server adds a row to the sysconstraints system catalog table for each constraint. If you do not specify a constraint name, the database server generates a constraint name using the following template:

<constraint_type><tabid>_<constraintid> 

ANSI

When you create a constraint of any type, the owner.constraint_name (the combination of the owner name and constraint name) must be unique within the database.

In addition, the database server adds a row to the sysindices system catalog table for each new primary-key, unique, or referential constraint that does not share an index with an existing constraint. The index name in sysindices is created with the following format:

[space]<tabid>_<constraintid>

Using Simple Large Object Types in Constraints

Restrictions on Temporary Table Constraints

• You cannot place referential constraints on columns in a temporary table. Temporary columns cannot be referenced or referencing columns.
• You cannot assign a name to a constraint on a temporary-table column.
• You cannot set the constraint mode on a temporary-table column. (See "Constraint Mode Definition" for information on this option.)

The following example creates a simple table with two constraints, a primary-key constraint named num on the acc_num column and a unique constraint named code on the acc_code column:

CREATE TABLE accounts (

	acc_num			INTEGER PRIMARY KEY CONSTRAINT num,

	acc_code 			INTEGER UNIQUE CONSTRAINT code,

	acc_descr 			CHAR(30))

Table-Level Constraint Definition

Using the UNIQUE and DISTINCT Keywords

When you define a unique constraint (UNIQUE or DISTINCT keywords), a column cannot appear in the constraint list more than once. You cannot place a unique constraint on a column on which you have already placed a primary-key constraint. You cannot place a unique constraint on a BYTE or TEXT column.

Each column named in a unique constraint must be a column in the table and cannot appear in the constraint list more than once.

The following example creates a simple table that has a unique constraint on one of its columns:

CREATE TABLE accounts

 	(acc_name			CHAR(12), 
	 acc_num			SERIAL UNIQUE CONSTRAINT acc_num) 

CREATE TABLE accounts
	(acc_name			CHAR(12), 
	 acc_num			SERIAL, 
	 UNIQUE			(acc_num) CONSTRAINT acc_num) 

In the previous two examples, a unique constraint was placed on the column acc_name. The following example creates this column as the primary key for the accounts table:

CREATE TABLE accounts

 	(acc_name			CHAR(12), 
	 acc_num			SERIAL, 
	 PRIMARY KEY (acc_num)) 

When you use FOREIGN KEY keywords, you must use the REFERENCES clause, page 1-235, to complete the foreign key dependencies.

CHECK Clause

Check constraints are defined using search conditions. The search condition cannot contain subqueries; aggregates; host variables; rowids; the CURRENT, USER, SITENAME, DBSERVERNAME, or TODAY functions; or stored procedure calls.

Warning: When you specify a date value in a search condition, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on how the database server interprets the search condition. When you specify a 2-digit year, the DBCENTURY environment variable can affect how the database server interprets the search condition, so the check constraint might not work as you intended. See the "Informix Guide to SQL: Syntax" for more information on the DBCENTURY environment variable.

The Column-Level Constraint definition on page 1-230 and the Table-Level Constraint definition on page 1-231 refer to the CHECK clause.

Defining Check Constraints at the Column Level

CREATE TABLE my_accounts (

	chk_id			SERIAL PRIMARY KEY,

	acct1			MONEY CHECK (acct1 BETWEEN 0 AND 99999),
	acct2			MONEY CHECK (acct2 BETWEEN 0 AND 99999))

Defining Check Constraints at the Table Level

CREATE TABLE my_accounts (
	chk_id			SERIAL PRIMARY KEY,

	acct1			MONEY,
	acct2			MONEY,

	CHECK (acct1 > acct2))

REFERENCES Clause

Element	Purpose	Restrictions	Syntax
column name	A referenced column or columns in the referenced table	You must observe restrictions on the column type and the number and length of columns. See "Restrictions on the Column Name Variable".	Identifier, p. 1-966
table name	The name of the referenced table	The referenced table must reside in the same database as the referencing table.	Table Name, p. 1-1048

Restrictions on the Column Name Variable

• The referenced column must be a unique or primary-key column.

That is, the referenced column must already include a unique or primary-key constraint.

• The data types of the referencing and referenced columns must be identical. The only exception is that a referencing column must be INTEGER if the referenced column is SERIAL or INT8 if the referenced column is SERIAL8.
• You cannot place a referential constraint on a BYTE or TEXT column.
• A column-level REFERENCES clause can include only a single column name.
• The maximum number of columns in a table-level REFERENCES clause is 16, and the total length of the columns cannot exceed 390 bytes.

The referencing column is the column or set of columns that refers to the referenced columns. Unlike a referenced column, the referencing column or set of columns can contain null and duplicate values. However, every non-null value in the referencing columns must match a value in the referenced columns. When a referencing column meets this criteria, it is called a foreign key.

The relationship between referenced and referencing columns is called a parent-child relationship, where the parent is the referenced column (primary key) and the child is the referencing column (foreign key). The referential constraint establishes this parent-child relationship.

You can use the REFERENCES clause to establish a referential relationship between two tables or within the same table. The referenced and referencing tables must be in the same database.

For example, you can have an employee table where the emp_no column uniquely identifies every employee through an employee number. The mgr_no column in that table contains the employee number of the manager who manages that employee. In this case, mgr_no is the foreign key (the child) that references emp_no, the primary key (the parent).

Using Column-Level REFERENCES Constraints

The following example creates two tables, accounts and sub_accounts. A referential constraint is created between the foreign key, ref_num, in the sub_accounts table and the primary key, acc_num, in the accounts table.

CREATE TABLE accounts (

	acc_num INTEGER PRIMARY KEY,

	acc_type INTEGER,

	acc_descr CHAR(20))



CREATE TABLE sub_accounts (

	sub_acc INTEGER PRIMARY KEY,

	ref_num INTEGER REFERENCES accounts (acc_num),

	sub_descr CHAR(20))

If the referenced table is different from the referencing table, you do not need to specify the referenced column; the default is the primary-key column or columns. If the referenced table is the same as the referencing table, you must specify the referenced column.

In the preceding example, you can simply reference the accounts table without specifying a column. Because acc_num is the primary key of the accounts table, it becomes the referenced column by default.

Using Table-Level REFERENCES Constraints

A referential constraint must have a one-to-one relationship between referencing and referenced columns. In other words, if the primary key is a set of columns, then the foreign key also must be a set of columns that corresponds to the primary key. The following example creates two tables. The first table has a multiple-column primary key, and the second table has a referential constraint that references this key.

CREATE TABLE accounts (

	acc_num INTEGER,

	acc_type INTEGER,

	acc_descr CHAR(20),

	PRIMARY KEY (acc_num, acc_type))



CREATE TABLE sub_accounts (

	sub_acc INTEGER PRIMARY KEY,

	ref_num INTEGER NOT NULL,

	ref_type INTEGER NOT NULL,

	sub_descr CHAR(20),

	FOREIGN KEY (ref_num, ref_type) REFERENCES accounts

		(acc_num, acc_type))

If you are referencing a primary key in another table, you do not have to state the primary-key columns in that table explicitly. Referenced tables that do not specify the referenced columns default to the primary-key columns. You can rewrite the references section of the previous example as follows:

...

	FOREIGN KEY (ref_num, ref_type) REFERENCES accounts

...

Locking Implications

Using ON DELETE CASCADE

For example, the all_candy table contains the candy_num column as a primary key. The hard_candy table refers to the candy_num column as a foreign key. The following CREATE TABLE statement creates the hard_candy table with the cascading-delete clause on the foreign key:

CREATE TABLE all_candy 
	(candy_num 			SERIAL PRIMARY KEY,
	 candy_maker			CHAR(25));

CREATE TABLE hard_candy 
	(candy_num				INT, 
	 candy_flavor				CHAR(20),

 	 FOREIGN KEY (candy_num) REFERENCES all_candy
	 ON DELETE CASCADE)

You specify cascading deletes with the REFERENCES clause on a column-level or table-level constraint. You need only the References privilege to indicate cascading deletes. You do not need the Delete privilege to perform cascading deletes; however, you do need the Delete privilege on tables referenced in the DELETE statement.

Locking and Logging

What Happens to Multiple Children Tables

Restriction on Cascading Deletes

See the Informix Guide to SQL: Tutorial for a detailed discussion about cascading deletes.

Constraint Mode Definition

You can use the Constraint Mode Definition option for the following purposes:

• To assign a name to a column-level or table-level constraint
• To set any type of column-level constraint or table-level constraint to the disabled, enabled, or filtering object modes

Description of Constraint Modes

If you choose filtering mode, you can specify the WITHOUT ERROR or WITH ERROR options. The following list explains these options.

Error Option	Effect
WITHOUT ERROR	When a filtering-mode constraint is violated during an insert, delete, or update operation, no integrity-violation error is returned to the user.
WITH ERROR	When a filtering-mode constraint is violated during an insert, delete, or update operation, an integrity-violation error is returned to the user.

Using Constraint Mode Definitions

• If you do not specify the object mode of a column-level constraint or table-level constraint explicitly, the default mode is enabled.
• If you do not specify the WITH ERROR or WITHOUT ERROR option for a filtering-mode constraint, the default error option is WITHOUT ERROR.
• Constraints defined on temporary tables are always in the enabled mode. You cannot create a constraint on a temporary table in the disabled or filtering mode, nor can you use the SET statement to switch the object mode on a temporary table to the disabled or filtering mode.
• You cannot assign a name to a not null constraint on a temporary table.
• You cannot create a constraint on a table that is serving as a violations or diagnostics table for another table.

WITH Clause

You cannot use the WITH ROWIDS clause with typed tables.

Important: Use the WITH ROWIDS clause only on fragmented tables. In non- fragmented tables, the rowid column remains unchanged. Informix recommends, however, that you utilize primary keys as an access method rather than exploiting the rowid column.

Using WITH NO LOG

If you use the WITH NO LOG keywords in a CREATE TABLE statement, and the database does not use logging, the WITH NO LOG option is ignored.

Once you turn off logging on a temporary table, you cannot turn it back on; a temporary table is, therefore, always logged or never logged.

The following example shows how to prevent logging temporary tables in a database that uses logging:

CREATE TEMP TABLE tab2 (fname CHAR(15), lname CHAR(15))

	WITH NO LOG 

You can specify a dbspace for the table that is different from the storage location specified for the database, or fragment the table into several dbspaces. You can also specify an sbspace for each smart large object (CLOB or BLOB) using the PUT clause.

Tip: If your table has columns that contain simple large objects (TEXT or BYTE), you can specify a separate blobspace for each object. For information on storing simple large objects, refer to "Large-Object Data Types".

The following statement creates the foo table. The data for the table is fragmented into the dbs1 and dbs2 dbspaces. However, the PUT clause assigns the smart large object data in the gamma and delta columns to the sb1 and sb2 sbspaces, respectively. The TEXT data in the eps column is assigned to the blb1 blobspace.

create table foo
(alpha			INTEGER,
beta			VARCHAR(150),
gamma			CLOB,
delta			BLOB,
eps			TEXT IN blb1)
	FRAGMENT BY EXPRESSION
	alpha <= 5 IN dbs1,
	alpha > 5 IN dbs2
	PUT gamma IN (sb1), delta IN (sb2)

IN dbspace Clause

For example, if the stores7 database is in the stockdata dbspace, but you want the customer data placed in a separate dbspace called custdata, use the following statements:

CREATE DATABASE stores7 IN stockdata

 

CREATE TABLE customer

 	(

 	customer_num				SERIAL(101),

 	fname				CHAR(15),

 	lname				CHAR(15),

 	company				CHAR(20),

 	address1				CHAR(20),

 	address2				CHAR(20),

 	city				CHAR(15),

 	state				CHAR(2),

 	zipcode				CHAR(5),

 	phone				CHAR(18)

 	)

 IN custdata EXTENT SIZE 16

 

 	.

	. 

	. 

FRAGMENT BY Clause

Use the FRAGMENT BY clause to define the distribution scheme, either round-robin or expression-based.

In a round-robin distribution scheme, specify at least two dbspaces where you want the fragments to be placed. As records are inserted into the table, they are placed in the first available dbspace. The database server balances the load between the specified dbspaces as you insert records and distributes the rows in such a way that the fragments always maintain approximately the same number of rows. In this distribution scheme, the database server must scan all fragments when it searches for a row.

In an expression-based distribution scheme, each fragment expression in a rule specifies a dbspace. Each fragment expression within the rule isolates data and aids the database server in searching for rows. Specify one of the following rules:

• Range rule

A range rule specifies fragment expressions that use a range to specify which rows are placed in a fragment, as the following example shows:

...

	FRAGMENT BY EXPRESSION

	c1 < 100 IN dbsp1,

	c1 >= 100 and c1 < 200 IN dbsp2,

	c1 >= 200 IN dbsp3

• Hash rule

A hash rule specifies fragment expressions that are created when you use a hash algorithm, which is often implemented with the MOD function, as the following example shows:

...

	FRAGMENT BY EXPRESSION

	MOD(id_num, 3) = 0 IN dbsp1,

	MOD(id_num, 3) = 1 IN dbsp2,

	MOD(id_num, 3) = 2 IN dbsp3

• Arbitrary rule

An arbitrary rule specifies fragment expressions based on a predefined SQL expression that typically includes the use of OR clauses to group data, as the following example shows:

...

	FRAGMENT BY EXPRESSION

	zip_num = 95228 OR zip_num = 95443 IN dbsp2,

	zip_num = 91120 OR zip_num = 92310 IN dbsp4,

	REMAINDER IN dbsp5

Warning:

PUT Clause

A smart large object is contained in a single sbspace. The SB_SPACE_NAME configuration parameter specifies the system default in which smart large objects are created unless you specify another area.

Important: The PUT clause does not affect the storage of simple large-object data types (BYTE and TEXT).

The PUT clause appears in the Storage Option on page 1-245.

EXTENT SIZE Option of the PUT Clause

If the extent size is not specified, or if no extent of the specified size exists, Universal Server uses the larger of:

• the size of the write request.
• the smallest extent size for the sbspace.

LOG and NO LOG Options of the PUT Clause

Warning: Use of the LOG option can generate large amounts of log traffic and increase the risk that the logical log fills up.

Instead of full logging, you might turn off logging when you load the smart large object initially, and then turn logging back on once the smart large object has been loaded.

Use the NO LOG option to turn off logging. If you use NO LOG, you can restore the smart-large-object metadata later to a state in which no structural inconsistencies exist. In most cases, no transaction inconsistencies will exist either, but that result is not guaranteed.

The NO LOG option is the default logging behavior for smart large objects.

HIGH INTEG and MODERATE INTEG Option

Important: After you have specified the HIGH INTEG option, you cannot use the ALTER TABLE statement to change to MODERATE INTEG.

The MODERATE INTEG option provides a lower level of data integrity but is faster. It does not produce page headers or trailers on user data pages, so it preserves the user data alignment on pages. The MODERATE INTEG option is useful for moving large volumes of data through the server when very high data integrity is not required. Audio and video applications may benefit from a MODERATE INTEG option.

KEEP ACCESS TIME and NO KEEP ACCESS TIME Options

When you specify the NO KEEP ACCESS TIME option, the database server does not track the system time at which the corresponding smart large object was last read or written. This option provides better performance than the KEEP ACCESS TIME option.

The NO KEEP ACCESS TIME option is the default.

EXTENT Option

Element	Purpose	Restrictions	Syntax
first kbytes	The length in kilobytes of the first extent for the table. The default length is eight times the disk page size on your system. For example, if you have a 2-kilobyte page system, the default length is 16 kilobytes.	The minimum length is four times the disk page size on your system. For example, if you have a 2-kilobyte page system, the minimum length is eight kilobytes. The maximum length is equal to the chunk size.	Expression, p. 1-880
next kbytes	The length in kilobytes for the subsequent extents. The default length is eight times the disk page size on your system. For example, if you have a 2-kilobyte page system, the default length is 16 kilobytes.	The minimum length is four times the disk page size on your system. For example, if you have a 2-kilobyte page system, the minimum length is 8 kilobytes.The maximum length is equal to the chunk size.	Expression, p. 1-880

The following example specifies a first extent of 20 kilobytes and allows the rest of the extents to use the default size:

CREATE TABLE emp_info

	(

	f_name			CHAR(20),

	l_name			CHAR(20),

	position			CHAR(20),

	start_date			DATETIME YEAR TO DAY,

	comments			VARCHAR(255)

	)

EXTENT SIZE 20 

The EXTENT option appears in the Storage Option on page 1-245.

LOCK MODE Clause

Row-level locking provides the highest level of concurrency. However, if you are using many rows at one time, the lock-management overhead can become significant. Also, you might exceed the maximum number of locks available, depending on the configuration of your database-server system.

Page locking allows you to obtain and release one lock on a whole page of rows. Page locking is especially useful when you know that the rows are grouped into pages in the same order that you are using to process all the rows. For example, if you are processing the contents of a table in the same order as its cluster index, page locking is especially appropriate.

You can change the lock mode of an existing table with the ALTER TABLE statement.

The Lock Mode clause appears in the Storage Option on page 1-245.

Access Method Option

An virtual table is managed outside of the database server but can be accessed by Universal Server users with SQL statements. Access to an virtual table requires a user-defined primary access method.

DataBlade Modules can provide other primary access methods to access virtual tables. When you access a virtual table, the database server calls the routines associated with that access method rather than the built-in table routines. For more information on these other primary access methods, refer to the DataBlade user guides.

Element	Purpose	Restrictions	Syntax
access method name	Name of the access method to be used with this table	Access method must already exist.	See "Access Method Name Clause"
configuration keyword	One of the configuration keywords associated with the specified access method name.	Keyword must already exist.	Keywords can be up to 18 bytes in length.
configuration value	Value of the specified configuration keyword. Configuration values are not required with all keywords. You can retrieve a list of configuration values for an access method from a table descriptor (mi_am_table_desc) using the MI_TAB_AMPARAM macro.	Value must be defined by the access method.	Value must be in quotation marks. Values can be up to 236 bytes in length.

The Access Method Option appears in the Options clause on page 1-243.

Access Method Name Clause

create table mybook

(... )

using textfile (delimiter=':')

References

In the Informix Guide to SQL: Tutorial, see the discussion of data-integrity constraints and the discussion of the ON DELETE CASCADE clause. Also see the discussion of creating a database and tables in the same book.

In the INFORMIX-Universal Server Performance Guide, see the discussion of extent sizing.