Altering smart-large-object columns

When you create or modify a table, you have several options for choosing storage characteristics and other attributes (such as logging status, buffering, data integrity, and locking granularity) for specific smart-large-object columns.

When you create or modify a table that can store BLOB or CLOB objects, you have these options:
  • Use the values that were set when the sbspace was created. These values are specified in one of the following ways:
    • With the various flags of the -Df option of the onspaces -c -S command
    • With the system default value for any flag that was not specified.

    For guidelines to change the default storage characteristics of the -Df flags, 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, including the number of sbspaces, the extent size, the logging, buffering, and data integrity status, and the locking granularity.

    Characteristics or attributes that you do not specify in the PUT clause default to the values set in the onspaces -c -S command, or to system default values (for example, no logging).

Later, you can use the PUT clause of the ALTER TABLE statement to change the optional storage characteristics of BLOB or CLOB columns. See Altering storage characteristics and other attributes of an sbspace for characteristics and attributes of sbspaces that 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 BLOB or CLOB column to a table.

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

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

    The new column characteristics apply only to smart large objects in new rows inserted after the ALTER TABLE PUT statement was issued. The old characteristics persist for any smart large objects that already existed in the column before the ALTER TABLE PUT statement modified the column.

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 changing sbspace extents with the CREATE TABLE statement, see Extent sizes for smart large objects in sbspaces.