PUT Clause

Use the PUT clause to specify the storage spaces and their characteristics for each column that will contain smart large objects.

This syntax fragment is part of the Storage options.

PUT Clause


Element	Description	Restrictions	Syntax
`column`	Column to store in `sbspace`	Must contain a BLOB, CLOB, user-defined, or complex data type	Identifier
`kilobytes`	Number of kilobytes to allocate for the extent size	Must be an integer value	Literal Number
`sbspace`	Name of a storage area for smart large objects	Must exist If the table is in a tenant database, the `sbspace` must be a dedicated sbspace in the tenant database properties list. If the table is not in a tenant database, the `sbspace` cannot be the name of an sbspace that is dedicated to a tenant database.	Identifier

The specified column cannot be in the form column.field. That is, the smart large object that you are storing cannot be one field of a ROW type.

Specifying the storage location

Each smart large object is stored in a single sbspace. The SBSPACENAME configuration parameter specifies the system default sbspace in which smart large objects are created, unless the PUT clause specifies another sbspace.

For example, the following statement defines tabwblob as a table whose only column is of data type BLOB. The column name is declared as image01, and the PUT clause specifies the storage location for all of its BLOB objects as sbspace01:

CREATE TABLE tabwblob
   (
    image01 BLOB
   ) PUT image01 IN (sbspace01);

For the example above to be valid, the sbspace01 must already exist. Because the no other options to the PUT clause are specified, sbspace01 has default values for its extent size and for the other storage characteristics that the PUT clause can define, including NO LOG, HIGH INTEG, and NO KEEP ACCESS TIME, as defined below.

The PUT clause can specify storage locations for a list of BLOB and CLOB columns. The following example defines tabw2blobs as a table with two columns, where column image02 is of type BLOB, and column commentary03 is of type CLOB. In the next example, the PUT clause specifies that all the smart large objects in both columns are stored in the same sbspace01 smart large object space:

CREATE TABLE tabw2blobs
   (
    image02 BLOB,
    commentary03 CLOB
   ) PUT image02 IN (sbspace01),
       commentary03 IN (sbspace01);

You can specify that more than one sbspace stores the same BLOB or CLOB column. This distributes the smart large objects in a round-robin distribution scheme, so that the number of smart large objects in each sbspace is approximately equal. The comma-separated list of sbspaces for a single column must be delimited by parentheses.

The next example defines tabw2sblobs as a table with two columns, where column image04 is of type BLOB, and column commentary05 is of type CLOB. The PUT clause specifies that the BLOB objects in column image04 are stored in two sbspaces, sbspace01 and sbspace02, and all the CLOB objects in column image05 are stored in sbspace sbspace03:

CREATE TABLE tabw2sblobs
   (
    image04 BLOB,
    commentary05 CLOB
   ) PUT image04 IN (sbspace01,sbspace02),
       commentary05 IN (sbspace03);

If an INSERT or MERGE operation adds six new rows to the table in this example,

three of the image04 BLOB objects of will be stored in sbspace01,
the other three image04 BLOB objects will be stored in sbspace02,
and all six commentary05 CLOB objects will be stored in sbspace03.

When you distribute smart large objects across different sbspaces, you can work with smaller sbspaces. If you limit the size of an sbspace, backup and archive operations can perform more quickly.

Specifying sbspace characteristics

The following storage options are available to store BLOB and CLOB data:

Option: Effect
EXTENT SIZE: Specifies a lower limit on how many kilobytes can be stored in a smart-large-object extent. The database server might round the specified kilobytes value up, so that the extent size is an integer multiple of the sbspace page size.
HIGH INTEG: This high data-integrity option produces user-data pages that contain 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 data-integrity option produces user-data pages that contain a page header but no page trailer. This option cannot compare the page header with the page trailer to detect incomplete writes and data corruption.
KEEP ACCESS TIME: This maintains a record in the smart-large-object metadata of the system time when the smart large object was last read or written.
NO KEEP ACCESS TIME: Does not record the system time when the smart large object was last read or written. This provides better performance than the KEEP ACCESS TIME option, and is the default tracking behavior.
LOG: Follows the logging procedure used with the current database log for the corresponding smart large object. This option can generate large amounts of log traffic and increase the risk of filling the logical log.
NO LOG: Turns off logging. This option is the default behavior.

The comma-separated list of keyword options that define sbspace characteristics must be enclosed in parentheses, and immediately follows the sbspace (or the list of sbspaces) that stores the BLOB or CLOB column. In the following example, the PUT clause specifies that the unlogged sbspace01 and sbspace02 sbspaces that store the BLOB objects of column image04 have characteristics different from sbspace03, a logged sbspace that stores CLOB objects of column commentary05:

CREATE TABLE tabw2sblobs
   (
    image04 BLOB,
    commentary05 CLOB
   ) PUT image04 IN (sbspace01,sbspace02) (KEEP ACCESS TIME, MODERATE INTEG),
       commentary05 IN (sbspace03) (EXTENT SIZE 30, LOG);

When you turn logging on for a smart large object, you must immediately perform a level-0 backup to be able to recover and restore the smart large object.

The syscolattribs system catalog table contains one row for each sbspace and column combination in the PUT clause:

The syscolattribs.extentsize column stores the extent size, based on the kilobytes value.
The syscolattribs.flags column stores a bitmap corresponding to the logging and access time status, and data integrity setting.

If a user-defined or complex data type contains more than one large object, the specified large-object storage options apply to all large objects in the type, unless the storage options are overridden when the large object is created.

Important: The PUT clause does not affect the storage of simple-large-object data types (BYTE and TEXT). For information on how to store BYTE and TEXT data, see Large-Object Data Types.

Alternative to full logging

Instead of full logging, you can turn off logging when you load the smart large object initially and then turn logging back on once the object is loaded.

Use the NO LOG option to turn off logging. If you use NO LOG, you can restore the smart-large-object metadata later to a state in which no structural inconsistencies exist. In most cases, no transaction inconsistencies will exist either, but that result is not guaranteed.

The following statement creates the greek table. Data values for the table are fragmented into the dbs1 and dbs2 dbspaces. The PUT clause assigns the smart-large-object data in the gamma and delta columns to the sb1 and sb2 sbspaces, respectively. The TEXT data values in the eps column are assigned to the blb1 blobspace.

CREATE TABLE greek
(alpha INTEGER,
 beta  VARCHAR(150),
 gamma CLOB,
 delta BLOB,
 eps   TEXT IN blb1)
   FRAGMENT BY EXPRESSION 
   alpha <= 5 IN dbs1, alpha > 5 IN dbs2
   PUT gamma IN (sb1), delta IN (sb2);