I have an application (which I'm only the user of, i.e. I cannot change it, but I do have control over the database) that performs relatively simple, but potentially very frequent concurrent look-ups on a table using prepared statements like the following on a DB2 LUW 10.5 system:


Particularly it does not seem to apply uncommitted read isolation. There are no updates on the table while the application reads from it and each read usually only returns a single row.

My questions are:

  • Can I assume there can be performance improvements by adding WITH UR to the statement?
  • If so: Can I expect similar performance improvements by creating a view like CREATE VIEW VIEWNAME AS SELECT * FROM SCHEMA.TABLENAME WITH UR and point the application at that view instead of the original table?

Or more generally: Can I use a view (or other DB-side facilities) to trick the application into using uncommitted read, provided that results in better performance in my scenario?

1 Answer 1


Readers should not block readers, so if no one writes to the table I don't think using UR instead of CS will make much of a a difference. There is a small overhead I guess for applying and releasing the share lock, but my guess is that it will be negligible. I guess only way to find out for sure is to try and see if it affects throughput.

I was unaware that you could add WITH UR in a view, but I tried and according to the plan the isolation level for the table actually changes from CS to UR. I find it a bit weird to be honest that the isolation level is lowered for the statement, without notifying the caller.

Since you are on 10.5 you might want to give CURRENTLY COMMITED a try. Keep an eye open on usage of the log buffer.

  • Hmm okay. So it is likely that the "view filter" would work, but performance gains may be insignificant in my case? Okay, so I would have to set up an actual performance test to find out whether it really doesn't change anything, which probably is the case. Thanks.
    – Wormbo
    Jun 1, 2016 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.