I am trying to enhance the performance and concurrency of a legacy application that I support. The tables are very large. I have a complex query that joins six tables. The server connects to the database using isolation level "uncommitted read". When the server executes this query it returns the first 50 rows to the client and then waits for the client to request more or to cancel the request.
When I execute this query I notice that DB2 locks all six tables in IN mode and that the locks are NOT escalation locks. DB2 also acquires six row locks of type NS on SYSIBM.SYSTABLES. One row for each table.
The query is really read-only data so I added the FOR READ ONLY clause to the SELECT statement. This eliminated the six row locks on SYSIBM.SYSTABLES. This is good because I know these row locks could be a source of concurrency problems.
1 - Is there any way to make the table locks go away?
2 - If the table locks remain, can another application update a row that is in the result set of the "active query"?
Please forgive the length of this post. Thank you very much...