Informix DataBlade API Programmer's Manual Using Smart Large Objects

To create a smart large object and save its LO handle in the database, take the following steps:

Figure 6-7 shows the first six of these steps that a DataBlade API module uses to insert the smart-large-object data into the emp_picture column of the employee table (Figure 6-3 on page 6-16).

Figure 6-7 Inserting Into a BLOB Column

Before you create a new smart large object, obtain a valid LO-specification structure to hold its storage characteristics. You can obtain an LO-specification structure in either of the following ways:

• Initialize a new LO-specification structure to hold the storage characteristics of a new smart large object with the mi_lo_spec_init() function.
• Obtain an LO-specification structure that holds the storage characteristics of an existing smart large object with the mi_lo_stat_cspec() function.

The mi_lo_spec_init() function is the constructor for the LO-specification structure. It performs the following tasks:

1. It allocates a new LO-specification structure when you provide a NULL-valued pointer as an argument.
2. It initializes all fields of the LO-specification structure (disk-storage information and attributes flag) to the appropriate null values.

Important: Do not handle memory allocation for an LO-specification structure with system memory-allocation routines (such as malloc() or mi_alloc()) or by direct declaration. You must use the LO-specification constructor, mi_lo_spec_init(), to allocate a new LO-specification structure.

When you pass a NULL-valued pointer as the second argument of the mi_lo_spec_init() function, this function allocates an LO-specification structure.

This new LO-specification structure has the current memory duration.

The following code fragment declares a pointer called myspec and initializes this pointer to NULL:

MI_LO_SPEC *myspec;

MI_CONNECTION *conn;

...
/* Allocate a new LO-specification structure */

myspec = NULL;

if ( mi_lo_spec_init(conn, &myspec) != MI_OK )

	handle_error();
/* Perform tasks with LO-specification structure */

...

/* Once finished with LO-specification structure, free it */
if ( mi_lo_spec_free(conn, myspec)!= MI_OK )

	handle_error();

After the execution of mi_lo_spec_init(), the myspec variable points to the newly allocated LO-specification structure. For more information on how to use an LO-specification structure to create a new smart large object, see Choosing Storage Characteristics.

If you provide a second argument that does not point to NULL, the mi_lo_spec_init() function assumes that this pointer references an existing LO-specification structure that a previous call to mi_lo_spec_init() has allocated. An LO-specification pointer that is not NULL allows a DataBlade API module to reuse an LO-specification structure. The following code fragment reuses the LO-specification structure that the LO_spec pointer references when the first_time flag is false:

MI_CONNECTION *conn;

MI_LO_SPEC *LO_spec = NULL;

mi_integer first_time;
...
if ( first_time )

	{
	...
	LO_spec = NULL; /* tell interface to allocate memory */
	first_time = 0; /* set "first_time" flag to false */
	...

	}
if ( mi_lo_spec_init(conn, &LO_spec) != MI_OK )

	{

	/* error */

	}


Important: Before you use an LO-specification structure, make sure that you either call mi_lo_spec_init() with the LO-specification pointer set to NULL, or that you have initialized this pointer with a previous call to mi_lo_spec_init().

Once you have a valid LO-specification structure, you can use the accessor functions to obtain the storage characteristics from this LO-specification structure. For more information, see Defining User-Specified Storage Characteristics. For the syntax of the mi_lo_spec_init() function, see its description in Chapter 15, DataBlade API Function Descriptions.

The mi_lo_spec_init() function initializes the LO-specification structure with values that obtain the system-specified storage characteristics. The system-specified storage characteristics are the defaults that the database server uses. They are the storage characteristics at the bottom of the storage-characteristics hierarchy.

After this initialization, you can change the values in the LO-specification structure so that:

• the new smart large object inherits column-level storage characteristics of a CLOB or BLOB column.
• you provide custom user-specified storage characteristics for the new smart large object.

For more information on storage characteristics and the storage-characteristics hierarchy, see Choosing Storage Characteristics.

The mi_lo_stat_cspec() function copies the storage characteristics from an existing smart large object to an LO-specification structure. This function performs the following tasks:

