I'll chime in to offer a different perspective on the relationship of
constraints and maintenance overhead.
Primary, Unique and Foreign Key constraints are implemented implicitly using
indexing technology in DB2 for i5/OS.
Being that Primary & Unique constraints tell query optimizer explicitly "all
of these rows are distinct", they are a great peace of statistic to have
around for query optimizer to use.
Being that all 3 types of constraints I mentioned are indexed, you now have
additional query implementation options for query optimizer (i.e. index only
Back to the overhead. True, updates/inserts/deletes will incur additional
overhead. That said, keep in mind that (non-scientific) observation is that
most applications have 25 to 1 ratio of reads versus writes (on average).
This is especially true for applications written in SQL.
My point is that by introducing constraints on System i, you're not only
enhancing database data integrity, you're potentially IMPROVING the
performance of your application(s).
I've seen this scenario played out more than once (the ever elusive "magical
If, on the other hand, the write to read ratio I mentioned earlier doesn't
hold for your application (i.e. update intensive batch load), maintenance
overhead of additional indexes may trump the potential benefit you'd gain on
your read-only queries, so your mileage WILL vary.
Hope this helps people in decision to use or not use constraints in their
database. I believe that any INFORMED decision is a good decision,
regardless of widely adopted practices.
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
Subject: Re: Which of the SYSIBM tables/views show the row count for
We've had the UNIQUE keyword on logicals and keyed physicals forever, and
not always used it for the simple reason that it added overhead. Should
every primary key have a UNIQUE constraint? That's a business decision.
But if I choose not to have it, it doesn't make my database design bad. It
just means I trust my application and would prefer not to have the extra
overhead of the constraint.
This thread ...
RE: DFU on a production box ... was Which of the SYSIBM tables/views show the row count for, (continued)
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact