Salesforce

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

« Go Back

Information

 
TitleHow to improve the time required to copy/move LOBs between databases
URL NameHow-to-improve-the-time-required-to-copy-move-LOBs-between-databases
Article Number000172498
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
How to improve the time required to copy/move LOBs between databases.

 
Steps to Reproduce
Clarifying Information
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.




 
Error Message
Defect Number
Enhancement Number
Cause
Resolution
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 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.
Workaround
Notes
Keyword Phrase
Last Modified Date11/20/2020 7:14 AM

Powered by