Two methods can be used to move LOB objects to another Storage Area:Method 1: Dump and LoadDump 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-CopyDump 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 useMETHOD 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.
