Choosing Storage Characteristics

After initializing an LO-specification structure, you need to ensure that this structure contains the appropriate values for the storage characteristics you want the smart large object to have. Then you pass this LO-specification structure to one of the smart-large-object creation functions (Table 29) so that the smart-large-object optimizer can obtain the storage characteristics to use for the new smart large object.

Obtaining Storage Characteristics

For most smart large objects, all you need to do is obtain the system-specified storage characteristics. When you obtain these storage characteristics for a smart large object, you can specify a location for it and override system-specified attributes.

You would probably want to modify the storage characteristics of the new smart large object in the following cases:

• Your application needs to obtain extra performance.

  You can use other LO-specification accessor functions to change the disk-storage information of a new smart large object. For more information, see Defining User-Specified Storage Characteristics.

• You want to use the storage characteristics of an existing smart large object.

  The mi_lo_stat_cspec( ) function can obtain the storage characteristics of an open smart large object through its LO-status structure. For more information, see Copying Storage Characteristics from an Existing Smart Large Object.

Using the Storage-Characteristics Hierarchy

Dynamic Server uses the storage-characteristics hierarchy, which Figure 28 shows, to obtain the storage characteristics for a new smart large object.

Figure 28. Storage-Characteristics Hierarchy

For a given storage characteristic, any value defined at the column level overrides the system-specified value, and any user-level value overrides the column-level value. Table 31 summarizes the ways to specify disk-storage information for a smart large object.

For most applications, use the values for the disk-storage information that the smart-large-object optimizer determines. If you know the size of the smart large object, it is recommended that you specify this size as a user-specified storage characteristic, instead of as a system-specified or column-level storage characteristic.

For more information on any of the disk-storage information in Table 31, see Disk-Storage Information.

Table 32 summarizes the ways to specify attribute information for a smart large object.

For more information on any of the attributes in Table 32, see Attribute Information.

Table 33 summarizes the ways to specify open-mode information for a smart large object.

For more information on the open mode and the default open mode, see Attribute Information.

Using System-Specified Storage Characteristics

The Database Administrator (DBA) establishes system-specified storage characteristics when he or she initializes the database server and creates an sbspace with the onspaces utility, as follows:

• If the onspaces utility has specified a value for a particular storage characteristic, the smart-large-object optimizer uses the onspaces value as the system-specified storage characteristic.
• If the onspaces utility has not specified a value for a particular storage characteristic, the smart-large-object optimizer uses the system default as the system-specified storage characteristic.

The system-specified storage characteristics apply to all smart large objects that are stored in the sbspace, unless a smart large object specifically overrides them with column-level or user-specified storage characteristics.

The onspaces utility establishes storage characteristics for an sbspace. For the storage characteristics that onspaces can set as well as the system defaults, see Table 31 and Table 32. For example, the following call to the onspaces utility creates an sbspace named sb1 in the /dev/sbspace1 partition:

onspaces -c -S sb1 -p /dev/sbspace1 -o 500 -s 2000
   -Df "AVG_LO_SIZE=32"

Table 34 shows the system-specified storage characteristics for all smart large objects in the sb1 sbspace.

For a smart large object that has system-specified storage characteristics, the smart-large-object optimizer calculates values for all disk-storage information except the sbspace name. The DBA can specify a default sbspace name with the SBSPACENAME configuration parameter in the ONCONFIG file. However, you must ensure that the location (the name of the sbspace) is correct for the smart large object that you create. If you do not specify an sbspace name for a new smart large object, the database server stores it in this default sbspace. This arrangement can quickly lead to space constraints.

Obtaining Column-Level Storage Characteristics

The DBA can establish column-level storage characteristics for a database table with the CREATE TABLE statement. If the table contains a CLOB or BLOB column, the PUT clause of CREATE TABLE can specify the storage characteristics that Table 31 and Table 32 show. This statement stores column-level storage characteristics in the syscolattribs system catalog table.

The column-level storage characteristics apply to all smart large objects whose LO handles are stored in the column, unless a smart large object specifically overrides them with user-specified storage characteristics. Column-level storage characteristics override any corresponding system-specified storage characteristics.

For example, if the sb1 sbspace was defined as Table 34 shows, the following CREATE TABLE statement specifies column-level storage characteristics of a location and last-access time for the cat_descr column:

CREATE TABLE catalog2
(
     catalog_num INTEGER,
     cat_descr CLOB
) PUT cat_descr IN (sb1) (KEEP ACCESS TIME);

Table 35 shows the storage characteristics for all smart large objects in the cat_descr column.

For more information on the syntax of the CREATE TABLE statement, see its description in the IBM Informix: Guide to SQL Syntax.

The following DataBlade API functions obtain column-level storage characteristics for a specified CLOB or BLOB column:

• The mi_lo_colinfo_by_name( ) function allows you to identify the column by the table and column name.
• The mi_lo_colinfo_by_ids( ) function allows you to identify the column by an MI_ROW structure and the relative column identifier.

Both these functions store the column-level storage characteristics for the specified column in an existing LO-specification structure. When a smart-large-object creation function receives this LO-specification structure, it creates a new smart-large-object instance that has these column-level storage characteristics.

