Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    69

    Question Unanswered: RI - Removal - Will increase performance????

    Ok, Hi All,
    I would like to bring up a general discussion. We are running with mainframe db2 v 6.1. (OS 390). Due to the huge data from our application we are utilising some significant CPU in the host side ( mainframe side).
    Some of the discussions brought up is removing the RI. Will it increase the performance?. Is it going to be any impact?. What kind of impact i can expect?. What's the risk in taking a decision of removing the RI from our database model.


    Thanks,
    Anto.A.R.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Referential integrity must be enforced by some mechanism. But it can be enforced by the application programs that update the data or by the database management system (with declared referential integrity defined), or some combination of the two.

    Some applications that perform updates to DB2 tables with declared DB2 referential integrity, end up doing redundant I/O in their programs to check referential integrity. In other words, the application does I/O to check integrity before performing an update, and then DB2 automatically checks it again when the update is performed.

    For best performance, if the referential integrity is defined in DB2, then application should utilize the DB2 returns codes on updates to determined data integrity, instead of performing it's own integrity checks independent of DB2.

    But if the application is already coded with independent referential integrity checks (which are redundant to the DB2 automatic checks), then you might want to turn off DB2 referential integrity if you trust the application code to do it for you. This would improve performance, but possibly increase the risk of integrity problems.

    More than likely, if you have performance problems, there are a number of issues that need to be looked at in addition to referential integrity. I would suggest you get a qualified DB2 performance consultant to do an evaluation of your application and system. I know some consultants who do this for a fixed price in a relatively short amount of time.

  3. #3
    Join Date
    Mar 2003
    Posts
    69
    Now our Issue has a very high visibility and the client is placing their db consultants to review each and every piece. From application scrath, since we have the app integrity we are planning to remove the RI.
    Thanks -

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just to clarify, RI only effects performance of inserts, updates, and deletes (and load utility with integrity checking), but has no effect on queries (select statements). Good luck.

  5. #5
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37

    Re: RI - Removal - Will increase performance????

    Since you already have some discussions which resulted in the idea of dropping the RI constraints in DB2, I expect all other options have been looked at and dismissed as having significant performance enhancing benefits.

    Some ideas, though, that never hurt to be reconsidered.

    - When DB2 enforces the RI, CPU usage is reduced as the Data Manager component is the one performing the RI check. Application-enforced RI checking would be performed by the RDS component.
    - Rows accessed for declarative RI checking are not required to be passed back to the application (less IO, less network delay), whereas for application-enforced RI, the referenced rows have to be passed back.
    - Application-enforced RI will use indexes based on the SQL involved, which may be quite non-favourable regarding the RI performance.
    - Declrative RI will ensure there is no way around the rules.

    If after considering these you still feel you have no other option, here are few additional points:
    - Large tables can be split (partitioned, segmented, etc.) for allowing DB2 can take advantage of parallel processing.
    - DB2 v 7 has some interesting performance enhancements in the are of complex joins. If you are experiencing performance difficulties in updates in complex joins, upgrading is another thing to look at.
    - You may want to take a closer look at those SQLs involved.
    - Get a good performance monitor and see what the CPU time actually means. I.e., what is DB2 doing while running the CPU up. Is it sorting a lot? If yes, reindexing, partitioning, optimizing buffer pools, adding CPU and memory are all options...
    - Also, as Marcus mentioned, do not expect any improvement in query performance when dropping DB2 RI.

    Finally, you will not need DB2-enforced RI, if the database is fed from a source which has its RI enforced already, and feed does not screw it up.

    HTH

    Julius

  6. #6
    Join Date
    Sep 2003
    Posts
    6

    Exclamation If you partition to increase performance

    I partitioned a table the other day to increase performance (IBM mainframe) and one of the update programs started failing with a 'C900C7'.

    The problem was another program was attempting to update a value of the partition key and if successful it would put the row into a different partition. Like Primary keys/ unique keys, you can not update the columns of that index without first deleting the row and then re-adding the row.

    So be careful if you partition and know how your data is updated.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    - When DB2 enforces the RI, CPU usage is reduced as the Data Manager component is the one performing the RI check. Application-enforced RI checking would be performed by the RDS component.

    Yes, but if the application is already coded and tested with it’s own RI checks, then it is probably not practical to remove the application enforced code and have the DB2 RI error messages translated into user error messages. Not a trivial task for most applicaitons.

    - Rows accessed for declarative RI checking are not required to be passed back to the application (less IO, less network delay), whereas for application-enforced RI, the referenced rows have to be passed back.

    Not much of a factor. For example when adding an employee row, and you want to make sure that the dept_no is valid, you just have to select a dummy value from the department table using the dept_no input. What is needed from DB2 is just the return code (0 if it exists or +100 if not found). In any case, this may not be a significant issue for most applications (but could be for batch programs).

    In addition, using declared RI requires careful evaluation of the return code and translation of the error into something a user might understand (like INVALID DEPT NO). This often requires the use of cross-reference tables to convert the RI SQL error and constraint name to a user error message.

    - Application-enforced RI will use indexes based on the SQL involved, which may be quite non-favourable regarding the RI performance.

    Not likely in most cases if the system is well designed.

    - Declrative RI will ensure there is no way around the rules.

    Absolutely correct. But if the DB2 declared RI has been in effect for awhile (and the programs abended when DB2 RI errors were encountered) they probably have some idea how good the applicaiton enforced RI is.

    SUMMARY
    When DB2 declared RI is carefully integrated into the application design and programming, it performs well and provides maximum integrity. When RI is slapped on after the fact, it provides maximum integrity, but may result in redundant DB2 I/O when the application is performing the same checks in the code.

    Just my opinion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •