Informix

 View Only
Expand all | Collapse all

Migrating BLOBs to the filesystem

  • 1.  Migrating BLOBs to the filesystem

    Posted Mon April 29, 2024 08:16 AM

    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
    ------------------------------


  • 2.  RE: Migrating BLOBs to the filesystem

    Posted Tue April 30, 2024 02:18 AM

    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

     




    Original Message