Creating and Specifying Storage Spaces

A storage space is a physical area where the index data is stored. To test how the access method builds new indexes, you create a new physical storage space before you create the index.

This section describes how to establish storage spaces.

Using Internal Storage

An sbspace holds smart large objects for the database server. This space is physically included in the database server configuration. It is recommended that you store indexes in smart-large objects because the database server protects transaction integrity in sbspaces with rollback and recovery.

Creating an Sbspace

An sbspace must exist before you can create a virtual index in it. Before you can test the ability of the access method to create an index that does not yet exist, you must run the onspaces utility to create a smart-large-object storage space. The onspaces command associates a logical name with a physical area of a specified size in a database server partition.

The following onspaces command creates an sbspace named vspace1:

onspaces -c -S vspace1 -g 2 -p /home/informix/chunk2 
   -o 0 -s 20000

onspaces -c -S vspace1 -g 2 -p \home\informix\chunk2 
   -o 0 -s 20000

Specifying the Logical Sbspace Name

The following example creates a virtual index in the previously created vspace1:

CREATE INDEX ix1 ON tab1(col1)
   IN vspace1
   USING your_access_method

If you do not intend to specify an sbspace explicitly in the CREATE INDEX statement, specify a default sbspace. To find out how to create a default dbspace, see Creating a Default Sbspace.

The following example also creates a virtual index in the sbspace that SBSPACENAME specifies:

CREATE INDEX ix1 ON tab1(col1)
   USING your_access_method

Using External Storage

An extspace lies outside the disk storage that is configured for the database server. To create a physical extspace, you might use an operating system command or use a data management software system. An extspace can have a location other than a path or filename because the database server does not interpret the location. Only the access method uses the location information.

To store virtual data in an extspace, take one of the following actions:

• Create logical names for existing external storage with the onspaces utility and then specify the reserved name or names when you create a virtual index with the CREATE INDEX statement.
• Directly specify an existing physical external storage location as a quoted string in the CREATE INDEX statement.
• Provide a default physical external storage location, such as a disk file, in the access-method code.

Specifying a Logical Name

The onspaces command creates an entry in the system catalog that associates a name with an existing extspace. To create a logical extspace name, use the following command-line syntax:

onspaces -c -x exspace_name -l "location_specifier"

onspaces -c -x disk_file -l "/home/database/datacache"

onspaces -c -x tape_dev -l "/dev/rmt/0"

onspaces -c -x disk_file -l "\home\database\datacache"

If you assign a name with onspaces, refer to it by its logical name in the SQL statement that creates the index, as in the following example:

CREATE INDEX ix1 ON tab1(col1)
   IN disk_file
   USING your_access_method

Specifying the Physical Location

As an alternative to the extspace name, a CREATE INDEX statement can directly specify a quoted string that contains the external location.

CREATE INDEX ix1 ON tab1(col1)
   IN "location_specifier"
   USING your_access_method

Providing a Default Extspace

If you do not intend to specify an extspace explicitly in the CREATE INDEX statement, the access method can create a default extspace. For an example that creates an extspace directly in the access-method code, refer to Figure 6.

Using Fragments

If you want to test the access method for fragmentation support, specify a different storage space for each fragment.

The following example shows the creation of an index with two fragments. Each fragment corresponds to a separate extspace. The database server alternates between the fragments to store new data.

CREATE INDEX index_name ON table(keys)
   FRAGMENT BY ROUNDROBIN IN "location_specifier1",
"location_specifier2"
   USING access_method_name

To fragment an index in smart-large-object storage, create a separate sbspace for each fragment before you create the index. Use the onspaces command, as the following example shows:

onspaces -c -S fragspace1 -g 2 -p location_specifier1 -o 0 -s 20000
onspaces -c -S fragspace2 -g 2 -p location_specifier2 -o 0 -s 20000

CREATE INDEX progress on catalog (status pages)
   USING catalog_am
   FRAGMENT BY EXPRESSION
      pages > 15 IN fragspace2,
      REMAINDER IN fragspace1

Avoiding Storage-Space Errors

An SQL error occurs if you include an IN clause with the CREATE INDEX statement and one of the following conditions is true:

• The IN clause specifies an extspace or sbspace that does not exist.
• The IN clause specifies an sbspace but the am_sptype purpose value is set to X.
• The IN clause specifies an extspace but the am_sptype purpose value is set to S.

An SQL error occurs if the CREATE INDEX statement contains no IN clause and one of the following conditions is true:

• The am_sptype purpose value is set to A, no default SBSPACENAME exists, and the access method does not create an extspace.
• The am_sptype purpose value is set to S, and no default SBSPACENAME exists.
• The am_sptype purpose value is set to X, and the access method does not create an extspace.

An SQL error occurs if one of the following conditions is true:

• The am_sptype purpose value is set to D.
• The IN clause with the CREATE INDEX statement specifies a dbspace, even if the am_sptype purpose value is set to A.