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
2. PHOTO TYPE
3. BLOB of PHOTO
Here is what the test.txt file looks like.
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!!