IBM Support

The del file does not contain LONG VARCHAR data when LOBS TO clause is used in the EXPORT statement with REPLACE option.

Troubleshooting


Problem

When exporting Long Varchar and Clob data, and using the replace function on the Long Varchar data, the resulting DEL file contains a pointer to the Long Varchar data in the Lob file, instead of the data itself.

Symptom

The following is an example of the symptom

db2 describe table TAB1

ColumnSchemaCol Data TypeLengthScaleNulls
LVC1SYSIBMLONG VARCHAR327000Yes
CLOBC2SYSIBMCLOB10485760Yes

2 record(s) selected.

Example:
Long Varchar data when exported without the REPLACE function:
db2 "export to test.del of del lobs to ./LOBS select clobc2, lvc1 from TAB1"
Number of rows exported: 2

cat test.del
"test.del.001.lob.0.6/","ALongVarchar1"
"test.del.001.lob.6.6/","ALongVarchar2"


Long Varchar data when exported with the REPLACE function:
db2 "export to test.del of del lobs to ./LOBS select clobc2, replace(lvc1,'A','F') from TAB1"
Number of rows exported: 2

cat test.del
"test.del.001.lob.0.6/","test.del.001.lob.6.13/"
"test.del.001.lob.19.6/","test.del.001.lob.25.13/"

Cause

This is because the REPLACE function converts CHAR to VARCHAR, and LONG VARCHAR to CLOB(1M). It returns a CLOB(1M) if the first, second or third argument is a LONG VARCHAR.

Diagnosing The Problem

When exporting a del file containing CLOB and LONG VARCHAR data with the replace function being used on the LONG VARCHAR column, the del file contains pointer to the LONG VARCHAR data, instead of LONG VARCHAR data.

Resolving The Problem

Starting DB2 Version 9.5 fix pack 1, LONG VARCHAR data type is deprecated. Consider migrating data type to VARCHAR or CLOB instead of LONG VARCHAR.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Movement - Export","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1","Edition":"Enterprise Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21589881