1. It allocates a new LO-specification structure to hold the storage characteristics.
2. It initializes all fields of this LO-specification structure (disk-storage information and attributes flag) to the values of the storage characteristics of the smart large object whose status information is in the LO-status structure that you pass as an argument.
3. It returns the address of the newly allocated LO-specification structure.

The LO-status structure holds status information for an existing smart large object. You initialize an LO-status structure with the mi_lo_stat() function. For more information on an LO-status structure, see Obtaining the Status of a Smart Large Object.

The following code fragment assumes that the old_LOfd variable has already been initialized as the LO file descriptor of an existing smart large object. This code fragment uses the storage characteristics of the existing smart large object (which the mi_lo_stat() function puts into the LO-specification structure that LO_spec specifies) as the storage characteristics for the new smart large object (which the mi_lo_create() function creates).

MI_LO_HANDLE *LO_hdl = NULL;

MI_LO_STAT *LO_stat = NULL;

MI_LO_SPEC *LO_spec;

MI_LO_FD new_LOfd, old_LOfd;


if ( mi_lo_stat(conn, old_LOfd, &LO_spec) != MI_OK )

	{

	/* handle error and exit */

	}
LO_spec = mi_lo_stat_cspec(LO_stat);

new_LOfd = mi_lo_create(conn, LO_spec, flags, &LO_hdl);

Once you initialize an LO-specification structure, you must ensure that this structure contains the desired storage characteristics. You want the LO-specification structure to contain the appropriate values for the storage characteristics you want the new smart large object to have. You then pass this LO-specification structure to one of the smart-large-object creation functions (Figure 6-5 on page 6-25) so that the smart-large-object optimizer can obtain the storage characteristics to use for the new smart large object.

You can choose the following storage characteristics for a new smart large object:

• Use the system-specified storage characteristics as a basis for obtaining the storage characteristics of a smart large object.

The system-specified storage characteristics are the default storage characteristics for a smart large object.

• Customize the storage characteristics.

You can override the system-specified storage characteristics with one of the following levels of the storage-characteristics hierarchy:

• Storage characteristics defined for a particular CLOB or BLOB column in which you want to store the smart large object

Storage characteristics that are unique to a particular CLOB or BLOB column are called column-level storage characteristics.

• User-specified storage characteristics

Special storage characteristics that you define for this smart large object only are called user-specified storage characteristics.

Important: For most applications, Informix recommends that you use the system-specified values for the disk-storage information. Most DataBlade API modules only need to ensure correct storage characteristics for an sbspace name (the location of the smart large object) and for the smart-large-object attributes.

For more smart large objects, you can use the following steps to obtain storage characteristics:

1. Use the mi_lo_spec_init() function to allocate an LO-specification structure and to initialize this structure to the appropriate null values.

When a storage characteristic in the LO-specification structure has a null value, the smart-large-object optimizer uses the system-specified value for the storage characteristic. The smart-large-object optimizer calculates the system-specified values for disk-storage storage characteristics. Most applications can use these system-specified values. For more information, see Using System-Specified Storage Characteristics.

2. Specify the location of the smart large object to override the default location. You can specify the location as one of the following:
• The name of the sbspace associated with the CLOB or BLOB column in which you want to store the smart large object

To store a new smart large object in a CLOB or BLOB column, use the mi_lo_colinfo_by_name() or mi_lo_colinfo_by_ids() function. These functions obtain the column-level storage characteristics for this column. One of the storage characteristics they obtain is the sbspace name for the column. For more information, see Obtaining Column-Level Storage Characteristics.

• The name of some other sbspace

You might want to specify an sbspace name for a new smart large object that is embedded in an opaque data type. The mi_lo_specset_sbspace() accessor function sets the name of the sbspace in the LO-specification structure. For more information, see Defining User-Specified Storage Characteristics.

3. Optionally, override any attributes for the smart large object with the mi_lo_specset_flags() accessor function.

