Feedback
Did this article resolve your question/issue?

   

Article

How to move a LOB to another Storage Area?

Information

 
TitleHow to move a LOB to another Storage Area?
URL NameP117881
Article Number000128168
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All supported platforms
Other: LOBs
Question/Problem Description
How to move a LOB to another Storage Area?
How to move a BLOB to another Storage Area?
How to move a CLOB to another database Storage Area?
Does tablemove include moving LOB objects?
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.  For example, when the table and related indexes are moved to another Storage Area with PROUTIL -C TABLEMOVE, the related LOBs are left in their existing Storage Area

An enhancement request has been submitted as an Idea on the Progress Community.
Weigh-in with your suggestions and promote the Idea to assist with prioritisation during review by selecting this link:
   
Add a "lobmove" qualifier to proutil

https://openedge.ideas.aha.io/ideas/OPENEDGE-I-606  

Idea submissions are monitored by our Product Management team. Enhancement requests are reviewed during the planning phase of each new product release and a list of the enhancements chosen for implementation can be found in the Release Notes documents that accompany each release.
Workaround
Two methods can be used to move LOB objects to another Storage Area:

Method 1:  Dump and Load
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:  ABL Buffer-Copy
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 the existing records across from the renamed table to the newly created table.

First perform the following steps which are required for both Methods:

1. Backup the database.  This is a precautionary step.
$   probkup <dbname> <outputFile>

2. Add a new Storage Areas to the database to which the LOBs will be moved, if they don't already exist.  

Type II Storage Areas are highly recommended. Information on structuring and adding areas is outside the scope of this Article, please refer to the Documentation and the following Articles: 3. Determine which Tables contain LOBS.  

Output a list of tables containing LOB fields along with their Area and Field names to a file (lobs.txt):
OUTPUT TO lobs.txt.
FOR EACH _storageobject WHERE _storageobject._object-type = 3 NO-LOCK,
  _file WHERE _file-num = _storageobject._object-associate NO-LOCK 
  BY _storageobject._Area-number BY _file._file-name:
  FIND _area WHERE _storageobject._Area-number = _area._Area-number NO-LOCK.
  FIND _field OF _file WHERE _fld-stlen = _storageobject._object-number NO-LOCK. 

  DISPLAY _area._Area-name
          _storageobject._area-number
          _file._file-name 
          _field._field-name with width 132 stream-io.
END.
4. Dump the Data Definitions (.df)

Use the Data Admin tool to dump the application schema definition for each table that contains LOB fields that need to be moved to a new Storage Area.

5. Configure the Schema Definitions for these database objects new Storage Areas 

Using your favourite text editor, edit the .df file(s) from Step 4 so that references for the table, indexes and LOBs are redefined to the new Areas required.

a)  Search for LOB-AREA and for each lob field to be moved, modify the quoted string value to reflect the new Storage Area for that LOB object.
b)  If the associated table data and indexes are also being moved out of their existing Storage Area(s), search for ADD TABLE or ADD INDEX respectively and for each table and/or index modify the AREA quoted string value to reflect the new Storage Area.
c)  When complete, save the df file(s).

Decide which method to use

METHOD 1:  Move the LOBs with dump, delete and load.

1. Dump the data for each table containing LOB fields.

a. With Binary dump, modify the following command for each table to be dumped:
$   proutil <dbname> -C dump <tablename> <outputDirectory> 

b. With an ASCII dump, use the Data Admin tool to dump each table:
  • Admin > Dump data and definitions -> Table Contents (.d file)
  • Enter two output directories: one for the Data and one for the Lobs.  
While the LOB output directory can be the same directory, it can get messy because each individual LOB gets its own file.  2. Delete the Table(s).

UNIX/LINUX: Access the Data Dictionary.
  • Select menus Schema -> Delete Table(s)... -> Use the Enter key to select the lob related tables to be deleted -> Press F1 to delete the table -> Select Yes button to confirm.
Windows: Access the Data Dictionary tool.
  • Select the Tables button -> Select the lob related tables to be deleted -> click the Delete Table button -> click the Yes button to confirm.  Select Edit -> Commit Transaction ->  Click the Yes button to confirm.
3. Load the modified Data Definitions (.df) file(s)

Use the Data Admin tool to load the schema definition with the new Storage Areas
Admin -> Load data and definitions -> Data Definitions (.df file).
Repeat for all modified .df files if there are more than one.

4. Load the data.

a. With Binary load, modify the following command for each table to be loaded:
$   proutil <dbname> -C load </path/filename.bd> build indexes

b. With an ASCII load use the Data Admin tool to load each table:
Admin -> Load data and definitions -> Table Contents (.d file)
Enter the Data and LOB directories used during the dump

METHOD 2: Moving the data and lobs by using BUFFER-COPY between a renamed table and newly created table.

1. Use the Data Dictionary tool to RENAME the original Table.

UNIX/LINUX: Access the Data Dictionary.
  • Select menus Schema -> Modify Table... -> Select the lob related table by pressing Enter -> Rename the table -> Press F1, Enter or Select OK button.
Repeat until all the lob related tables have been renamed.

Windows: Access the Data Dictionary tool.
Select the Tables button -> Select the lob related table -> click the Table Properties button -> Rename the table -> click the OK button.  Then Select Edit -> Commit Transaction -> Click the Yes button to confirm.

2. Load the modified Data Definitions (.df) file(s).

Use the Data Admin tool to load the schema definition with the new Storage Areas
Admin -> Load data and definitions -> Data Definitions (.df file).
Repeat for all modified .df files if there are more than one.

3. Create ABL code to buffer-copy each record of the original table (which has been renamed), to the new table definition loaded in the previous Step (2).

Example pseudo-code:
FOR EACH renamedtable NO-LOCK:
 CREATE originaltablename.
 BUFFER-COPY renamedtable TO originaltablename.
END.
4.  Delete the old (renamed) table.

UNIX/LINUX: Access the Data Dictionary.
  • Select menus Schema -> Delete Table(s)... -> Use the Enter key to select the lob related tables to be deleted -> Press F1 to delete the table -> Select Yes button to confirm.
Windows: Access the Data Dictionary tool.
  • Select the Tables button -> Select the lob related tables to be deleted -> click the Delete Table button -> click the Yes button to confirm.  Edit -> Commit Transaction ->  Click the Yes button to confirm.
Alternative Method to Deleting Tables through the Data Dictionary (in either Method) :

If the associated tables/indexes/lobs that have been moved:
  • Do not share their previous Storage Area with other database objects, or 
  • If other database objects have also since been moved to their new Storage Areas with tablemove or are no longer needed
A faster means of deleting the lob tables in the Methods outlined above is to truncate the area (Since Progress 9.1B), provided:
  • The area concerned is not the "Schema Area" and,
  • After-Imaging is not enabled
$   echo y | proutil -C truncate area [area-name]

After the area has been truncated, indexes will need to be rebuilt in order to delete the remaining schema definitions. The idxbuild is fast, there are no data left after truncate area except for the table definition templates themselves that need to be removed through the Data Dictionary where the definition is deleted.
$ proutil -C idxbuild area "area name"

Then goto the Data Dictionary and remove the table from the Schema.
After this you load the .df again for these tables and then load the data as outlined in the methods above.
Notes
Last Modified Date11/20/2020 6:55 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.