The BLOB and CLOB Field Attributes box in the Data Dictionary specifies which Area these database objects are placed. They can be located in Areas different to where the rest of the fields for the table are stored, but don't have to be. The decision to place them in a different Area should be based on the same criteria as when deciding whether or not to locate any other database object (e.g. indexes) in its own Area, i.e. it should be done for reasons of space, for balancing I/O and reducing I/O contention.
The advantage of storing BLOBs/CLOBs are in a separate Storage Area, is that different Records per Block, Create and Toss Limits values can be used than those for the associated Table and in addition, Type II Storage Area architecture can be used (since OpenEdge 10+) where the number of Blocks per Cluster can also be defined for the Lob-Storage-Area. LOBs in Type II areas share the same benefit as other objects - defining clusters ensures that contiguous space is created for storing the objects, thus eliminating some amount of search time when writing or retrieving the data.
In Type II Storage Areas, possible values of blocks per cluster are: 8, 64, or 512 - the value chosen will depend on the database blocksize and the BLOB/CLOB size. Suppose, for example, the average BLOB is 10MB is a database with 4KB blocksize:
- This means that the object will be stored in around 10240/4 = 2560 blocks in a Type I Storage Area architecture.
- With a blocks per cluster of 512 in a Type II Storage Area architecture, this object would populate 5 clusters.
- For further discussion refer to Article How the size of a LOB is stored and retrieved
LOBS are by nature, '
large'. They are cut into slices (by the 4GL runtime) that are stored separately and each slice is stored as a record fragment. A LOB is saved in (almost) the same format as a regular record. It's not block based. Consider a rare case where there are small size LOB fields less than the database blocksize, the storage engine may then store multiple lobs within a block, just like small records are stored depending on the Records per Block (rpb) configuration and some of these may still be fragmented and stored in multiple database data blocks depending on available space. As such LOB activity generates a lot of BI activity. The BI subsystem needs to be tuned to accommodate this. Refer to the following Articles for further details:
There is a strong argument in defining 1 RPB for Lob areas (Type I or Type II) especially where the lob objects get updated or appended to. LOBS by nature are 'large' so the argument is:
If i'm wasting (max 8KB database blocksize) per lob by setting 1 record per block, that's a small compromise if i'm maximising the addressable space in the database block itself (for Type I and Type II areas) and area (in the case of Type I areas).
Remember in the Type I Storage Area architecture, a 1 rpb gives the maximum addressable space in the storage area as well as each block:
- Type I: 1rpb 512 GB for 8KB
- Type I: 1rpb 256 GB for 4KB
Shared Memory requirements should also be considered. LOB blocks get called into shared memory when the lob-field is explicitly accessed. LOBS are then likely to pollute the MRU/LRU chain. While new features in 10.2B (secondary buffer pool, -B2) and LRUSKIP (10.2B06) may be considered, when the application is heavily LOB based a separate "LOB database" may be best advised instead and/or separate Storage Areas for each Tables LOB objects.
As with general recommendations, the particular configurations for each application environment will be particular to that environment. When planning bear in mind that the space used by LOB objects is not accounted for in DBANALYS reports until OpenEdge 11.6. In previous versions this information can be found programatically for LOBS in the PUB schema. An example is provided in Article:
Prior to OpenEdge 10.2B04, 11.0, if the client creating the LOB is an ODBC client, then please read the following Article
for a known space allocation issue in this regard:
