Obtain 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 1 and Table 2 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 1 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);

The following table shows the storage characteristics for all smart large objects in the cat_descr column.

Table 1. Storage characteristics for the cat_descr column
Disk-storage characteristic	Column-level value	Specified by PUT clause of CREATE TABLE
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	32 KB (smart-large-object optimizer uses as size estimate)	system-specified
Maximum size of I/O block	Calculated by smart-large-object optimizer	system-specified
Name of sbspace	sb1	IN (sb1)
Logging attribute	OFF	system-specified
Last-access time attribute	ON	KEEP LAST ACCESS

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 1) 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.