DB2 Export / Import with BLOB data

Jim Youmans
Jim Youmans used Ask the Experts™
on
DB2 10.5 LUW on Windows Server 2016

I have a few tables in production that have photos (as BLOBs) in the rows.  I want to archive them to an archive server and I have tried SSIS but it is very slow due to the fact that SSIS has to write the JPEG to disk and then read it in again.

So someone suggested that I could create an export file with DB2, move the file to the archive server, and then use the load utility to import it.

I have been able to get the export to work, but not the import.

Here is what I have for the export.

db2 "EXPORT TO 'C:\EXPORT\TEST.TXT' OF DELL LOBS TO 'C:\EXPORT\LOBS' MODIFIED BY LOBSINFILE SELECT * FROM Photos FETCH FIRST 1000 ROWS ONLY"

The FETCH FIRST 1000 ROWS ONLY is for testing since there are millions or records.

When I try to import it I use this.

db2 "IMPORT FROM 'c:\export\test.txt' OF DEL LOBS FROM 'c:\export\lobs\test.txt.001.lob' MODIFIED BY lobsinfile INSERT INTO PHOTO_TEST"

It runs but each row gives me the message...

SQL3229W  The field value in column 3 is invalid.  The row was rejected. Reason code: 1

The columns are
1. ID
2. PHOTO TYPE
3. BLOB of PHOTO
4. LASTTIME

Here is what the test.txt file looks like.

10000002,"JPEG","test.txt.001.lob.0.24442/","2013-10-13-15.40.55.705000"
10000004,"JPEG","test.txt.001.lob.24442.25957/","2013-10-13-15.40.58.112000"
10000006,"JPEG","test.txt.001.lob.50399.26155/","2013-10-13-15.41.00.533000"
10000007,"JPEG","test.txt.001.lob.76554.0/","2013-10-13-15.41.05.252000"


I am assuming the 3 column holds the reference to the LOB file.  From what I can tell it should work.  I have been beating my head on this for 2 days.  I have read the IBM reference on this and thought I understood it.  

Can anyone tell me what I am doing wrong?  Thank you!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Jim,

The Export should have written the LOB values to files in the folder C:\EXPORT\LOBS

The exported data looks like this and includes the file name that contains the lob data:

10000002,"JPEG","test.txt.001.lob.0.24442/","2013-10-13-15.40.55.705000"

The Import should only have to identify the folder that contains the lob files.  The file name will be read from the exported row and the lob file read and inserted.

db2 "IMPORT FROM 'c:\export\test.txt' OF DEL LOBS FROM 'c:\export\lobs\' MODIFIED BY lobsinfile INSERT INTO PHOTO_TEST"


Kent
Jim YoumansSr Database Administrator

Author

Commented:
That was it, thank you so much!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial