Re: Multi-member files - Big picture feedback

On 5/26/06, Pete Helgren <Pete@xxxxxxxxxx> wrote:

Let's say that you have the opportunity to rewrite an RPGIII based app
that uses multiple members in files that are poorly normalized.  This is
your opportunity to "do it right". The DB I/O will be in RPG primarily
and your target platform will be System i (although you may have other
JDBC/ODBC apps that access it).

IMHO, you want to get rid of the multi-members and the poor
normalization whichever way the SQL/DDS and RPG/LE/free/embeddedSQL
arguments go.

You want to keep it relatively easy to
maintain because there will be plenty of need for backups, restores,
renaming of DB's either because the customer wants to set up test
environments or your company that provides the software occasionally
will do that either at a customer location at or at their own location.

Question .. if these reasons were not there, would you be considering
making the system difficult to maintain?

You want to be leading edge without making your life miserable.  Which
direction do you "lean"?  To SQL/DDL tables or to DDS/Physical/Logical
files?

I would use SQL/DDL.  Among other reasons, it is straight-forward to
have a SQL script for building the database, and use the script
repeatedly for test environments, etc.

Is there any compelling reason to create multi-member files (which would
rule out SQL)?

None that I can think of.

Our current customers like the multi-member approach
which can be used in service bureau settings (member names reflect
customer "clients").

I used to work with the PRMS ERP system, which used members to
separate "companies", similar to your system.  Many PRMS users
modified that away, putting the companies into separate libraries,
because of the tremendous performance hit from overriding the database
files upon sign-on.

We also use multiple members for denoting data
sets between fiscal years.  Good news and bad news in that approach.
Some records sets in members can reach 200 million records.  Some files
may have 10-20 members.  More, if the customer is a service bureau that
has 20 customers with 10 years of data (200 members per file).

I can see that a customer might not want their client data
inter-mingled, so a valid architecture might be to separate clients
into different libraries (or collections), and then have fiscal year
be a primary key for the relevant tables within the collection.

So, what do you all think?  Should we spend some time investigating
going down the SQL path or should we stick with what we have done in the
past (cleaning up the DB a bit by normalizing it, though).

By all means at least do the investigation.  All you have to lose is some time.

Anyone had experience with this fork in the road and what the outcome was?

You are going to hear both sides of the issue related with fervor and
passion.  I don't necessarily see that for your situation there is a
huge difference.  There will be some learning curve up front, but it
won't be as bad as you might think.

In my view, there are two "soft" reasons that push for SQL and
RPG/ILE/free/embedded SQL ...

1- It is IBM's technical direction.

2- it is a far better career move in my view.

Good luck!