Re: Using joinfiles w/ large databases, referential integrity

On Sat, 21 Sep 2002 10:36:26 GMT, rhairgroveNoSpam_at_Pleasebigfoot.com (Bob Hairgrove) wrote:

>Hi,
>
>I do a lot of database development, but haven't worked with DB2 on
>AS/400 too much since most of my previous work has been in Oracle and
>MS-related stuff on Windows.
>
>We are doing some work now for a company which gets their data from a
>different company which has a large database on DB2 running on AS/400
>or some other IBM mainframe (I don't know how large, but it is large
>.. presumably something in the terabyte range).
>
>Everything is in flat files, no FK constraints, PK constraints are
>only on paper, nothing is journalled, etc. All referential integrity
>and business rules are apparently implemented by server procedures,
>and the data is only in 1st normal form (at best).

Quite often in the AS/400 world data is validated "up front," in just the same way as the MS-SQL world, however since developing MS windows apps and asking questions I've found that often RI is performed in the DB, and the user gets either some criptic message "Update failed due to FK constraint on Customer" Or they get a programmer interprated message "Update failed due to incorrect customer key" from the previous try/except/finally message.

de-normalising a DB is standard practice when performance is an issue, but should not be concidered the starting point... 2nd or 3rd is more "standard," but often the DB design maps the requirements which is more 1 record with multiple RI's, but not further RI's from those... 4th form is more the realms of theory :-)

>
>Since we only read their data, it doesn't really matter to us as long
>as they keep it consistent. Just out of curiosity, though, I asked one
>of their developers why there are no constraints in the database and
>the response was that it is apparently quite painful to rebuild the
>joinfiles (i.e. constraints) with a large database on AS/400 if they
>ever "break" (whatever that means, "painful" meaning a downtime of
>several days).

IMO the AS/400 is fantastic in its solidness.. however that comes at a price, during a power on the AS/400 runs diags on just about everything, from hardware to database consistency... a re-build of logicals and joins after an emergency power down can add hours on start up. But "breaking" is not really an issue in normal use.

As most (or used to be) data access is via "programs" and therefore validation has been performed, it seems redundant to then have the DB re-validate just to enforce integrity.... but that is however a must when using SQL/query/OLEDB access from other non AS/400 programs.

One of the most often spoken comments I've heard from AS/400'ers is "OMG... theres no security if we open up the AS in the same way that MS-SQL is..." because security is enforced via the programs and menus, not within the DB... having to write view security, SP's, RI, and constraints, to prevent spurious updates is a nightmare and serious hard work when its never been needed before. But once done becomes a maintanance issue... ie new file, new SP etc.

>
>So my question to you is: How many people work with such large
>databases and still use constraints with referential integrity
>enforced through joinfiles? Is there another possibility? Or is it
>just too impractical above a certain size? What limits are considered
>too large for DB engine-level RI in the AS/400 and mainframe world?

I think this is one of those "how long a piece of string," questions... But the larger the DB, and the greater the number of RI's the slower a single update becomes. A rough and ready figure used to be: if a physical file has more than 8 views (indexes/logicals) then the performance will degrade exponentially for each single view added. [this is however a very very rough figure!!!!!]

But if a single record add had to update 7 views, and 15 FK's then i'd expect a serious performance loss, especially if the FK's were in the 10,000's of records (i believe there are formular that apply depending on the type of indexing)

>
>As I had always been taught to normalize and use sufficient
>constraints with RI, I must admit that I had trouble getting used to
>the idea that many serious applications don't use them at all.

From the AS POV I feel its a case of "we dont use them because we write the programs to test prior to saving the data," and the programs used to be green screen... however i'm sure that most programmers have had to find out which program deleted the FK record from under a PK file totally mucking up a system :-)

>
>
>Bob Hairgrove
>rhairgroveNoSpam_at_Pleasebigfoot.com
Received on Sat Sep 21 2002 - 19:40:53 CEST