Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39

    Unanswered: How to crate a readonly view

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    1. create view...
    2. grant select...
    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39
    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

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nagbuchi
    #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).
    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).

    #2.As per our organization standards,we should not create triggers and should not grant insert,update,delete permissions on a view.
    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.

    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.
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •