How Does the Database Reorganization Utility Work?

Database reorganization is the process of applying the changes you have made to the data dictionary to the database. Reorganize the database whenever you modify the structure of the database through the Database Definition utility (dbdef) and build the data dictionary (blddbdict).

After a database reorganization, the database tables match the database definition. Tables are restructured in alphabetical order, one table at a time. As part of the reorganization, the current data dictionary becomes the old dictionary and the new or future dictionary becomes the active dictionary. The original dictionary is overwritten.

There are three modes for database reorganization: Safe, Fast, and Conservative. The following table shows how each mode works.


Mode	Description
Safe (-S)	Table data is saved in a dump file before the utility alters the table directly. If an alteration fails, dbreorg switches to Conservative mode for the failed table and reload from the dump file.
Fast (-F)	Bypasses the step of saving the table's data in a dump file and alters tables and indexes directly in the database. If an alteration fails, dbreorg switches to Conservative mode for the failed table and perform a dump/load.
Conservative (-C)	Requires longer processing time but ensures that no data is corrupted. dbreorg performs the following steps: Export the contents of the data table to a flat file. Drop the tables and indexes. Create the table with the new definition. Load data from the flat file into the newly defined table. Build indexes. Remove the flat file. Note: If you are reorganizing to move the application data from one database to another, dbreorg automatically uses the `-C` option or conservative mode. For example, if you previously stored your data in the Lawson database and now you are moving your data to a relational database, dbreorg uses this mode.

Other Database Reorganization Utility Options

This section describes the full syntax of the dbreorg utility.

dbreorg [-SCFYdin] [-W WFFile] {{-p dataarea} | Productline}

dbreorg [-G [GCFile]] [-W WFFile] {{-p dataarea} | Productline}

dbreorg [-lcs] {{-p dataarea} | Productline}

dbreorg [-x] {{-p dataarea} | Productline}

dbreorg -L Library Productline

Note: Multiple file names can be separated with a space, tab, carriage return, or comma.


`-Y`	Yes. Do not prompt to continue in Fast mode. (Requires `-F`.)
`-d`	No physical database exists.
`-G file`	Garbage collection. Reorganize only file(s) specified in the garbage collection file. If no garbage collection file is specified, all files are reorganized. The garbage collection file is a list of files to be reorganized. File names specified in the garbage collection file are separated by any of the following: <cr> (carriage return character), spaces, tabs, or commas. Note: The previous syntax for specifying garbage collection is supported for a limited time to give clients an opportunity to update scripts that use the old syntax. Migrate scripts that include garbage collection as soon as possible.
`-i`	Rebuild Data ID dictionaries.
`-l`	List only. List files to be reorganized but do not perform a reorganization. The `-lc` options together provide details about what the dbreorg does.
`-c`	Show file changes. Requires `-l`.
`-s`	List number of records in the file to be reorganized but do not perform the reorganization. Requires `-l`.
`-n`	Do not run srgen.
`-p DataArea`	Reorganize only the specified data area or product line.
`-x`	Examine the control file.
`-W file`	Use work file list.
`-L Library`	The library where you want the NEWIO delivery file placed. Use this option if you have made changes through the Database Definition utility (dbdef) and need to run the split80 program after the dbreorg utility. For more information, see Doc for Developers: Application Development Workbench Standards.

dbreorg Files

The Reorganize Database utility (dbreorg) uses three files:

The reorg.hist log file.
The reorg.rwrk work file.
The reorg.cntl control file.

When you run dbreorg, it writes the changes it has made in the data tables to the log file, reorg.hist. For the data area named test, the log file is located in /EnvironmentDirectory /db/TEST.

The $LAWDIR/dataarea/reorg.rwrk work file is a flat file that contains the data from a single table. dbreorg writes to the work file during the unload phase and reads from it during the load phase. If dbreorg completes successfully, it deletes the work file when it terminates.

You can select any location for the work file. The default location is dataarea. If the disk on which the work file resides becomes full during the reorganization, dbreorg prompts you for the path and name of another work file to which it can write the additional data.

As dbreorg executes, it writes information to the control file, reorg.cntl, describing what step the utility is currently executing. The reorg.cntl file contains a description of all the work that needs to be done and all the work that has been done during the reorganization, providing error detection. When dbreorg successfully completes, it removes the reorg.cntl file. The control file is located in the $LADBDIR/dataarea directory.

