Examples

The following examples illustrate range fragmentation in its simple and hybrid forms.

Simple Range-Fragmentation Strategy

In this example, the database server fragments the table according to the following allocations.

The previous table shows allocations that can also be made with an expression-based fragmentation scheme:

As the examples show, the range-fragmentation example requires much less coding to achieve the same results. The same is true for the hybrid-range fragmentation compared to hybrid-expression fragmentation methods.

Column-Major-Range Allocation

The following example demonstrates the syntax for column-major-range allocation, a hybrid-range fragmentation strategy:

Storage Space	Holds Values		Storage Space	Holds Values
db1	100 <= Col1 < 125		db3	150 <= Col1 < 175
db2	125 <= Col1 < 150		db4	175 <= Col1 < 200

This type of fragmentation creates a distribution across dbslices and provides a further subdivision within each dbslice (across the dbspaces in the dbslice) such that when a query specifies a value for col1 (for example, WHERE col1 = 127), the query uniquely identifies a dbspace. To take advantage of the additional subdivision, you must specify more than one dbslice.

Row-Major-Range Allocation

The following example demonstrates the syntax for row-major-range allocation, a hybrid-range fragmentation strategy:

This fragmentation strategy is the counterpart to the column-major-range allocation. The advantages and restrictions are equivalent.

Independent-Range Allocation

The following example demonstrates the syntax for an independent-range allocation, a hybrid-range fragmentation strategy:

In this type of range fragmentation, the two columns are independent, and therefore the range allocations are independent. The range allocation for a dbspace on both columns is the conjunctive combination of the range allocation on each of the two independent columns.

This type of fragmentation does not provide subdivisions within either column. With this type of fragmentation, a query that specifies values for both columns (such as, WHERE col4 = 128 and col5 = 650) uniquely identifies the dbspace at the intersection of the two dbslices identified by the columns independently.

PUT Clause (IDS)

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

PUT Clause:
 
        .-,-----------------------------------------.
        |                  .-,-------.              |
        V                  V         |              |
|--PUT----+-column--IN--(----sbspace-+--)---------+-+-----------|
          |    .-,---------------------------.    |
          |    V                             |    |
          '-(----+-------------------------+-+--)-'
                 +-EXTENT SIZE--kbytes-----+
                 | .-NO LOG-.              |
                 +-+--------+--------------+
                 | '-LOG----'              |
                 | .-HIGH INTEG-.          |
                 +-+------------+----------+
                 | .-NO KEEP ACCESS TIME-. |
                 '-+---------------------+-'
                   '-KEEP ACCESS TIME----'

Element	Description	Restrictions	Syntax
column	Column to store in sbspace	Must contain a BLOB, CLOB, user-defined, or complex data type	Identifier, p. Identifier
kbytes	Number of kilobytes to allocate for the extent size	Must be an integer value	Literal Number, p. Literal Number
sbspace	Name of an area of storage	Must exist	Identifier, p. Identifier

The 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.

A smart large object is contained in a single sbspace. The SBSPACENAME configuration parameter specifies the system default in which smart large objects are created, unless you specify another area.

Specifying more than one sbspace distributes the smart large objects in a round-robin distribution scheme, so that the number of smart large objects in each space is approximately equal. The syscolattribs system catalog table contains one row for each sbspace that you specify in the PUT clause.

When you fragment 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. For an example that uses the PUT clause, see Alternative to Full Logging.

Six storage options are available to store BLOB and CLOB data:

Option: Effect
EXTENT SIZE: Specifies how many kilobytes in a smart-large-object extent.
The database server might round the EXTENT SIZE up so that the extents are multiples of the sbspace page size.
HIGH INTEG: Produces user-data pages that contain a page header and a page trailer to detect incomplete writes and data corruption.
This is the default data-integrity behavior.
KEEP ACCESS TIME: Records, in the smart-large-object metadata, the system time when the smart large object was last read or written.
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. (See also Alternative to Full Logging.)
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.
NO LOG: Turns off logging. This option is the default behavior.

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.