The system-specified attributes have both logging and last-access time disabled. You might want to enable one or more attributes for the new smart large object. The mi_lo_specset_flags() function sets the attributes flag in the LO-specification structure. For more information, see Defining User-Specified Storage Characteristics.

4. Pass this LO-specification structure to one of the smart-large-object creation functions (mi_lo_create(), mi_lo_copy(), mi_lo_expand(), or mi_lo_from_file()) to create the new smart large object.

The smart-large-object creation function creates a new smart large object that has storage characteristics that the LO-specification structure indicates. For more information, see Initializing an LO-Specification Structure.

You can modify the storage characteristics that these steps obtain if:

• 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() can obtain the storage characteristics of an open smart large object from its LO-status structure. For more information, see Storage Characteristics from an Existing Smart Large Object.

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

Figure 6-8 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. Figure 6-9 summarizes the ways to specify disk-storage information for a smart large object.

For more information on any of the disk-storage information in Figure 6-9, see Disk-Storage Information.

Figure 6-10 summarizes the ways to specify attribute information for a smart large object.

For more information on any of the attributes in Figure 6-10, see Attribute Information.

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 Figure 6-9 on page 6-42 and Figure 6-10 on page 6-43. For example, the following call to the onspaces utility creates an sbspace called sb1 in the /dev/sbspace1 partition:

onspaces -c -S sb1 -p /dev/sbspace1 -o 500 -s 2000

	-Df "AVG_LO_SIZE=32"

Figure 6-11 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.

Important: For new smart large objects, Informix recommends that you use the system-specified values of all disk-storage information except the sbspace name. The smart-large-object optimizer can best determine most of the values of the storage characteristics. Most applications only need to specify an sbspace name for their disk-storage information. Obtaining Column-Level Storage Characteristics

The DBA can establish column-level storage characteristics when he or she creates 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 Figure 6-9 on page 6-42 and Figure 6-10 on page 6-43 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 Figure 6-11 on page 6-44 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)

Figure 6-12 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 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.

Tip: When you use the column-level storage characteristics, you do not usually need to override the name of the sbspace for the smart large object. The sbspace name is specified in the PUT clause of the CREATE TABLE statement.

For example, the following code fragment obtains the column-level storage characteristics for the emp_picture column of the employee table (Figure 6-3 on page 6-16) 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.

You can establish user-specified storage characteristics when you create a new smart large object. DataBlade API functions can specify the storage characteristics that Figure 6-9 on page 6-42 and Figure 6-10 on page 6-43 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.

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

Important: The LO-specification structure, MI_LO_SPEC, is an opaque structure to DataBlade API modules. Do not access its internal structure directly. Informix does not guarantee that the internal structure of MI_LO_SPEC will not change in future releases. Therefore, to create portable code, always use the LO-specification accessor functions to obtain and store values from this structure.

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

Figure 6-13 shows the disk-storage information along with the corresponding LO-specification accessor functions.

For most applications, Informix recommends that you 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, Informix recommends 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 allocates the entire smart large object as one extent.

For more information on the disk-storage information, see Disk-Storage Information. You can also refer to the description of the appropriate LO-specification accessor function in Chapter 15, DataBlade API Function Descriptions.

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

Use the mi_lo_specset_flags() function to override system-specified or column-level attributes in the LO-specification structure and the mi_lo_specget_flags() function to retrieve the attributes flag from the 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.

1. If you need to set more than one attribute, use the C-language bitwise OR operator (|) to mask attribute constants together.
2. Use the mi_lo_specset_flags() accessor function to store the attributes flag in the LO-specification structure.

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. You can also refer to descriptions of the mi_lo_specset_flags() and mi_lo_specget_flags() functions in Chapter 15, DataBlade API Function Descriptions.

Once you have an LO-specification structure that describes the storage characteristics for the new smart large object, you can create the smart large object with one of the smart-large-object creation functions: mi_lo_copy(), mi_lo_create(), mi_lo_expand(), or mi_lo_from_file(). These smart-large-object creation functions perform the following tasks to create a new smart large object:

1. Initialize the LO handle for the new smart large object.

