Creating and Specifying Storage Spaces

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

If the access method interfaces with legacy data, the storage spaces already exist, usually in external storage.

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.

Creating an Sbspace

An sbspace must exist before you can create a virtual table in it. Before you can test the ability of the access method to create a table 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 table in the previously created vspace1:

CREATE TABLE tab1(...)
   IN vspace1
   USING your_access_method

If you do not intend to specify an sbspace explicitly in the CREATE TABLE 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 table in the sbspace that SBSPACENAME specifies:

CREATE TABLE tab1(...)
   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 table with the CREATE TABLE statement.
• Directly specify an existing physical external storage location as a quoted string in the CREATE TABLE 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 table, as in the following example:

CREATE TABLE tab1(
   col1 INTEGER,
   col2 INTEGER)
   IN disk_file
   USING your_access_method

Specifying the Physical Location

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

CREATE TABLE tab1(
   col1 INTEGER,
   col2 INTEGER)
   IN "location_specifier"
   USING your_access_method

Providing a Default Extspace

If you do not intend to specify an extspace explicitly in the CREATE TABLE 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

To test the access method for fragmentation support, specify a different storage space for each fragment.

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

CREATE TABLE table_name(...)
   FRAGMENT BY ROUNDROBIN IN "location_specifier1",
"location_specifier2"
   USING access_method_name

To fragment a table in smart-large-object storage, create a separate sbspace for each fragment before you create the table. 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 TABLE 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 TABLE 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 TABLE 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 TABLE statement specifies a dbspace, even if the am_sptype purpose value is set to A.