How to modify BLOB fields on the iSeries to be greater than 10MB

Date:6 May 2008
Product/Release:LANSA for the iSeries V11
Abstract:How to modify BLOB fields in LANSA for the iSeries to be greater than 10MB
Submitted By:LANSA Technical Support

Description:

In Version 11 when working with BLOBs you may encounter messages indicating that there is a limit of 10MB.

Message ID . . : SQL0404
Message . . . . : Value for column or variable BLBLOB too long.
Cause . . . . . : An INSERT or UPDATE statement or a SET or VALUES INTO statement or a GET
DIAGNOSTICS statement specifies a value that is longer than the maximum length string that can
be stored in BLBLOB.
The length of BLBLOB is 10,485,760 and the length of the string is 35,286,634.
Recovery . . . : Reduce the length of the string from 35286634 to a maximum of 10485760 and try
the request again.

Unlike a Windows database, a field size must be specified when creating BLOB fields on the iSeries. As of V5R1, the iSeries has a BLOB size limit of 2GB. However in LANSA we have set the BLOB default size of 10MB. This is a configurable setting that may be altered as described below.

Solution:

This default BLOB size is controlled by the x_dbmenv.dat file, located in the IFS under the LANSA_[configuration name]\x_lansa\ folder. Underneath the following section:

* ====================================================
* DB2 - iSeries platform (For runtime NOT Development)
* ====================================================

Look for the line:

TYPE_LONGVARBINARY_DEFAULT_LEN=10M

After changing this to an acceptable value for your system, save the x_dbmenv.dat, and redo the check-in (ensure you select to recreate the file)

Note: The x_dbmenv.dat is overwritten when you upgrade LANSA and is set by default at 10MB when performing new installs, so you will need to remember to reconfigure this setting when doing upgrades/new installs.