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:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.