Since DB2 version 4 it has been possible to execute SQL statements using dirty reads. You can decide for each SQL statement whether it should use dirty reads or not. Some of you may say that this is very old news, but I still meet a lot of people who are not aware of this possibility.

Dirty reads happens when your SQL SELECT reads rows inserted or updated by another process, but not yet committed. In terms of data integrity this is very bad and therefore I use the term dirty read. You must carefully consider when you are going to use it and when not. Dirty reads were introduced in DB2 because Oracle had the possibility for ages and also because all lock processing can be avoided and thus leading to better performance.

The easiest way to use dirty reads is to append the phrase WITH UR to your SQL SELECT statement. UR stands for Uncommited Read. According to the DB2 manuals WITH UR is only allowed on SELECT statements returning READ ONLY results, whatever that is. If you run into trouble because of this, you can force a READ ONLY result by using the option FOR FETCH ONLY. I always append FOR FETCH ONLY WITH UR, when I want to make a dirty read SELECT. Then I wount have any trouble at all.

My recommendation is to use dirty reads in all adhoc queries. This includes queries issued from QMF or SPUFI or other products with the same functionality. If you need a 100% correct result at the time you issue the SQL statement then do not use WITH UR. I can't remember when I last issued an adhoc query where a 100% correct result was needed. When you use WITH UR my guess is that one query out of 10000 will encounter uncommitted data that are rolled back. Some of the DB2 gurus even recommend WITH UR for all SQL SELECT statements, also those issued from critical online and batch programs. I do not agree with this attitude, though I understand their arguments. One of the heavy arguments are the savings both in elapsed time and in CPU costs, so do not hesitate to use dirty reads, but use them with care.

Another point about dirty reads are that you can read data from a production environment without causing neither deadlocks nor timeouts for other processes. This is a good thing if you want to look at production data without disturbing the production environment, except for the CPU costs and I/O time used to carry out the SQL statement.

I have used dirty reads a lot and for some reason I do not understand the SQL statement performing the dirty reads may be timed out on rare occasions. If you have an explanation for this I will be happy to hear it.