Ask an experienced OpenEdge DBA what to do with an old database and one of the first answers is likely to be: get the data into Type II storage areas. This article explains why, how to tell whether it matters for your database, and how to plan the dump and load that usually gets you there.
Type I and Type II in one paragraph
OpenEdge databases store data in storage areas. In a Type I area, records from many tables can share the same blocks, interleaved as they were created over the years. In a Type II area, space is allocated in clusters, and each cluster belongs to a single table or index. Records of one table sit together, which helps reads of that table, makes space management more predictable, and enables some maintenance operations that work per object. Type II areas have been available for many releases, but plenty of long-lived databases still have tables in Type I areas, often including the original schema area.
Signs that it matters for you
Moving to Type II is not automatically worth the effort. It tends to pay off when:
- large, busy tables live in Type I areas, especially the schema area;
- table scans and reports have slowed as the database has grown;
- database analysis output shows heavy fragmentation or scatter for important tables;
- you want per-table maintenance options that Type II makes possible;
- you are already planning an upgrade with a full test cycle.
If the database is small, the busy tables are already in Type II areas, and performance is fine, leave it alone.
The usual route: dump and load
You cannot simply flip an area from Type I to Type II. The data has to be moved, and the most common approach is a dump and load: build a new database with the area structure you want, dump the data from the old one, and load it into the new one, rebuilding indexes on the way.
OpenEdge offers more than one way to do this, including binary dump and load through proutil, which is generally the fastest for large tables, and the data dictionary's text dump and load, which is slower but simpler for small ones. Which to use, table by table, depends on size and on how much time you have.
Planning the new structure
The dump and load is a chance to design the database layout on purpose:
- group large, busy tables into their own areas, with their indexes in separate areas;
- put small reference tables together;
- choose records-per-block and cluster sizes based on average record sizes from database analysis output, not on defaults copied from an old structure file;
- plan extents and growth so the new database does not start life fragmented.
Write this down as a structure file and review it before the first rehearsal.
Rehearse and time it
As with any irreversible database work, the dump and load is rehearsed on copies of production until it is predictable. The timings that matter are:
- dumping each table;
- loading each table;
- rebuilding indexes;
- post-load checks;
- backing up the new database.
Large tables dominate. Rehearsals often reveal that a handful of tables account for most of the elapsed time, and those are the ones to parallelize: dumping and loading several tables at once, within the limits of your disk and CPU.
Fitting the outage window
If the full dump and load does not fit the outage window the business can give you, there are a few options:
- Parallelize more aggressively across tables, after checking the hardware can take it.
- Move historical data first. Tables that only grow, such as old transaction history, can sometimes be dumped and loaded ahead of time, with only recent changes moved on the night. This needs careful design and testing.
- Split the work. Move the most important tables during the upgrade and the rest in later windows.
- Accept a longer window once, in exchange for a database that is easier to run for years.
The rehearsal numbers make this a business decision rather than a guess.
Checks after loading
Use the same check set every time:
- record counts per table compared with the source;
- index checks on the new database;
- schema comparison between old and new data definitions;
- application smoke tests on the most important transactions;
- a short performance comparison on known queries.
Combining it with an upgrade
A dump and load fits well with an OpenEdge 11 to 12 upgrade: you can build the new database directly on OpenEdge 12 and load into it, rather than converting the old database in place. The old database is never modified, which makes rollback simpler. The trade-off is time. We discuss the conversion side in rehearsing the 11 to 12 database conversion.
Planning a move off a retired release? The OpenEdge Estate and Upgrade Readiness Assessment reviews your release position, database, AppServer topology and customizations, and ends in written findings, a conversion risk register and a sequenced plan. Tell us about your estate.