PUT Clause

When more than one sbspace name follows the IN keyword, parentheses must delimit the comma-separated list of sbspaces that store the BLOB or CLOB objects. Unless you accept default values for all of the storage options that can follow the sbspace list, delimiting parentheses must also enclose the list of storage options, as in the following example:

ALTER TABLE MyClobs ADD (c5 CLOB)
   PUT c5 IN (sbs3,sbs4) (EXTENT SIZE 64, HIGH INTEG);   

Here the ADD clause above appends a new column c5 of type CLOB to table MyClobs. The PUT clause stores each CLOB object in sbspace sbs3 or sbs4, in a round-robin distribution. The first extent size is 64 kilobytes, and the data pages will include page headers and page footers in HIGH data integrity mode. By default, the column is unlogged, and access times are not recorded.

When you use this clause to specify storage attributes for smart large objects in a new column that the ALTER TABLE statement is adding to the table schema, or for an existing column whose sbspace storage the ALTER TABLE statement is modifying, these keyword options of the PUT clause can define explicit or default characteristics for one or more existing sbspace:

IN ( sbspace_list )

Here sbspace_list is a comma-separated list of one or more sbspace names. If you omit IN sbspace_list from the PUT clause, the SBSPACENAME configuration parameter specifies the system default sbspace in which smart large objects are stored. If the sbspace_list of the PUT clause specifies more than one sbspace, the smart blob objects are distributed round-robin among those sbspaces. Each smart large object is stored in a single sbspace.

EXTENT SIZE integer

This sets a lower limit on how many kilobytes can be stored in a smart-large-object extent. If you omit the EXTENT SIZE keywords, the database server calculates a default extent size.

NO LOG

This prevents the same logging procedure that is applied to other columns from being applied to the smart large objects. This default reduces log traffic, and reduces the risk of filling the logical log.

LOG

This alternative option to NO LOG applies the logging procedure used with the current database for smart large objects in the sbspaces that this PUT clause assigns.

HIGH INTEG

This high data-integrity option produces user-data pages that each contains a page header and a page trailer to detect incomplete writes and data corruption. This option is the default data-integrity behavior.

MODERATE INTEG

This alternative data-integrity option to HIGH INTEG produces user-data pages that each contains a page header but no page trailer. This option reduces operational costs, but cannot detect incomplete writes or data corruption by comparing the page header with a page trailer.

NO KEEP ACCESS TIME

This does not record the system time when the smart large object was last read or written. This option provides better performance than the KEEP ACCESS TIME option, and is the default tracking behavior.

KEEP ACCESS TIME

This alternative tracking option to NO KEEP ACCESS TIME maintains a record in the smart-large-object metadata of the system time when the smart large object was last read or written.

When you modify the storage characteristics of an existing column, by default all attributes previously associated with the storage space for that column are dropped. If you want the same settings for certain attributes to persist, you must explicitly specify the settings for those attributes again. For example,

• to retain logging, you must specify the LOG keyword again. Otherwise, the default NO LOG option takes effect.
• to retain the less costly level of data-integrity, you must specify the MODERATE INTEG keywords again. Otherwise, the default HIGH INTEG option takes effect.
• to maintain a smart-large-object metadata record of when the smart large object was last read or written, you must specify KEEP ACCESS TIME keywords again. Otherwise, the default NO KEEP ACCESS TIME option takes effect.

Suppose that permanent table sbtab was created with the following schema:

CREATE TABLE sbtab (c2 INT);

The following ALTER TABLE statement alters the schema of table sbtab by adding BLOB column c1 as the new first column, and stores its BLOB objects in existing sbspace sbs1, for which the PUT clause defines additional attributes:

ALTER TABLE sbtab ADD (c1 BLOB BEFORE c2)
   PUT c1 IN (sbs1) (EXTENT SIZE 32, 
                     NO LOG,
                     MODERATE INTEG,
                     KEEP ACCESS TIME);     

The PUT clause specifies these storage attributes for new column c1:

• allocates 32 kilobytes for the first extent of each BLOB object,
• disables transaction logging for the new column in sbspace sbs1,
• uses the data-integrity option of data page headers without footers,
• and maintains a record of when each BLOB object was last read or written.

Suppose that the owner of table sbtab subsequently decides to change the storage options for the BLOB objects in column c1. The following ALTER TABLE statement alters the storage attributes of BLOB column c1 by changing the extent size in the sbspace sbs1 to 64 kilobytes, and turning on transaction logging:

ALTER TABLE sbtab PUT c1 IN (sbs1) (EXTENT SIZE 64, LOG);

Because this example does not repeat the nondefault settings for the data integrity and access time attributes that Example 1 specified, the new PUT clause sets these storage options for new BLOB objects in column c1:

• continues to store new BLOB objects in sbspace sbs1,
• increases to 64 kilobytes the first extent of each BLOB object,
• explicitly enables transaction logging for column c1,
• implicitly restores the default data-integrity option of data pages with headers and footers,
• and implicitly discontinues recording ACCESS TIME records.

But all of the storage attributes that the ALTER TABLE sbtab ADD statement set for sbspace sbs1 in Example 1 persist in any rows that were added to the sbtab table before Example 2 reset those attributes.

The next example of the PUT clause modifies only one attribute of the same sbspace sbs1 to change its logging mode for BLOB column c1 to NO LOG:

ALTER TABLE sbtab PUT c1 IN (sbs1) (EXTENT SIZE 64, NO LOG);

Because NO LOG is the default for smart large objects, this statement has the same effect if the NO LOG keywords are omitted, as in this equivalent example:

This example alters the sbtab table to distribute the storage of BLOB column c1 in sbspaces sbs1 and sbs2.

ALTER TABLE sbtab PUT c1 IN (sbs1, sbs2)
   (EXTENT SIZE 100, LOG, KEEP ACCESS TIME);

The PUT clause also

• changes the extent size to 100 kilobytes,
• turns on transaction logging,
• and records the system times of the last read access and write access in the smart-large-object metadata.