Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Creating a read-only VIEW

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Rome
    Posts
    15
    Execute a query with option WITH UR

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

  3. #3
    Join Date
    Oct 2003
    Location
    Rome
    Posts
    15
    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 ....

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

  5. #5
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    Quote Originally Posted by stolze
    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.

Posting Permissions

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