You provide a pointer to an LO handle as an argument to these functions. The creation functions initialize the LO handle with information about the location of the new smart large object.

2. Store the storage characteristics in a user-supplied LO-specification structure for the new smart large object in the meta-data area of the sbspace.

You provide a pointer to an LO-specification structure as an argument to these functions. For more information, see Obtaining the LO-Specification Structure.

3. Open the new smart large object in the specified access mode.

You provide the open mode as an argument to the mi_lo_create(), mi_lo_copy(), and mi_lo_expand() functions. The mi_lo_from_file() function opens a smart large object in read/write mode. For more information, see Opening a Smart Large Object.

4. Write any associated data to the new smart large object.

The mi_lo_copy(), mi_lo_expand(), and mi_lo_from_file() functions specify data to write to the sbspace of the new smart large object.

5. Return an LO file descriptor that identifies the open smart large object.

The LO file descriptor is needed for most subsequent operations on the smart large object. However, this LO file descriptor is only valid within the current database connection.

These smart-large-object creation functions initialize the following data structures for a smart large object:

• An LO handle identifies the location of the smart large object and can be stored in a CLOB, BLOB, or opaque-type column.
• An LO file descriptor identifies the open smart large object.

A DataBlade API module can obtain an LO handle with any of the following methods:

• Any of the smart-large-object creation functions can allocate an LO handle for a new smart large object
• A DataBlade API module can explicitly allocate an LO handle
• A SELECT statement can return an LO handle from a CLOB or BLOB column in the database.

For more information, see Selecting the LO Handle.

Any of the smart-large-object creation functions (Figure 6-5 on page 6-25) can allocate memory for an LO handle when you specify a NULL-valued pointer for the last argument. For example, the code fragment declares a pointer to an LO handle, called LO_hdl, initializes it to NULL, and then calls the mi_lo_create() function to allocate memory for this LO handle:

MI_CONNECTION *conn;

MI_LO_SPEC *LO_spec;

MI_LO_HANDLE *LO_hdl = NULL; /* request allocation */

MI_LO_FD LO_fd;
mi_integer flags;


LO_fd = mi_lo_create(conn, &LO_spec, flags, &LO_hdl);

After the execution of mi_lo_create(), the LO_hdl variable is a pointer to the new LO handle, which identifies the location of the new smart large object.

This new LO handle has a default memory duration of PER_ROUTINE. If you switch the memory duration, the creation function uses the current memory duration for the LO handle that it allocates.

If you provide an LO-handle pointer that does not point to NULL, the smart-large-object creation function assumes that memory has already been allocated for the LO handle and it uses the LO handle that you provide.

You can explicitly allocate an LO handle in either of the following ways:

• Dynamically, with one of the DataBlade API memory-management functions such as mi_alloc():
MI_LO_HANDLE *LO_hdl = mi_alloc(sizeof(MI_LO_HANDLE));
• On the stack, with a direct declaration:
MI_LO_HANDLE my_LOhndl;
MI_LO_HANDLE *LO_hdl2 &my_LOhndl;

However, this LO handle is still an opaque C data structure; that is, it is declared as a flat array of undifferentiated bytes and its fields are not available to the DataBlade API module.

Important: The LO handle structure is the only smart-large-object structure that a DataBlade API module can allocate directly. You must allocate other smart-large-object data structures, such as the LO-specification structure and the LO-status structure, with the appropriate DataBlade API constructor function.

Obtaining an LO File Descriptor

The smart-large-object creation functions (Figure 6-5 on page 6-25) return an LO file descriptor for a smart large object. The LO file descriptor is needed for most subsequent operations on the smart large object. However, this LO file descriptor is only valid within the current database connection.

The following code fragment uses the mi_lo_create() function to generate an LO file descriptor for a new smart large object:

MI_LO_FD LO_fd;
MI_LO_HANDLE *LO_ptr;
MI_LO_SPEC *LO_spec;
MI_CONNECTION *conn;


LO_fd = mi_lo_create(conn, LO_spec, MI_LO_RDONLY, &LO_ptr);


