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 Specifying disk-storage information and Specifying attribute information 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 System-specified storage characteristics for the sb1 sbspace 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 (A smart large object in a database column) 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.