1 GB, is the maximum LOB size supported by OpenEdge:
- BLOB (Binary Large OBject) specifies a database table or temp-table field that contains a BLOB locator, which points to the associated BLOB data stored in the database. BLOB fields may contain any binary byte values ranging from 0 to 255 inclusive.
- CLOB (Character Large OBject) specifies a database table or temp-table field that contains a CLOB locator, which points to the associated CLOB data stored in the database. CLOB fields may contain only character values that are within the CLOB's declared character set. They are like LONGCHAR's but a CLOB is a data type defined in the database with an associated code page (CLOB-CODEPAGE).
How the size of a LOB is stored and retrieved?
The data that is stored in a LOB field is based on the actual size of the LOB and not on the MAX SIZE defined in the Data Dictionary in the Field Editor, or the LOB-SIZE in the .df file for a BLOB/CLOB field.
- If the LOB-SIZE associated with a LOB field is changed, nothing has to be done with the existing data. The existing data is unchanged and not checked against the maximum (LOB-SIZE). The max-size is verified when the lob data is initially stored.
- The MAX SIZE of a LOB field does not affect the maximum record size (32 KB), the LOB itself is stored in the LOB-AREA.
The purpose of the MAX SIZE is to identify the maximum size for the LOB field. The reason is to ensure that something larger than the maximum size cannot be stored within the field that the application may not be prepared to handle:
Example: ADD FIELD "iclob" OF "iTable" AS clob; LOB-SIZE 2M; LOB-AREA "LOBSX"
DEFINE VARIABLE cVariable AS CHARACTER NO-UNDO.
DEFINE VARIABLE lcVariable AS LONGCHAR NO-UNDO.
cVariable = "ABC".
lcVariable = cVariable.
// Create a record and move the data */
CREATE iTable.
COPY-LOB lcVariable to iTable.iclob.
// Read data from the CLOB field
COPY-LOB iTable.iclob to lcVariable.
cVariable = lcVariable.
MESSAGE cVariable.
1. The CLOB field "iclob" stores an internal locator that points to where the CLOB data are stored in the LOB-AREA. This is not stored in the the data record itself which is limited to 32 KB
2. To get the CLOB, the CLOB locator is read from the "iclob" field and then the CLOB data are read using the reference from the row in the LOB-AREA
3. LOB-SIZE 2M - defines a 2 megabyte upper bound for the CLOB value (MAX SIZE), which is verified when the clob-data are initially stored. It does not determine space used to store the clob data in database blocks.
- CLOB = ‘ABC’: The CLOB would use 3 characters = 3 bytes for single-byte ASCII chars.
4. When the CLOB is read, only the bytes containing data are read. As many database blocks that hold this data need to be read from disk into shared memory to retrieve the data.
- 3 bytes would be read logically, the 2MB defined size is not read to get the 3 bytes. This equates to one database block that must be read to get this data. If the MAX SIZE were used, this would lead to an inefficient i/o read of 256 x 8KB database blocks.
The above example is simplified, the topic of LOB storage (and retrieval) is more complex. For example:
a. Creating CLOBS vs BLOBS
A CLOB takes more work to retrieve than a simple VARCHAR or LONGCHAR value due to code page conversion. Equally sized CLOBs are slower than BLOBs, as:
- CLOBs are character based instead of bytes based;
- Characters can be in different encodings.
- CLOBS need to support more complicated operations like copy, truncate, and overlay (basically a text editor that can support multiple languages, ccToUTF8)
b. Database blocks that hold LOB data
LOBS are by nature, 'large' or at least are able to be stored larger than the maximum record size. Creating a LOB uses the record subsystem, where the amount of work the record manager performs is exactly the same operation by operation to load the lob.
LOB activity can be viewed in PROMON or with VST's
- PROMON > R&D, > Other Displays > I/O Operations by User by Lob
- _LobStat and _UserLobStat
For example: <lob> Buffer Hit % = _LobStat._lobstat-osread * 100 / _LobStat._lobstat-read
A LOB is saved in (almost) the same format as a regular record. It's not block based. The LOB data are cut (by the 4GL runtime) into fragments that are stored separately in the same manner that normal data records are, where each slice is stored as a record fragment. When the LOB value is larger than the database blocksize, it will be fragmented across multiple blocks which will all need to be read to retrieve the data with a LONGCHAR or MEMPTR.
Consider a rare case where there are small size LOB fields less than the database blocksize, the storage engine will then store multiple lobs within a block just like small records are stored depending on the Records per Block (rpb) configuration. Some of these may also be fragmented and stored in multiple database data blocks, depending on available space when the database manager searches the RM chain.
As a consequence, LOB activity generates a lot of BI activity. The BI subsystem should be tuned to accommodate this. Refer to the following Articles for further detail:
In OE 12.8.x, When using COPY-LOB from a BLOB field to a LONGCHAR variable, if the BLOB field contains the Unknown value (?) , the LONGCHAR variable is set to the empty string ("").
