Sbspace extents

As you add smart large objects to a table, the database server allocates disk space to the sbspace in units called extents. Each extent is a block of physically contiguous pages from the sbspace.

Even when the sbspace includes more than one chunk, each extent is allocated entirely within a single chunk so that it remains contiguous. Contiguity is important to I/O performance.

When the pages of data are contiguous, disk-arm motion is minimized when the database server reads the rows sequentially. The mechanism of extents is a compromise between the following competing requirements:
  • The size of some smart large objects is not known in advance.
  • The number of smart large objects in different tables can grow at different times and different rates.
  • All the pages of a single smart large object should ideally be adjacent for best performance when you retrieve the entire object.

Because you might not be able to predict the number and size of smart large objects, you cannot specify the extent length of smart large objects. Therefore, the database server adds extents only as they are needed, but all the pages in any one extent are contiguous for better performance. In addition, when the database server creates a new extent that is adjacent to the previous extent, it treats both extents as a single extent.

The number of pages in an sbspace extent is determined by one of the following methods:
  • The database server calculates the extent size for a smart large object from a set of heuristics, such as the number of bytes in a write operation. For example, if an operation asks to write 30 kilobytes, the database server tries to allocate an extent the size of 30 kilobytes.
  • The final size of the smart large object as indicated by one of the following functions when you open the sbspace in an application program:
    • For DB-Access: the DataBlade® API mi_lo_specset_estbytes function. For more information about the DataBlade API functions to open a smart large object and set the estimated number of bytes, see the HCL OneDB™ DataBlade API Programmer's Guide.
    • For ESQL/C: the ifx_lo_specset_estbytes function. For more information about the functions to open a smart large object and set the estimated number of bytes, see the HCL OneDB ESQL/C Programmer's Manual.

These functions are the best way to set the extent size because they reduce the number of extents in a smart large object. The database server tries to allocate the entire smart large object as one extent (if an extent of that size is available in the chunk).

  • The EXTENT_SIZE flag in the -Df option of the onspaces command when you create or alter the sbspace

    Most administrators do not use the onspaces EXTENT_SIZE flag because the database server calculates the extent size from heuristics. However, you might consider using the onspaces EXTENT_SIZE flag in the following situations:

    • Many one-page extents are scattered throughout the sbspace.
    • Almost all smart large objects are the same length.
  • The EXTENT SIZE keyword of the CREATE TABLE statement when you define the CLOB or BLOB column

    Most administrators do not use the EXTENT SIZE keyword when they create or alter a table because the database server calculates the extent size from heuristics. However, you might consider using this EXTENT SIZE keyword if almost all smart large objects are the same length.

Important: For most applications, you should use the values that the database server calculates for the extent size. Do not use the DataBlade API mi_lo_specset_extsz function or the ifx_lo_specset_extsz function to set the extent size of the smart large object.

If you know the size of the smart large object, it is recommended that you specify the size in the DataBlade API mi_lo_specset_estbytes() function or ifx_lo_specset_estbytes() function instead of in the onspaces utility or the CREATE TABLE or the ALTER TABLE statement. These functions are the best way to set the extent size because the database server allocates the entire smart large object as one extent (if it has contiguous storage in the chunk).

Extent sizes over one megabyte do not provide much I/O benefit because the database server performs read and write operations in multiples of 60 kilobytes at the most. However, the database server registers each extent for a smart large object in the metadata area; therefore, especially large smart large objects might have many extent entries. Performance of the database server might degrade when it accesses these extent entries. In this case, you can reduce the number of extent entries in the metadata area if you specify the eventual size of the smart large object in the mi_lo_specset_estbytes() function or ifx_lo_specset_estbytes() function.

For more information, see Improving metadata I/O for smart large objects.