1.1.18 CLOB

CLOB is a variable-length character field of undefined maximum length.

CLOBs can be used for saving files into the database and retrieving them so they can be viewed/edited/etc. In RDML and RDMLX, CLOB fields are manipulated as filenames.

Please review the general 1.1.1 Field Type Considerations.

Field Definition Rules

Rules for defining a CLOB field in the repository are:

Length	Length cannot be specified. No decimals.
Valid Keyboard Shift	Blanks, O, E, J or W.
Allowed Attributes	AB, ASQN, CS, FE, LC, ND, RA, RL, RLTB, SUNI. Note: LC and ASQN must always be defined and cannot be removed.
Edit Mask	Not allowed.
Default	*SQLNULL. ASQN will be enabled by default.

Field Definition Notes

• There is no working field type for CLOBs.

Usage Rules

Partition Type	RDMLX Enabled Partition
Files	CLOBS may only be used in RDMLX Files. CLOB fields may be used as real fields. CLOBS must not be used as key fields.
Logical Views	CLOBS must not be used as key fields.
Virtual Fields	Not applicable.
Predetermined Join Fields	Not applicable.
RDML Commands	If a BLOB or CLOB field is used, keep in mind that the field contains a filename, not the actual data in the object. In RDML and RDMLX, LANSA LOB fields will be manipulated as filenames.
Built-In Functions	When used in Built-In Functions, CLOBs are classified as their own types and are not valid for numeric or alpha arguments.
Special Values	*SQLNULL, *NAVAIL, *REMEMBERED_VALUE, *EMPTY
Conversion	CLOBs are treated as file pointers and do not support conversion to a Numeric or Date/Time field type. Refer to Field Type Conversions.

Usage Notes

• There are no working field types for CLOBs.

CLOBs cannot be part of any key (e.g. for Access Routes, etc.)

• You cannot display or print CLOB data.

• If a CLOB field is added to the list, keep in mind that the field contains a filename, not the actual data in the object.

• If an SQL WHERE clause will/may be generated by a condition, these field types may only be compared to *NULL or *SQLNULL; any other comparison will be rejected.

• Rather than the default property .Value, fields of type CLOB have a default property called .FileName to clearly indicate that changing the "value" of the field is actually changing its default property which is a file name property.

• CLOB fields are subject to certain restrictions:

• They cannot be used in SELECT_SQL commands.

• They cannot be used in a condition; the exception is comparison against *NULL, or *SQLNULL.

• Changes may not be logged (and therefore rollback may have no effect) on some or all DBMSs.

• The attribute LC is always enabled, and cannot be disabled. This will affect filenames initially, but could also eventually affect the content for CLOBs.

• The attribute ASQN is always enabled, and cannot be disabled.

• When BLOB and CLOB data is read from the database, files are automatically created in the directory structures under the LPTH= directory (for information, refer to Standard X_RUN Parameters).

Platform Considerations

• When reading CLOB fields on IBM i, the file created on the IFS will have the same CCSID as the native CLOB or DBCLOB column on the database table; no data conversion is performed on the data. That includes DBCLOB columns with CCSID 1200 or 13488.

• Refer to Platform Considerations in 1.1.1 Field Type Considerations.

Tips & Techniques

• The recommended design when using BLOB and CLOB fields is to put them in a separate file from the rest of the fields using the same key as the main file. This forces programmers to do separate IOs to access the BLOB and CLOB data, thus reducing impact on database performance from indiscriminate use of this data. It is also the most portable design ensuring that the non-BLOB and non-CLOB data can be quickly accessed at all times.

• The LOB directory files created on read are occasionally not deleted at the end of your LANSA session. A special process, *LOBCLNUP, can be executed occasionally to cleanup the LOB directory structure. The process needs to be run by a user with sufficient authority to remove files that may have been created by other users. For example, on IBM i, use the following command: LANSA REQUEST(X_RUN) X_RUNADPRM('PROC=*LOBCLNUP')

Also See

1.1.17 BLOB