For example, the following code fragment obtains the column-level storage characteristics for the emp_picture column of the employee table (Figure 26) and puts them in the LO-specification structure that LO_spec references:

MI_LO_SPEC *LO_spec = NULL;
MI_CONNECTION *conn;
...
mi_lo_spec_init(conn, &LO_spec);
mi_lo_colinfo_by_name(conn, "employee.emp_picture",
   LO_spec);

The call to mi_lo_colinfo_by_name( ) overwrites the system-specified storage characteristics that the call to mi_lo_spec_init( ) put in the LO-specification structure. The LO-specification structure that LO_spec references now contains the column-level storage characteristics for the emp_picture column.

Defining User-Specified Storage Characteristics

You can establish user-specified storage characteristics when you create a new smart large object. DataBlade API functions can specify the storage characteristics that Table 31 and Table 32 show. The user-specified storage characteristics apply only to the particular smart-large-object instance that is being created. They override any corresponding column-level or system-specified storage characteristics.

After you have an LO-specification structure allocated, you can use the appropriate LO-specification accessor functions to set fields of this structure. Accessor functions also exist to retrieve storage-characteristic values from the LO-specification structure. When a smart-large-object creation function receives the LO-specification structure, it creates a new smart-large-object instance that has these user-specified storage characteristics.

The following sections describe how to access each group of storage characteristics in the LO-specification structure.

Accessing Disk-Storage Information

Table 36 shows the disk-storage information with the corresponding LO-specification accessor functions.

For most applications, use the values for the disk-storage information that the smart-large-object optimizer determines. If you know the size of the smart large object, it is recommended that you specify this size in the mi_lo_specset_estbytes( ) function instead of in the onspaces utility or the CREATE TABLE or the ALTER TABLE statement. This mi_lo_specset_estbytes( ) function (and the corresponding ESQL/C ifx_lo_specset_estbytes( ) function) is the best way to set the extent size because the database server can allocate the entire smart large object as one extent. For more information, see Disk-Storage Information.

Accessing Attributes

The LO-specification structure uses a bitmask flag, called an attributes flag, to specify the attributes of a smart large object. Table 37 shows the attribute constants of an LO-specification structure.

The milo.h header file defines the attribute constants: MI_LO_ATTR_LOG, MI_LO_ATTR_NO_LOG, MI_LO_ATTR_KEEP_LASTACCESS_TIME, and MI_LO_ATTR_NOKEEP_LASTACCESS_TIME, MI_LO_ATTR_HIGH_INTEG, and MI_LO_ATTR_MODERATE_INTEG.

Table 38 shows the LO-specification accessor functions for the attribute information.

Masking mutually exclusive flags results in an error. If you do not specify a value for a particular attribute, the database server uses the storage-characteristics hierarchy to determine this information.

For example, the following code fragment specifies the constants to enable logging the last-access time for the attributes flag in the LO-specification structure that LO_spec identifies:

MI_CONNECTION *conn;
MI_LO_SPEC *LO_spec = NULL;
mi_integer create_flgs;
...

if ( mi_lo_spec_init(conn, &LO_spec) != MI_OK )
   /* handle error and exit */
create_flgs = 
   MI_LO_ATTR_LOG | MI_LO_ATTR_KEEP_LASTACCESS_TIME;
if ( mi_lo_specset_flags(LO_spec, create_flgs) != MI_OK )
   /* handle error and exit */

For more information on the attributes of a smart large object, see Attribute Information and the descriptions of the mi_lo_specset_flags( ) and mi_lo_specget_flags( ) functions in the IBM Informix: DataBlade API Function Reference.

Accessing the Default Open Flag

When you open a smart large object, you can specify the open mode for the data. The open mode describes the context in which the I/O operations on the smart large object are performed. The LO-specification structure uses a bitmask flag, called a default-open-mode flag, to specify the default open mode of a smart large object. Table 39 shows the open-mode constants of an LO-specification structure.

The milo.h header file defines the open-mode constants: MI_LO_RDONLY, MI_LO_DIRTY_READ, MI_LO_WRONLY, MI_LO_APPEND, MI_LO_RDWR, MI_LO_TRUNC, MI_LO_RANDOM, MI_LO_SEQUENTIAL, MI_LO_FORWARD, MI_LO_REVERSE, MI_LO_BUFFER, MI_LO_NOBUFFER, MI_LO_LOCKALL, and MI_LO_LOCKRANGE.

Table 40 shows the LO-specification accessor functions for the default-open-mode information.

Masking mutually exclusive flags results in an error. However, you can mask the MI_LO_APPEND constant with another access-mode constant. In any of these OR combinations, the seek operation remains unaffected. The following table shows the effect that each of the OR combinations has on the read and write operations.

For more information on access modes of a smart large object, see Access Modes.

If you do not specify a value for a particular part of the open mode, the database server assumes the following system default open mode when you open a smart large object.

You can specify a different open mode for a particular smart large object when you open a smart large object. For more information on how to open a smart large object, see Opening a Smart Large Object.