DB2 v72
AIX 5

This has been a pain-point for me for a long time. I have been unable to figure out how to create a DB2 view or table so that a user with SELECT privledge can not lock the table unintentionally. There are various clients (and apps, and users, ...) which can open up a cursor via a select statement and never close it and therefore locking the table. I have been supplying the for read only clause, but I want to be able to fix this on my side. How can I create a VIEW which will not allow a SELECT to lock down the table.

I found this solution, but it does not appearing to work for me.

http://www.thescripts.com/forum/thread184099.html

Help

Thanks,
Charlie

Execute a query with option WITH UR

SELECT ... FROM TABLE WHERE ... WITH UR

Sorry for the first reply...

A view can be READ ONLY if it is NOT deletable.
A view CAN BE DELETABLE if all of the following are true:

• the FROM clause of the outer subselect identifies only one base table, deletable view, or deletable nested table expression (that is, a nested table expression whose subselect, if used to create a view, would create a deletable view) that is not a catalog table or view
• the outer subselect does not include a GROUP BY clause or HAVING clause
• the outer subselect does not include column functions in the select list
• the select-clause of the outer subselect does not include DISTINCT
• no base table (or underlying base table of a view) in a subquery contained in the subselect is the same as the base table (or underlying base table of a view) in the outer subselect

Try with...

SELECT DISTINCT .... FROM ... WHERE ....

DISTINCT is a bad idea: you change the semantics of the statement and the optimizer may have to throw in a SORT operator. Thus, the result table may have to be materialized and that wrecks performance unnecessarily.

What's the problem with the approach that the OP cited, i.e. Serge's suggestion to introduce a join by adding "TABLE ( VALUES(1) ) AS x(n)" in the outer-most FROM clause?

Another approaches is to add a function call to the select list, e.g. CREATE VIEW ... AS SELECT ..., RAND() FROM ... Essentially, the idea is to change the view definition in such a way that prevents DB2 from establishing a backward-mapping from the columns in the view to the columns of the underlying base table(s). If DB2 doesn't figure out this backward mapping, you have a readonly (aka non-updatable) view.

Note that making a view readonly does not influence the locking behavior on the base table. Shared locks are still acquired and if someone else runs updates on the base table, those shared locks may collide with the U and X locks.

p.s: In literature, people speak about "updatable views" - not "deletable" ones.

The locking behavior on the base table is exactly what i am trying to prevent. I would think this would be a common issue. How to give read access to data to my customer while preventing any chance of him locking the table, on the database end. I dont want my import to fail or hang because somebody opened up the table with control center and walked away leaving it with an UPDATEABLE lock.