1 1 18 CLOB

LANSA Technical

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.

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

Ý 1.1 Field Types