DB2 read-only clause and performance

Question: Why do some sql queries use a read-only clause? Is there any performance gain by using the read only clause?

 Answer:Sometimes you will see a sql statement such as :

Select name from mytable for read only

The purpose of using the read only clause is to make the cursor unambiguous. When the read only clause is used , positional updates cannot occur

By unambiguous I mean : Normally the SELECT is turned into a cursor. If the cursor is explicitly defined as a READ ONLY – “blocking” is used. “Blocking” means the DB2 server sends groups of rows across to the client.  Normally DB2 only shows this behaviour on non updatabable cursors e.g joins

The potential result in a multi user environment is to decrease throughput –  as more users are allowed to take shared locks

 Read More

Performance Tuning series – Performance Stack

Database Tuning  overview – Hardware Tuning

OLTP checklist for DB2 configurations.

Database tuning and Operating System Tuning  

Database Tuning overview – Buffer Tuning