Version : DB2 UDB V8.2

I am asked to created read only views and should not have any locks on the underlying tables while accessing the view.End users are fine to have non commited data as well.I tried to use WITH UR and FOR READ ONLY options in the CREATE VIEW statement.But these options are not allowing.Is there a way to create readonly views??.

Thanks ,
Nagbuchi.

Hi,
1. create view...
2. grant select...
Hope this helps,
Grofaty

Unless you create a materialized query table (MQT), there is no way to prevent locks on the underlying table. Using UNCOMMITTED READ isolation level does not set any read locks, but DB2 must set write locks for such transactions nevertheless.

A read-only view is a view for which DB2 cannot directly determine how to map an update operation on the view back to the underlying tables. Usually, this applies to views that contain joins in the FROM clause. Another point are function calls in the SELECT list.

Note, however, that you can create INSTEAD OF triggers on views. While the view is and remains read-only, you can tell DB2 what to do instead of an insert/update/delete on the view.

Thanks for the clarification,Stolze.

I can't use either MQTs/Triggers since
#1.MQT uses write locks and also consumes extra space on the disk.We don't have enough space on the disk (I was told to create 60views on large tables).
#2.As per our organization standards,we should not create triggers and should not grant insert,update,delete permissions on a view.

If there is no way to prevent the locks,I will use the straight forward way(just grant SELECT permission) to create read-only views.

Thanks,
NagBuchi

What do you mean with "MQT uses write locks"?

An MQT is like any other table. Thus, if data in that table is changed for whatever reason, DB2 must lock the changed data. If someone reads data from that table, DB2 must lock the data being read (unless UNCOMMITTED READ is used).

If you don't grant INSERT, UPDATE, or DELETE on views, why do you bother with making read-only views in the first place?

I didn't want to imply that you should use triggers for anything. I only tried to explain the situation. For example, If I wouldn't have mentioned INSTEAD OF triggers on views, someone may have jumped in and claimed that those views are not truly read-only.

Think about it: a view is just like a macro and it gets compiled into the query. Therefore, each query against a view will (in the end) always query the underlying table. And because DB2 must ensure the data integrity, it has to use locks to synchronize the access to a table. Put both together, and it is obvious that a view can't have any influence on the locking behavior.