Using the IN Clause

Use the IN clause of the CREATE TABLE statement to specify a storage space for the table. The storage space that you specify must already exist.

You can use the IN clause to isolate a table. For example, if the history database is in the dbs1 dbspace, but you want the family data placed in a separate dbspace called famdata, use the following statements: 
CREATE DATABASE history IN dbs1;

CREATE TABLE family
   (
   id_num      SERIAL(101) UNIQUE,
   name        CHAR(40),
   nickname    CHAR(20),
   mother      CHAR(40),
   father      CHAR(40)
   )
   IN famdata;

For more information about how to store and manage your tables in separate dbspaces, see your Informix® Administrator's Guide.

In general, use the extspace storage option in conjunction with the USING Access-Method Clause. For more information, refer to the documentation of your access method.

If IN dbspace is the only storage specification for the new table, then by default 16 kilobytes of storage (or enough storage for four pages, if 4 pages require more than 16 kilobytes) are allocated for the first extent size at the time of table creation.

No storage is allocated for the first extent, however, if the CREATE TABLE statement includes none of the following storage specifications:
  • EXTENT SIZE
  • NEXT SIZE
  • IN dbspace.
In this case, storage allocation for the first extent is deferred until the first row is stored in the table.

The same storage allocation deferral applies to tables defined by the CREATE TEMP TABLE statement that do not include any of the storage specifications listed above.

When rows are first inserted into a table for which extent allocation has been deferred, the default size for the first extent is 16 kilobytes. If 16 kilobytes are insufficient for 4 pages, the first extent size will be 4 pages.

When reverting a Version 11.70 or later database server to an earlier release that does not support deferred extent allocation for empty tables, you must either drop or else insert a row into any empty database table that you wish to revert. Empty external tables that do not support INSERT operations must be dropped.