Using the IN Clause

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

Storing Data in a dbspace

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:

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

Storing Data in a Partition of a dbspace (IDS)

Besides the option of storing the table (or a fragment of it) in a dbspace, Dynamic Server supports storing fragments of a table in a subset of a dbspace, called a partition. Unless you explicitly declare names for the fragments in the PARTITION BY clause, each fragment, by default, has the same name as the dbspace where it resides. This includes all fragmented tables and indexes migrated from earlier releases of Dynamic Server.

You can store fragments of the same table in multiple partitions of the same dbspace, but each name that you declare after the PARTITION keyword must be unique among partitions of that dbspace. The PARTITION keyword is required when you store more than one fragment of the table in the same dbspace. You can also use the PARTITION keyword to declare a more meaningful name for a dbspace that has only one partition.

Storing Data in a dbslice (XPS)

If you are using Extended Parallel Server, the IN dbslice clause allows you to fragment a table across a group of dbspaces that share the same naming convention. The database server fragments the table by round-robin in the dbspaces that make up the dbslice at the time the table is created.

To fragment a table across a dbslice, you can use either the IN dbslice syntax or the FRAGMENT BY ROUND ROBIN IN dbslice syntax.

Storing Data in an extspace (IDS)

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

FRAGMENT BY Clause

Use the FRAGMENT BY clause to create fragmented tables and to specify their distribution scheme. (The keywords PARTITION BY are a synonym for FRAGMENT BY in Dynamic Server.)

FRAGMENT BY Clause for Tables:
 
|--+--------------------+--+-FRAGMENT---------+--BY------------->
   |  (1)               |  |  (1)             |
   '--------WITH ROWIDS-'  '--------PARTITION-'
 
                          .-,-------.
                          V         |
>--+-ROUND ROBIN--+-IN--+---dbspace-+----+------------------+-+--|
   |              |     |  (2)           |                  | |
   |              |     '--------dbslice-'                  | |
   |              | .-,-----------------------------------. | |
   |              | V  (1)                                | | |
   |              '----------PARTITION--part--IN--dbspace-+-' |
   +-EXPRESSION--+-----------------------+--| Fragment List |-+
   |             |  (1)                  |                    |
   |             '--------USING--opclass-'                    |
   |                 .-,------.           .-,-------.         |
   |  (2)            V        |           V         |         |
   '------+-HASH--(----column-+--)--IN--+---dbspace-+-+-----+-'
          |                             '-dbslice-----'     |
          |            .-,------.                           |
          |            V        |                           |
          +-HYBRID--(----column-+--)--| EXPRESSION Clause |-+
          |                         (3)                     |
          '-| RANGE Method Clause |-------------------------'
 
Fragment List:
 
   .-,-------------------------------------------------------.
   | .-,--------------------------------------.              |
   V V                                        |              |
|------+------------------------+--(--expr--)-+--IN--dbspace-+-->
       |  (1)                   |
       '--------PARTITION--part-'
 
>--+-------------------------------------------------------+----|
   '-,--REMAINDER--+------------------------+--IN--dbspace-'
                   |  (1)                   |
                   '--------PARTITION--part-'
 
EXPRESSION Clause:
 
               .-,-------------------------------.
               V                                 |
|--EXPRESSION----expr--IN--+-dbspace-----------+-+-------------->
                           +-dbslice-----------+
                           |    .-,-------.    |
                           |    V         |    |
                           '-(----dbspace-+--)-'
 
>--,--+-REMAINDER-+--IN--+-dbspace-----------+--)---------------|
      '-expr------'      +-dbslice-----------+
                         |    .-,-------.    |
                         |    V         |    |
                         '-(----dbspace-+--)-'

Notes:

Dynamic Server only
Extended Parallel Server only
See page RANGE Method Clause (XPS)

Element	Description	Restrictions	Syntax
column	Column to which to apply the fragmentation strategy	Must be a column within the table	Identifier, p. Identifier
dbslice, dbspace	Dbslice or dbspace to store the table fragment	The dbslice must be defined. You can specify no more than 2,048 dbspaces.	Identifier, p. Identifier
expr	Expression that defines a table fragment using a range, hash, or arbitrary rule	Columns can be from the current table only, and data values can be from only a single row. Value returned must be Boolean (true or false).	Expression, p. Expression
opclass	No default operator class	Must be defined and must be associated with a B-tree index	Identifier, p. Identifier
part	Name that you declare here for a partition of a dbspace	Required for any partition in the same dbspace as another partition of the same table	Identifier, p. Identifier

When you fragment a table, the IN keyword is followed by the name of the storage space where a table fragment is to be stored.