Re: Articles or links to build a case on why to improve your rpg-db2 database.

Just my opinion, but I'm a firm believer in the single extension file concept, at least when I'm dealing with third-party packages. The original database file does not change, and all additional fields go into a single extension file with the same keys. By leaving IIM the way it is, all existing programs that reference it don't need to be recompiled.

Then, for the extension file, I believe in encapsulating it. I like a single program that updates the file, accessed with a version number. Typically, I have two parameters: a control data structure and a data data structure (not to be too redundant). The control data structure includes the version of the data file. The I/O program recognizes the version and handles differences. That way, calling programs don't have to change when the database changes.

Read-only access can be either through the control program or through SQL.

The goal is to have only one program with an F-spec. Then when I have to add another field to the extension file, no other programs need to be recompiled except those that need the new field. It's awesome, but it does require a lot of upfront thought and it's definitely not everyone's cup of tea. It works best in mixed environments where you have everything from advanced ILE to legacy RPG III programs.

There are a couple of other routes, most of which use some combination of SQL, logical views and/or triggers, but each has its own pros and cons. Triggers in particular are very powerful but require some serious architectural discipline. Some version of Alan Campin's trigger mediator approach is vital.

Just my experience.

Our database files here leave a lot to be desired. In our environment
(w/o any change management tool) the trend has been to create an extension
file with a 1 to 1 relationship with the affected file when fields need to
be added to a affected file.

ie Item master file is IIM, so let's create an XIM file that relates to
the IIM file in a one to oen relationship, oops need more fields create an
XIM2 extension file...

If anyone has any links or articles on the benefits of cleaning up or
normalizing or just plain improving the ERP database I would appreciate
them.
Any ammunition is appreciated?

Also, in the past they have re-purposed file fields w/o changing the files
to reflect the new purpose... a lot.

I would like to empower users to create their own reports but with the
state of the database the way it is, that ain't gonna happen, heck i'm a
programmer and i get confused with all the mislabeled fields and extension
files.

Thanks for any links or help.

John