Use large objects (LOBs)

The VARCHAR, VARGRAPHIC, and VARBINARY data types have a limit of 32 KB (where KB equals 1024 bytes) of storage. While this may be sufficient for small to medium-size text data, applications often need to store large text documents. They might also need to store a wide variety of additional data types such as audio, video, drawings, mixed text and graphics, and images. There are three data types to store these data objects as strings of up to 2 GB (where GB equals 1 073 741 824 bytes).

The three data types are: binary large objects (BLOBs), single-byte character large objects (CLOBs), and double-byte character Large objects (DBCLOBs). Each table may have a large amount of associated LOB data. Although a single row containing one or more LOB values cannot exceed 3.5 GB, a table may contain nearly 256 GB of LOB data.

You can refer to and manipulate LOBs using host variables just like any other data type. However, host variables use the program's storage which may not be large enough to hold LOB values. Other means are necessary to manipulate these large values. Locators are useful to identify and manipulate a large object value at the database server and for extracting pieces of the LOB value. File reference variables are useful for physically moving a large object value (or a large part of it) to and from the client.

Understand large object data types (BLOB, CLOB, DBCLOB)
The large object data types are defined here.
Understand large object locators
Large object (LOB) locators use a small, easily managed value to refer to a much larger value.
Example: Use a locator to work with a CLOB value
In this example, the application program retrieves a locator for a LOB value; then it uses the locator to extract the data from the LOB value.
Indicator variables and LOB locators
For normal host variables in an application program, when selecting a NULL value into a host variable, a negative value is assigned to the indicator variable signifying that the value is NULL. In the case of LOB locators, however, the meaning of indicator variables is slightly different.
LOB file reference variables
File reference variables are similar to host variables except that they are used to transfer data to and from IFS files (not to and from memory buffers).
Example: Extract a document to a file
This program example shows how character large object (CLOB) elements can be retrieved from a table into an external file.
Example: Insert data into a CLOB column
This example shows how to insert data from a regular file referenced by :hv_text_file into a CLOB column.
Display layout of LOB columns
When a row of data from a table holding LOB columns is displayed using CL commands such as Display Physical File Member (DSPPFM), the LOB data stored in that row will not be displayed. Instead, the database shows a special value for the LOB columns.
Journal entry layout of LOB columns
These commands return a buffer that gives the user addressability to LOB data that had been journaled.

Parent topic: Process special data types