How do I make a VIEW read-only?

aj wrote:

DB2 WSE 8.1 FP5
Red Hat Linux AS 2.1

I have simple view definition like:

create view B as
select * from A ;

How can I make B READ-ONLY so that no locks are
ever created when people query B?

In the SQL reference doc, under CREATE VIEW, I see
reference with-options, where you can somehow specify
that a column is read-only, but I get syntax errors
when I try to use this. There's no super or sub
table stuff w/ my view or base table - is that what
the doc is referring to?

Any help appreciated.

aj

The READ ONLY column properties on typed views was introduced in order
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:

CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);

The optimizer will throw the VALUES(1) out, so it should be harmless.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Cool way to do that there, Serge. Plus this helped
me w/ an unrelated thing where I wanted a view to just
return a group of hardcoded values while acting just
like a table (for join purposes).

thanks :)

aj

Serge Rielau wrote:

aj wrote:

DB2 WSE 8.1 FP5
Red Hat Linux AS 2.1

I have simple view definition like:

create view B as
select * from A ;

How can I make B READ-ONLY so that no locks are
ever created when people query B?

In the SQL reference doc, under CREATE VIEW, I see
reference with-options, where you can somehow specify
that a column is read-only, but I get syntax errors
when I try to use this. There's no super or sub
table stuff w/ my view or base table - is that what
the doc is referring to?

Any help appreciated.

aj

The READ ONLY column properties on typed views was introduced in order
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:

CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);

The optimizer will throw the VALUES(1) out, so it should be harmless.

Cheers
Serge

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:37*************@individual.net...

The READ ONLY column properties on typed views was introduced in order
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:

CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);

The optimizer will throw the VALUES(1) out, so it should be harmless.

Cheers
Serge Serge Rielau

Are you suggesting that no share locks are taken on the real table?

Mark A wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:37*************@individual.net...

The READ ONLY column properties on typed views was introduced in order
to create subviews with expressions for columns which are natively
updatable in the parent.
The easiest way to make a view READ ONLY is this:

CREATE VIEW v1 AS SEELCT ....FROM ...., TABLE(VALUES (1)) AS A(a);

The optimizer will throw the VALUES(1) out, so it should be harmless.

Cheers
Serge

Serge Rielau

Are you suggesting that no share locks are taken on the real table?

No. I'm suggesting that this makes the view READ ONLY.
Operating on a hunch here w.r.t. OP's question.
There are various clients (and apps, and users, ...) which are rather
sloppy when it comes to defining a cursor as read only.
So any cursor opened on an updatable query will hold a U lock on the row
it positions even if the isolation level is UR.
This trick will make away with the problem. So UR will take no locks.

Cheers
Serge

PS: I learned that some JDBC drivers by default even presume cursors to
be scrollable... now there is a way to wreck performance.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

I think

grant select on <view name> to user | group <name>

will solve the problem.

Correct me if iam wrong.

Cheers,
Thiru
WantedToBeDBA

Thiru wrote:

I think

grant select on <view name> to user | group <name>

will solve the problem.

Correct me if iam wrong.

Cheers,
Thiru
WantedToBeDBA

From an authorization perspective yes. From a locking perspective, no.
When DB2 for LUW compiles a DML statement that happens independent of
authorization.
Only when the statement executes is authorization validated.
Another user with more authorization can execute the same statement
without needing to recompile.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab