TEXT and BYTE (LOB) types


Informix provides the TEXT, BYTE, CLOB and BLOB data types to store very large texts or binary data.

Legacy Informix 4GL applications typically use the TEXT and BYTE types.

Genero BDL does not support the Informix CLOB and BLOB types.


IBM DB2 supports the LONG VARCHAR/CLOB and BLOB/VARGRAPHIC/DBCLOB types for large objects storage.

Additionally, depending on the nchar_mapping DB2 database configuration parameter, the NCLOB type name can be used as a synonym for CLOB with CODEUNITS32 units specifier, or as DBCLOB with CODEUNITS32 or CODEUNITS16 units specifier.

IBM DB2 allows to specify a maximum size of LOB types. This size will determine storage features (data of small LOBs will be implicitly inlined in rows):
CREATE TABLE tab1 ( ... tx CLOB(200K) ... )

With DB2 LUW 11.5, the default maximum size of CLOB/DBCLOB types is 1,048,576 (1M) code units. For the BLOB type, the default is 1,048,576 (1M) bytes.


The DB2 database interface can convert BDL TEXT data to DB2 CLOB and BYTE data to DB2 BLOB.

In programs, when creating a table with TEXT or BYTE columns, the ODI driver will convert the type to the native DB2 SQL types CLOB, NCLOB or BLOB.

Based on the FGLPROFILE entry dbi.database.dbname.ifxemul.nationalchars, the TEXT type name in SQL statements is converted to CLOB or to NCLOB DB2 types, which is then interpreted by DB2 to CLOB or DBCLOB types, depending on the nchar_mapping database parameter.

By default, TEXT/BYTE SQL types are mapped to DB2 CLOB, NCLOB and BLOB types with a size of 500K. A different size can be specified for all TEXT and BYTE columns created from programs, with the dbi.database.dbname.ifxemul.datatype.typename.size FGLPROFILE entries:
dbi.database.dsname.ifxemul.datatype.text.size = "500K"
dbi.database.dsname.ifxemul.datatype.byte.size = "2M"
Note: Any native specific LOB type, size or storage option can be specified directly by using dynamic SQL and the /* fglhint_no_ifxemul */ SQL hint:
EXECUTE IMMEDIATE "/* fglhint_no_ifxemul */ ... notes DBCLOB(200K CODEUNITS32) ..." 
The TEXT and BYTE types translation can be controlled with the following FGLPROFILE entries:
dbi.database.dsname.ifxemul.datatype.text = { true | false }
dbi.database.dsname.ifxemul.datatype.byte = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.