Managing Storage Spaces

A user-defined access method stores data in sbspaces, extspaces, or both. To access data in smart large objects, the access method must support sbspaces. To access legacy data in disk files or within another database management system, the access method supports extspaces.

The following sections describe how the access method supports sbspaces, extspaces, or both.

Choosing DataBlade API Functions

The type of storage space determines whether you use mi_file_*() functions or mi_lo_*() functions to open, close, read from, and write to data.

To have the access method store data in an sbspace, use the smart-large-object interface of the DataBlade API. The names of most functions of the smart-large-object interface begin with the mi_lo_ prefix. For example, you open a a smart large object in an sbspace with mi_lo_open() or one of the smart-large-object creation functions: mi_lo_copy(), mi_lo_create(), mi_lo_expand(), or mi_lo_from_file().

If the access method stores data on devices that the operating system manages, use the DataBlade API file-access functions. Most file-access functions begin with the mi_file_ prefix. For example, the am_open purpose function might open a disk file with mi_file_open().

For more information about smart-large-object functions and file-access functions, refer to the IBM Informix: DataBlade API Programmer's Guide.

If another database manager reads and writes the data, pass input/output requests to the external database manager. For a demonstration of a primary access method that passes data requests to external processes, choose the DataBlade Corner from the list box on the IBM Informix Developer Zone at www.ibm.com/software/data/developer/informix.

Setting the am_sptype Value

Set the am_sptype value to S if the access method reads and writes to sbspaces but not to extspaces. Set the am_sptype value to X if the access method reads and writes only to extspaces but not to sbspaces.

To set the am_sptype purpose value, use the CREATE PRIMARY ACCESS_METHOD or ALTER ACCESS_METHOD statement, as SQL Statements for Access Methods describes.

If you do not set the am_sptype storage option, the default value A means that a user can create a virtual table in either extspaces or sbspaces. The access method must be able to read and write to both types of storage spaces.

Creating a Default Storage Space

A default storage space of the appropriate type prevents an exception from occurring if the user does not specify a storage-space name in the CREATE TABLE statement.

Creating a Default Sbspace

If the access method supports sbspaces, the user, typically the database server administrator, can create a default sbspace.

For example, you create a default sbspace named vspace with the following steps.

1. From the command line, create the sbspace with logging turned on:

   onspaces -c -S vspace -p path -o offset -s size -Df "LOGGING=ON"

2. Edit the ONCONFIG file to insert the following line:

   SBSPACENAME vspace # Default sbspace name

3. Take the database server offline and then bring it online again to initialize memory with the updated configuration.

   onmode -ky
   oninit

For more information about the configuration file parameters and the onspaces, onmode, and oninit utilities, refer to the IBM Informix: Administrator's Reference.

Creating a Default Extspace

The ONCONFIG file does not provide a parameter that specifies default extspace name. The access method might do one of the following if the CREATE TABLE statement does not specify an extspace:

• Raise an error.
• Specify an external storage space.

  The example in Figure 6 specifies a directory path as the default extspace on a UNIX system.

mi_integer external_create(td)
MI_AM_TABLE_DESC *td;
{
...
/* Did the CREATE statement specify a named extspace? **/
dirname = mi_tab_spaceloc(td);
if (!dirname || !*dirname)
{
   /* No. Put the table in /tmp */
   dirname = (mi_string *)mi_alloc(5);
   strcpy(dirname, "/tmp");
}
sprintf(name,"%s/%s-%d", dirname, mi_tab_name(td),
      mi_tab_partnum(td));

out = mi_file_open(name,O_WRONLY|O_TRUNC|O_CREAT,0600);

Ensuring Data Integrity

The access method might provide any of the following features to ensure that source data matches virtual data:

• Locks
• Logging
• Backup and recovery
• Transaction management

Activating Automatic Controls in Sbspaces

The following advantages apply to data that resides in sbspaces:

• A database server administrator can back up and restore sbspaces with standard IBM Informix utilities.
• The database server automatically provides for locking.
• If a transaction fails, the database server automatically rolls back sbspace metadata activity.

If logging is turned on for the smart large object, the database server does the following:

• Logs transaction activity
• Rolls back uncommitted activity if a transaction fails

You can either advise the end user to set logging on with the onspaces utility or call the appropriate DataBlade API functions to set logging.

In the access-method user guide, provide the appropriate information to describe transaction logging using the access method. If the access method does not turn on transaction logging, the user guide should explain how to turn on logging for a virtual table in an sbspace.

To enable logging, the access method sets the MI_LO_ATTR_LOG create-time constant with the DataBlade API mi_lo_create() or mi_lo_alter() function. The following example attempts to set the constant that turns on logging and verifies that the setting succeeded:

mi_integer status;.
status = mi_lo_specset_flags(lo_spec_p, MI_LO_ATTR_LOG);
if(status == MI_ERROR)
{
   mi_db_error_raise(NULL,MI_EXCEPTION, 
      "Unable to activate transaction logging.");
   return MI_ERROR;
}

For more information about metadata logging and transaction logging, refer to the IBM Informix: Administrator's Guide.

Adding Controls for Extspaces

Because the database server cannot safeguard operations on extspace data, include UDRs for any of the following features that you want the access method to provide:

• Locks
• Logging and recovery
• Transaction commit and rollback management (described in Checking Isolation Levels)

Checking Storage-Space Type

The database server issues an error if the CREATE TABLE statement specifies inappropriate storage type. To determine the storage space (if any) that the CREATE TABLE statement specifies, the access method calls the mi_tab_spacetype() function. For details, refer to the description of mi_tab_spacetype() on page mi_tab_spacetype().

For more information about errors that occur from inappropriate storage-space type, refer to Avoiding Storage-Space Errors. For more information about documenting potential errors and intercepting error events, refer to Supplying Error Messages and a User Guide.

Supporting Fragmentation

A fragmented table has multiple physical locations, called fragments. The user specifies the criteria by which the database server distributes information into the available fragments. For examples of how a user creates fragments, refer to Using Fragments. For a detailed discussion about the benefits of and approaches to fragmentation, refer to the IBM Informix: Database Design and Implementation Guide.

When the table is fragmented, each call to the access method involves a single fragment rather than the whole table. An SQL statement such as CREATE TABLE can result in a set of purpose-function calls from am_open through am_close for each fragment.

The database server can process fragments in parallel. For each fragment identifier, the database server starts a new access-method thread. To obtain the fragment identifier for the table, call the mi_tab_partnum() function.

An end user might change the way in which values are distributed among fragments after data already exists in the table. Because some rows might move to a different fragment, an ALTER FRAGMENT statement requires a scan, delete, and insert for each moved row. For information about how the database server uses the access method to redefine fragments, refer to ALTER FRAGMENT Statement Interface.

For information about the FRAGMENT BY clause, refer to the IBM Informix: Guide to SQL Syntax.