Migrating BLOBs to the filesystem

Hello everyone. We have a table with a BLOB column and It's size is spiraling out of control. As it stands 90% of the database space is consumed by this one table. It is also impossible to dbexport the database in a timely manner because an unload of this single table takes more than 24 hours. We want to migrate the BLOBS to the filesystem and back them up separately. My first thought was to rename the base table, add a filepath column, do a lotofile on the blobs in batches and update the blob column to NULL. Then create a view that looks like the original table and use INSTEAD OF triggers for updates/inserts/deletes. Is this a feasible approach? Has someone already migrated blobs to the filesystem successfully? Thanks in advance, -Snorri ------------------------------ Snorri Bergmann ------------------------------

Yes, I unloaded a very large table with BLOBs to Filesystem. You can do it with lotofile() if you do not worry about automated filenames. The better way that I took was to create a simple bash script that takes row by row and creates files using lotofile() with a name created from an identifier column in the table. If you have old BLOBs stored as BYTE, it also works with a simple cast doc::blob and you can use lotofile() as well. Mit freundlichen Grüßen - Kind regards Gerd Kaluzinski Consultant Software Services IBM Expert Labs DACH, Data & AI Phone: +49 175 228 1983 EMail: gerd.kaluzinski@de.ibm.com IBM Deutschland GmbH Vorsitzender des Aufsichtsrats: Sebastian Krause Geschäftsführung: Gregor Pillen (Vorsitzender), Nicole Reimer, Gabriele Schwarenthorer, Christine Rupp, Frank Theisen Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562 / WEEE-Reg.-Nr. DE 99369940