Forcing uncommitted read access via view?

Question

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:

SELECT A, B, C FROM SCHEMA.TABLENAME WHERE D = ?

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?

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.

• Wormbo: 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.