How to improve the time required to copy/move LOBs between databases

How to improve the time required to copy/move LOBs between databases.

 

PROUTIL database features exist to move table and/or index database objects, but not LOB (CLOB/BLOB) database objects. 

Moving LOB (CLOB/BLOB) database objects can be achieved programmatically using BUFFER-COPY, which can take a long time with a large database.
 

FOR EACH oldblob.xalblo: 
    CREATE newblob.alblo. 
    BUFFER-COPY oldblob.xalblo TO newblob.alblo. 
    DELETE oldblob.xalblo.
END.

 

The following are generic suggestions on how performance of the LOB move operation can be improved:

Database Considerations:

• Define / size database extents upfront to be large enough to cover existing data. This will avoid the overhead of having to extend a variable extent during the copy operation.
• Use a self-service (shared memory) database connection.
• Size -B parameter to accommodate the largest LOB data, Tune the BI subsystem and Optimize the Area structure of LOB objects.
• Starting the database with no-integrity will improve performance if the risks are understood. Refer to Article:
  Application Performance impact of large LOB creation with COPY-LOB    

  Application Considerations:

• Perform the DELETE operation after the transfer OR run delete operations from a separate client session.
• First BUFFER-COPY the records with the NO-LOBS Option specified, to generate new records / indexes in advance. Then perform a COPY-LOB operation to move the LOB data.

This is not an exhaustive list and additional tuning options may be available.
For further assistance, please contact Progress Professional Services so that a consultant can review the specific environment and present options targeted towards it.

Progress Articles:

Best practices with BLOB/CLOB fields   
How to move a LOB to another Storage Area?