March 17, 2014
By Brian Hansen
dbreorg as hammer and screwdriver
The dbreorg command is very familiar to those who have been long time Infor Lawson customers. It is a program that leverages the captured meta data about a productline that we call a dictionary to automatically move database tables and data from one version to a newer version. This transformation is done in a very deliberate manner so that it can be restarted and successfully recover from most any error it may encounter. It is a capability unmatched in the industry and has served Infor Lawson and our customers well in both Infor Lawson System Foundation (LSF) and Infor Landmark Technology Runtime. In moving to a new definition, dbreorg adds and drops tables, adds and drops columns, transforms data in columns that have changed, and adds, drops, and rebuilds indexes. Tables that are added are empty, columns that are added are "blank," and columns that change are transformed based on the old and new type of the field.
With the increased expressive power of the Infor Java Framework the standard transformations provided by dbreorg are sometimes not sufficient. Sometimes a new table needs to be populated with data or a new column stored with data from another column or table. These actions were often done via application logic and had to be run manually as part of the update. We also have a substantial framework that is used by all the Infor Landmark applications that is continually being improved so those model changes also need to be taken into account during an application update.
Just as one does not use a hammer to drive in screws, Landmark needed a utility designed to better deal with application upgrades.
Our solution to these increasing demands on dbreorg was to introduce a command to be used specifically for application updates – initially just for the GEN product line. The command that resulted was dbupgrade. This command reads a file of upgrade rules and applies those rules to transform the old file data into the new data layout during an export phase followed by a dbreorg phase which loads the upgraded data into the recreated files as well as performing the remaining transformations needed to bring the database tables up to date with the new definitions.
Field assignment file
The key concept we introduced with dbupgrade was the field assignment file. This file consists of assignment statements using expressions similar to those used when writing applications. The following examples are from the file used for GEN upgrades.
ReportPrintFile = ReportDocument where SourceType =
This example states that the new file ReportPrintFile should be loaded with data from the old ReportDocument file using those rows where the SourceType is 9. Fields with the same name in both files will automatically be copied into the row for the new file.
The field assignment file rules need to be flexible enough to handle an update from various older versions of Infor Landmark Technology Runtime. To assist us in writing these rules we provide for guarded blocks of rules surrounded by '#if' and '#end' statements so that the rules are only processed when appropriate. These examples are also from the GEN upgrade rules.
field AsyncActionRequest.ScheduleTimeZone added
field UserPage.BusinessPage dropped and field UserPage.ReportName dropped and field UserPage.UserPageName added
#elseif field UserPage.ReportName dropped
These #if expressions can be arbitrarily complex. You also see from this last example the '#ignore' directive which lets dbupgrade know that it can leave that change for dbreorg.
Because of the utility of field assignment files, we allow them to be used with dbcopy and dbexport and created a new command, dbmodify, to change file data in place.
The product line upgrade.faf file
Originally the -A parameter was required on dbupgrade to select the field assignment file to be used for an upgrade. Over time, we have standardized on the location"$LASRCDIR/<prodline>/upgrade.faf" as the default location for the upgrade rules for a product line. Since the GEN product line consists mostly of framework tables, the rules for framework updates are found in the GEN upgrade.faf file. Starting in Infor Landmark Technology Runtime 10.0.4.0 dbupgrade when run for any data area now automatically loads this upgrade file and it also loads the upgrade file for GEN to ensure that the data area's framework tables are properly transformed.
Some products currently use different files for specific upgrade scenarios. The future direction is that all product upgrades will use this default file for upgrade rules.
Ensuring the upgrade rules are complete
To ensure that all changes to a product line definition have been accounted for, dbupgrade requires that there is a rule for every change. If dbreorg will do the right thing then we will add the rule '#ignore <file>' or '#ignore <file>.<field>' to the upgrade.faf file. The comparedict command can also be used to verify that the rules account for all the differences by specifying the -A parameter. Every time we change the GEN product line we verify the upgrade rules against the GEN definition in each previous release of Infor Landmark Technology Runtime to verify that these rules will allow customers running older releases to successfully upgrade to this new release of Infor Landmark Technology Runtime.
Use dbupgrade by default
To ensure any appropriate rules are processed you should now run dbupgrade any time you create a pending dictionary using the buildprod command. Starting with Infor Landmark Technology Runtime 10.0.4.0 dbreorg will fail with a message indicating you must run dbupgrade when an upgrade is appropriate. The dbreorg command is still needed to deal with failures that occur after the export phase of the upgrade has finished. It is also used to perform garbage collects though the need for that is now rare with Infor Landmark Technology Runtime.
In future releases of Infor Landmark Technology Runtime, dbupgrade and dbreorg will just be two interfaces into the same underlying program at which time dbupgrade will be the preferred interface and dbreorg kept only for scripting compatibility.