Tip: A return value of zero (0) from a smart-large-object creation function does not indicate an error. The value zero (0) is a valid LO file descriptor. 

To write data to the sbspace of a smart large object, use one of the following smart-large-object functions:

• The mi_lo_write() function begins the write operation at the current LO seek position.

You can obtain the current LO seek position with the mi_lo_tell() function, or you can specify the LO seek position with the mi_lo_seek() function.

• The mi_lo_writewithseek() function performs the seek and write operations with a single function call.

You specify the seek position at which to begin the write operation as arguments to mi_lo_writewithseek().

These functions both write a specified number of bytes from a user-defined character buffer to the open smart large object that an LO file descriptor identifies. The smart-large-object optimizer determines the default extent size for the smart large object based on the amount of data that you write. Therefore, try to maximum the amount of data you write in a single call to mi_lo_write() or mi_lo_writewithseek().

Important: An attempt to write data to an sbspace that does not exist results in an error.

For more information on the syntax of the mi_lo_write() and mi_lo_writewithseek() functions, see their descriptions in Chapter 15, DataBlade API Function Descriptions.

In addition to a write operation, you might also need to perform the following operations on the open smart large object.

The INSERT or UPDATE statement can store the LO handle of a smart large object into the CLOB, BLOB, or opaque-type column. To store a smart large object in the database, follow these steps:

1. Provide the LO handle to the INSERT or UPDATE statement, as follows:
• For a CLOB or BLOB column, provide the LO handle as data for the column.
• For an opaque-type column, store the LO handle in the internal structure of the opaque data type and pass this internal structure as data for the column.
2. Execute the INSERT or UPDATE statement with a DataBlade API function such as mi_exec() or mi_exec_prepared_statement().

Tip: The data of the smart large object is stored when you write it to the sbspace of the smart large object.

When you save the LO handle in the CLOB or BLOB column, the smart-large-object optimizer increments the reference count of the smart large object by one. When you save the LO handle in an opaque-type column, the assign() support function for the opaque type must increment the reference count.

If you create a new smart large object but do not store it in a database column, the smart large object is a temporary smart large object. The database server does not guarantee that temporary smart large objects remain valid once they are closed. When all references to the smart large objects are deleted, the database server deletes the smart large object. For more information, see Deleting a Smart Large Object.

For more information on how to execute an INSERT or UPDATE statement, see Chapter 8, Executing SQL Statements.

Once you have stored the new smart large object in the database, make sure that any resources you no longer need are freed. When you create a new smart large object, you might need to free resources of the following data structures:

• The LO-specification structure
• The LO handle

If any of the smart-large-object data structures have a memory duration of PER_ROUTINE, the database server automatically frees the structure when the user-defined routine completes.

The mi_lo_spec_init() function allocates an LO-specification structure in the current memory duration. Therefore, if an LO-specification structure has a memory duration of PER_ROUTINE, the database server automatically frees it when the user-defined routine completes.

To explicitly free the resources assigned to an LO-specification structure, use the mi_lo_spec_free() function. The mi_lo_spec_free() function is the destructor function for the LO-specification structure. When these resources are freed, they can be reallocated to other structures that your program needs.

The LO handle structure is allocated with the current memory duration. Therefore, if it has a memory duration of PER_ROUTINE, the database server automatically frees it when the user-defined routine completes.

To explicitly free the resources assigned to an LO handle, you can use:

• The mi_lo_release() function frees resources of a temporary smart large object.

A temporary smart large object is one that was created in a DataBlade API module but was never inserted in a table. The mi_lo_release() function frees an LO handle that the DataBlade API allocated. It is the destructor function for an LO handle.

• The mi_free() function frees an LO handle that you have allocated.

If you allocate an LO handle with a DataBlade API memory-management function (such as mi_alloc() or mi_dalloc()), use mi_free() to explicitly free these resources.

When these resources are freed, they can be reallocated to other structures that your program needs.

Suppose you want to create a new smart large object for the cat_descr column in the catalog2 table that contains the following data:

