Changing Storage Characteristics of Smart Large Objects

When you create an sbspace but do not specify values in the -Df option of the onspaces -c -S command, you use the defaults for the storage characteristics and attributes (such as logging and buffering).

After you monitor sbspaces, you might want to change the storage characteristics, logging status, lock mode, or other attributes for new smart large objects.

The database administrator or programmer can use the following methods to override these default values for storage characteristics and attributes:

• The database administrator can use one of the following onspaces options:
  • Specify values when the sbspace is first created with the onspaces -c -S command.
  • Change values after the sbspace is created with the onspaces -ch command.

  Specify these values in the tag options of the -Df option of onspaces. For more information about the onspaces utility, see the utilities chapter in the IBM Informix: Dynamic Server Administrator's Reference.

• The database administrator can specify values in the PUT clause of the CREATE TABLE or ALTER TABLE statements.

  These values override the values in the onspaces utility and are valid only for smart large objects that are stored in the associated column of the specific table. Other smart large objects (from columns in other tables) might also reside in this same sbspace. These other columns continue to use the storage characteristics and attributes of the sbspace that onspaces defined (or the default values, if onspaces did not define them) unless these columns also used a PUT clause to override them for a particular column.

  If you do not specify the storage characteristics for a smart-large-object column in the PUT clause, they are inherited from the sbspace.

  If you do not specify the PUT clause when you create a table with smart-large-object columns, the database server stores the smart large objects in the system default sbspace, which is specified by the SBSPACENAME configuration parameter in the onconfig file. In this case, the storage characteristics and attributes are inherited from the SBSPACENAME sbspace.

• Programmers can use functions in the DataBlade API and ESQL/C to alter storage characteristics for a smart-large-object column.
  ESQL/C

  For information about the DataBlade API functions for smart large objects, see the IBM Informix: DataBlade API Programmer's Guide. For information about the ESQL/C functions for smart large objects, see the IBM Informix: ESQL/C Programmer's Manual.

  End of ESQL/C

Table 6 summarizes the ways to alter the storage characteristics for a smart large object.

Altering Smart-Large-Object Columns

When you create a table, you have the following options for choosing storage characteristics and other attributes (such as logging status, buffering, and lock mode) for specific smart-large-object columns:

• Use the values that were set when the sbspace was created. These values are specified in one of the following ways:
  • With the various tags of the -Df option of the onspaces -c -S command
  • With the system default value for any specific tag that was not specified

  For guidelines to change the default storage characteristics of the -Df tags, see onspaces Options That Affect Sbspace I/O.

• Use the PUT clause of the CREATE TABLE statement to specify non-default values for particular characteristics or attributes.

  When you do not specify particular characteristics or attributes in the PUT clause, they default to the values set in the command. onspaces -c -S

Later, you can use the PUT clause of the ALTER TABLE statement to change the optional storage characteristics of these columns. Table 6 shows which characteristics and attributes you can change.

You can use the PUT clause of the ALTER TABLE statement to perform the following actions:

• Specify the smart-large-object characteristics and storage location when you add a new column to a table.

  The smart large objects in the new columns can have different characteristics than those in the existing columns.

• Change the smart-large-object characteristics of an existing column.

  The new characteristics of the column apply only to new smart large objects created for that column. The characteristics of existing smart large objects remain the same.

• Convert simple large objects to smart large objects by changing the column type from TEXT to CLOB or from BYTE to BLOB. For more information about converting simple large objects, see the IBM Informix: Migration Guide.

For example, the BLOB data in the catalog table in the superstores_demo database is stored in s9_sbspc with logging turned off and has an extent size of 100 kilobytes. You can use the PUT clause of the ALTER TABLE statement to turn on logging and store new smart large objects in a different sbspace. For information about sbspace logging, see smart large objects in the logging chapter of your IBM Informix: Dynamic Server Administrator's Guide.

For information about changing sbspace extents with the CREATE TABLE statement, see Extent Sizes for Smart Large Objects in Sbspaces.

For more information about CREATE TABLE and ALTER TABLE, see the IBM Informix: Guide to SQL Syntax.