Feedback
Did this article resolve your question/issue?

   

Article

How to re-org a LOB database when objects are in the Schema Area?

« Go Back

Information

 
TitleHow to re-org a LOB database when objects are in the Schema Area?
URL Name000060129-How-to-re-org-a-LOB-database-when-objects-are-in-the-Schema-Area
Article Number000173021
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: LOBS
Question/Problem Description
How to re-org a LOB database when objects are in the Schema Area?
How to restructure a LOB database?
How to move LOBS to different Storage Areas?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
In the current product implementation, PROUTIL database features exist to move table and/or move index database objects, but not LOB (CLOB/BLOB) database objects.  So when the table and related indexes are moved to another Storage Area, the related LOB objects are left in their existing Storage Area. This feature enhancement has been raised but not implemented to-date.

Two methods can be used to move the LOB objects:

Method 1:  Dump the schema definitions and data for the table, delete the table, modify the schema definitions to use the new Area, load the new schema definitions and the data.

Method 2:  Dump the schema definitions for the table, rename the old table, modify the schema definitions to use the new Area, load the schema definitions and use the ABL to BUFFER-COPY existing records across from the renamed table to the newly created table.

For further instruction on these Methods, refer to Articles: When the database is essentially a "LOB" database, this adds another layer of complexity when the LOB objects are in the Schema Area as it is limited to a maxarea size of 256 GB (actually less with the reserved space, 256 - 5 = 251 GB) because:
  • It is a Type I Storage Area which have the 2^31 rowid limit and 
  • It has hard limits of 32 rpb for 4 KB and 64 rpb for 8 KB database blocksize
  • It cannot be changed to a Type II Storage Area (introduced in OpenEdge 10) and
  • It cannot be truncated with PROUTIL -C truncate area to reclaim the disk space.
If Method 2 (referenced above) is considered, then one would be left with a very large Storage Area in addition to the new areas that the LOBS and related database objects (Table, index) were moved to with BUFFER-COPY. These objects can be moved out of the current Schema Area with the PROUTIL -C mvsch utility safely in OpenEdge 11.6.3, 11.7. Refer to Article Truncate the Schema Area with PROUTIL mvsch    
Workaround
Notes
Last Modified Date11/20/2020 7:29 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.