Large-Object Data Types

Large-object data types can store extremely large column values, such as images and documents, independently of the column.

Large-Object Data Type:
 
|--+-+-TEXT-+--+----------------------------+-+-----------------|
   | '-BYTE-'  '-IN--+-TABLE--------------+-' |
   |                 +-blobspace----------+   |
   |                 |  (1)               |   |
   |                 '--------family_name-'   |
   |  (2)                                     |
   '--------+-BLOB-+--------------------------'
            '-CLOB-'
 

1. Optical Subsystem only
2. Dynamic Server only

The large object data types can be classified in two categories:

• Simple large objects: TEXT and BYTE
• Smart large objects: CLOB and BLOB

Simple-Large-Object Data Types

These are the simple-large-object data types:

Data Type

Description

TEXT

Stores text data of up to 2³¹ bytes

BYTE

Stores any digitized data of up to 2³¹ bytes

These data types are not recoverable. Do not supply a BYTE value where TEXT is expected. No built-in cast supports BYTE to TEXT data-type conversion.

You cannot create a table with more than approximately 195 BYTE or TEXT columns. (This restriction applies to all varying-length and ROW data types.)

Storing BYTE and TEXT Data

A simple-large-object data type can store text or binary data in blobspaces or in tables. The database server can access a BYTE or TEXT value in one piece. When you specify a BYTE or TEXT data type, you can specify the location in which it is stored. You can store data with the table or in a separate blobspace.

In Dynamic Server, if you are creating a named ROW data type that has a BYTE or TEXT field, you cannot use the IN clause to specify a separate storage space.

The following example shows how blobspaces and dbspaces are specified. The user creates the resume table. The data values are stored in the employ dbspace. The data in the vita column is stored with the table, but the data associated with the photo column is stored in a blobspace named photo_space.

CREATE TABLE resume
   (
   fname         CHAR(15),
   lname         CHAR(15),
   phone         CHAR(18),
   recd_date     DATETIME YEAR TO HOUR,
   contact_date  DATETIME YEAR TO HOUR,
   comments      VARCHAR(250, 100),
   vita          TEXT IN TABLE,
   photo         BYTE IN photo_space
   )
   IN employ 

Smart-Large-Object Data Types (IDS)

A smart-large-object data type stores text or binary data in sbspaces. The database server can provide random access to a smart-large-object value. That is, it can access any portion of the smart-large-object value. These data types are recoverable. The following table summarizes the smart-large-object data types that Dynamic Server supports.

Data Type

Description

BLOB

Stores binary data of up to 4 terabytes (4*2⁴⁰ bytes)

CLOB

Stores text data of up to 4 terabytes (4*2⁴⁰ bytes)

Both of these are built-in opaque data types. Like other opaque types, they cannot be accessed in a database of a non-local database server by a distributed query or by other DML operations, nor can they be returned from a database of another database server by a UDR. For information on accessing BLOB or CLOB values in other databases of the local server, however, see BOOLEAN and Other Built-In Opaque Data Types (IDS).

Smart large object data types are not parallelizable. The PDQ feature of Dynamic Serve has no effect on operations that load or unload BLOB or CLOB values, or that process them in queries or in other DML operations.

For more information about these "smart blob" data types, see the IBM Informix Guide to SQL: Reference.

For information on how to create blobspaces, see your IBM Informix Administrator's Guide.

For information about optical families, see the IBM Informix Optical Subsystem Guide.

For information about the built-in functions that you can use to import, export, and copy smart large objects, see Smart-Large-Object Functions (IDS) and the IBM Informix Guide to SQL: Tutorial.