Salesforce

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 Number
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
Keyword Phrase
Last Modified Date11/20/2020 7:29 AM

Powered by