Avoiding character conversion for LOB locators

In certain situations, Db2 materializes the entire LOB value and converts it to the encoding scheme of a particular SQL statement. This extra processing can degrade performance and should be avoided.

About this task

You can use a VALUES INTO or SET statement to obtain the results of functions that operate on LOB locators, such as LENGTH or SUBSTR. VALUES INTO and SET statements are processed in the application encoding scheme for the plan or package that contains the statement. If that encoding scheme is different from the encoding scheme of the LOB data, the entire LOB value is materialized and converted to the encoding scheme of the statement. This materialization and conversion processing can cause performance degradation.

To avoid the character conversion, SELECT from the SYSIBM.SYSDUMMYA, SYSIBM.SYSDUMMYE, or SYSIBM.SYSDUMMYU sample table. These dummy tables perform functions similar to SYSIBM.SYSDUMMY1, and are each associated with an encoding scheme:

SYSIBM.SYSDUMMYA: ASCII
SYSIBM.SYSDUMMYE: EBCDIC
SYSIBM.SYSDUMMYU: Unicode

By using these tables, you can obtain the same result as you would with a VALUES INTO or SET statement.

Example

Suppose that the encoding scheme of the following statement is EBCDIC:

SET : unicode_hv = SUBSTR(:Unicode_lob_locator,X,Y);

Db2 must materialize the LOB that is specified by :Unicode_lob_locator and convert that entire LOB to EBCDIC before executing the statement. To avoid materialization and conversion, you can execute the following statement, which produces the same result but is processed by the Unicode encoding scheme of the table:

SELECT SUBSTR(:Unicode_lob_locator,X,Y) INTO :unicode_hv
    FROM SYSIBM.SYSDUMMYU;