Status: Verified
GOAL:
What to do when you hit the record limit for a Type I area?
GOAL:
what to do when you hit the area size limit for a Type I area?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
FIX:
The following are methods to handle data migration from Type I areas which have hit one of the known table size or record number limitations of a Type I area in Progress when full dump and load is not immediately feasible.
A)
Determine if there are any tables that record sequential transactions (i.e. for each new transaction a new sequentially higher record is created and no updates / deletions are performed for any prior records).
If any such tables exists it should be possible to perform the following steps:
1) Dump the Data Definitions of these tables from the Data Dictionary.
2) Modify all references to the area names listed in the .df file to reflect a Type II area to be made later to hold
future data to the table.
3) Create a new Type II storage area.
If necessary define additional extents for future area growth.
4) Use the Data Dictionary tool to rename the old tables whose data definition was dumped and modified in step 1
to something similar to (oldTablename). Any name may be used so long as it is not in use by any existing table.
5) Load the definition file modified in step 2.
6) All new records for the table will be created in the new area.
7) Programmatically dump data or migrate data from the oldTableName and then once all data from the
oldTableName has been dumped / copied to the new area, delete the records from oldTableName.
8) If there are a lot of records it may be useful to purge a limited selection of records in bursts so as not to cause a
significant growth in the BI.
B)
Determine if there are tables which are static (they do not get updated at all but may be read by the application).
If any such tables exist, use the proutil command to move the table and possibly the indices of the table as well.
The same command may also be used to move the indices at the same time and would also a good action if the indices also reside in the Type I area which has hit one of the known record / size limits.
Syntax of tablemove:
proutil <dbname> -C tablemove PUB.<tablename> "AreaNameForTable" ["OptionalAreaForIndices"]
C)
Determine if there are tables which only use a subset of the table data (eg. last day's worth of data, last week's worth of data, etc.).
If any such tables exist it may be possible to perform the following series of steps to avoid hitting the Type I record / area size limit.
1) Dump the definition file for the table using the Data Dictionary.
2) Modify the definition file dumped in step 1 to change all instances of the table name within the file to a new
unique name and all references of the area name to an area name of a new Type II area which will be made
later.
3) Create a new Type II area with the same area name as was referenced in step 2.
4) Load the modified definition file edited in step 2.
5) Programmatically buffer-copy the set of data needed from the old table to the newly created table.
(multiple client sessions may be used to migrate the data if the database is served)
6) When all the necessary subset of data is migrated, use the data dictionary to rename the old table to
&nb.sp; oldTableName.
7) Rename the new table to the original table name.
8) Programmatically make sure that any new records that might have been written to the oldTableName while the
programmatic copy operation was running are migrated to the new table.
9) After steps 1 - 8 are performed, it is advisable to programmatically buffer-copy all remaining records from oldTableName to the new table
and delete the records as they are copied. This may be done over some space of time since the critical
subset of data has already been migrated in steps 1 - 8.
.