If dbreorg terminates abnormally before it has finished reorganizing the product line, it does not remove the control file. If you fix the cause of the failure and run dbreorg again, it can pick up where it left off by reading the information in the reorg.cntl file.

Troubleshooting the Database Reorganization Process

Before you reorganize a database, always create and view a list of the potential changes that will result to identify possible problems.

The -l option lists the files to be reorganized without actually reorganizing them.
The -c option lists file changes. It compares the current dictionary with the new dictionary. To run dbreorg -lc, both versions of the dictionary must be present. That is, blddbdict must have been run a second time to create a new or future dictionary.
The -s option lists the number of records to be changed. The count of changes helps you estimate the amount of time it takes for the reorganization.

Note: Both the -c and -s options require the -l option.

The dbreorg utility stores the list of changes and the number of changes in the reorg.hist log file, located in dataarea. There is one log file for each data area.

When reorganizing a database, the dbreorg utility creates the reorg.cntl control file in dataarea, as well as one or more work files that contain application data from the table currently being reorganized. By default, the work file is stored in dataarea and is named dataarea/reorg.rwrk.

Caution:

Never attempt to remove, edit, or change these files. To do so makes the files unusable, causing the recovery of the database reorganization to fail. Should this happen, file recovery requires using the database backup.

The dbreorg utility uses the reorg.cntl file to restart at the table where it failed, in the correct phase (Unload, Load, or Index).

If dbreorg fails during the Unload phase, restarting unloads data at the first row of the table.
If dbreorg fails during the Load phase, restarting drops the table, recreates it, and then starts reloading data from the first record in the temporary work file.
If dbreorg restarts during the Index phase, it recreates indexes.

These features help you correct errors that caused the dbreorg to fail. Rerun blddbdict to rebuild the input dictionary, if necessary, and restart dbreorg

Only remove the control and work files when a database reorganization fails and cannot be corrected or recovered. If the backup of the database is reloaded, putting the database back to the status prior to the reorganization, the reorg.cntl file and work files must be removed. This removal prevents subsequent database reorganizations from using the work and control files from a previous reorganization.

Caution:

Failing to remove the control and work files after reloading from backup could cause subsequent reorganizations to either fail or to run to completion for only part of the database reorganization, resulting in lost application data.

In a normal state, there are no reorg.cntl or work files present. They exist only if dbreorg is active or if dbreorg has failed and error correction has begun, restarting the dbreorg process.

If a dbreorg Fails

The method of recovering from a failed database reorganization prevents dictionary definitions from becoming inconsistent. In some previous releases, dictionary definitions could become inconsistent when, for example, a dbreorg failed and, before the dictionary problem that caused the failure was resolved, a new version of the dictionary was created. If this situation occurred (which would be most likely to happen only in a large company where several administrators and developers make changes to a database), the dictionary that the failed dbreorg was based on would no longer exist and the problems that caused the failure could not be isolated.

To prevent inconsistencies, dbreorg creates a saved dictionary, which is a copy of the +dictionary (the new, in-progress dictionary). The saved dictionary contains definitions of tables that have been reorganized successfully. If the dbreorg process completes normally, the saved dictionary is deleted as part of the dbreorg process. If dbreorg fails, the saved dictionary, which contains definitions of successfully reorganized tables, exists in the product line. The definitions of failed tables remain in the current dictionary.

The reorg.cntl file contains a record for each table that dbreorg attempted to reorganize that tells which dictionary contains the current definition of the table. The existence of a reorg.cntl file and a saved dictionary file indicate that the active dictionary is in an unstable state. The saved dictionary file contains information that dbreorg makes use of the next time it is run. Do not delete the saved dictionary.

The saved dictionary naming convention includes a unique version stamp. If another dbreorg is run while the dictionary is in an unstable state, a new saved dictionary with a unique version ID exists in the product line. The saved dictionary uses the naming convention SD989697777, where SD stands for "saved dictionary" and where 986997777 is an example of a version number assigned to the file.

If a data reorganization fails, investigate the cause of the failure. After correcting the error, rerun dbreorg. The next time you successfully rerun dbreorg, all saved dictionary files are deleted by dbreorg.