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
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
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21589881