Rép. : Multi-member files - Big picture feedback

If you want to be leading edge, you go with SQL. Wether or not it is
easier is not relevant since IBM clearly want us to leave DDS.

As for multimember, we baned it here. What we do is setup multiple
libraries (one per year per company...). This way we control what data
is accessed with the library list.

I found that to be much better on all account.

>>> Pete Helgren <Pete@xxxxxxxxxx> 2006-05-26 13:14 >>>
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).  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.
 
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 have seen issues mentioned with SQL collections.  Primarily in saving

a "collection" and then restoring it under a different name.  Or, 
renaming it in place.  True?

How about the relative merits of coding RPG against an SQL collection
vs 
a "traditional" files based library?  Seems to me that IBM has been 
generally discouraging the use of "traditional" files and multiple
members.

Is there any compelling reason to create multi-member files (which
would 
rule out SQL)?  Our current customers like the multi-member approach 
which can be used in service bureau settings (member names reflect 
customer "clients").  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).

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).

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

Thanks,

Pete Helgren