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

Technote (troubleshooting)


Problem(Abstract)

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

Column Schema Col Data Type Length Scale Nulls
LVC1 SYSIBM LONG VARCHAR 32700 0 Yes
CLOBC2 SYSIBM CLOB 1048576 0 Yes

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.

Related information

REPLACE scalar function
LONG VARCHAR deprecated

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
Data Movement - Export

Software version:

9.5, 9.7, 10.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Software edition:

Enterprise Server

Reference #:

1589881

Modified date:

2013-01-31

Translate my page

Machine Translation

Content navigation