The rain in Spain stays mainly in the plain. In Hartford, 
Hereford, and Hampshire, hurricanes hardly happen.

The following code fragment creates a new smart large object, which assumes the storage characteristics of its column, cat_descr, and then modifies the logging behavior.

#include "int8.h"

#include "mi.h"

#define BUFSZ 10000

{
	MI_CONNECTION *conn;
	MI_LO_SPEC *create_spec = NULL;
	MI_LO_HANDLE *descrip = NULL;
	MI_LO_FD lofd;
	char buf[BUFSZ];

	mi_integer buflen = BUFSZ;

	mi_int8 offset, est_size;

	mi_integer numbytes;
...
/* Allocate and initialize the LO-specification structure 

**/

	if ( mi_lo_spec_init(conn, &create_spec) == MI_ERROR )

		handle_lo_error("mi_lo_spec_init()");

/* Obtain the following column-level storage characteristics

** for the cat_desc column:

**           sbspace name = sb1 (this sbspace must already exist)

**            keep last access time is ON  */
	if ( mi_lo_colinfo_by_name(conn, "catalog2.cat_descr",

			create_spec) == MI_ERROR )
		handle_lo_error("mi_lo_colinfo_by_name()");

/* Provide user-specified storage characteristics:

**          logging behavior is ON

**          size estimate of 2 kilobytes   */

	mi_lo_specset_flags(create_spec, MI_LO_ATTR_LOG);
	ifx_int8cvint(2000, &est_size);
	mi_lo_specset_estbytes(create_spec, &est_size)

/* Create an LO handle and LO file descriptor for the new

** smart large object */

	if ( lofd = mi_lo_create(conn, create_spec, MI_LO_RDWR, 

			&descrip) == MI_ERROR )

		handle_lo_error("mi_lo_create()");

/* Copy data into the character buffer 'buf' */

	sprintf(buf, "%s %s %s"

		"The rain in Spain stays mainly in the plain. ",

		"In Hartford, Hereford, and Hampshire, hurricanes",

		" hardly happen.");

/* Write contents of character buffer to the open smart

** large object to which lofd points.  */

	ifx_int8cvint(0, &offset);
	if ( numbytes = mi_lo_writewithseek(conn, lofd, buf,

			buflen, &offset, MI_LO_SEEK_SET) == MI_ERROR )
		handle_lo_error("mi_lo_writewithseek()");

/* Close the LO file descriptor */
	mi_lo_close(lofd);

/* Free LO-specification structure */
	mi_lo_spec_free(conn, create_spec);

After the mi_lo_create() function executes:

• the create_spec LO handle was allocated and identifies the new smart large object.
• the lofd LO file descriptor identifies the open smart large object.
• the new smart large object has user-specified storage characteristics for logging behavior and estimated size.

The smart large object inherits the other storage characteristics. Figure 6-15 shows the complete storage characteristics for this new smart large object.

Figure 6-15
Storage Characteristics for the New Smart Large Object

Storage Characteristic		Value	Specified By
Disk-storage information:
	Size of extent	Calculated by smart-large-object optimizer	system-specified
	Size of next extent	Calculated by smart-large-object optimizer	system-specified
	Minimum extent size	Calculated by smart-large-object optimizer	system-specified
	Size of smart large object	2 kilobytes (smart-large-object optimizer uses as size estimate)	mi_lo_specset_estbytes()
	Maximum size of I/O block	Calculated by smart-large-object optimizer	system-specified
	Name of sbspace	sb1	column-level
Attribute information:
	Logging	ON	mi_lo_specset_flags() with MI_LO_ATTR_LOG
	Last-access time	ON	column-level

Figure 6-12 on page 6-46 shows the column-level storage characteristics for the cat_descr column and Figure 6-11 on page 6-44 shows the system-specified storage characteristics for the sb1 sbspace.

The mi_lo_writewithseek() function writes the buf data to the smart large object that lofd identifies. When the write operation is successful, the descrip LO handle is ready to be stored in the CLOB column with the INSERT statement.

For more information on how to insert a value into a column, see Chapter 8, Executing SQL Statements.