Help on "create view" (no locking required)

[alessandro menchini]

Hello,

First of all: i apologize for my english...i'm still learning...

If i create a view it seems that DB2 manage this view like an
updateable view.
But I need to create a view "read only", so DB2 never enables lock on
any record...

How can i do that?

[Serge Rielau]

Re: Help on "create view" (no locking required)

alessandro menchini wrote:

Hello,
>
First of all: i apologize for my english...i'm still learning...
>
If i create a view it seems that DB2 manage this view like an
updateable view.
But I need to create a view "read only", so DB2 never enables lock on
any record...
>
How can i do that?
>

Are you concerned about cursors on the view?
The easy way is to define your cursors as READ ONLY. That is, in fact,
good style.Anyway to a view read only without affecting it's performance
simply cross-join with SYSIBM.SYSDUMMY 1

CREATE VIEW V AS SELECT T.* FROM T, SYSIBM.SYSDUMMY 1

DB2 will toss the bogus join out after it decides it to be read only.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

[alessandro menchini]

Re: Help on "create view" (no locking required)

Are you concerned about cursors on the view?

The easy way is to define your cursors as READ ONLY. That is, in fact,
good style.

Can you make me a (simple) example of READ ONLY "cursors on the view"?

[Serge Rielau]

Re: Help on "create view" (no locking required)

alessandro menchini wrote:

>Are you concerned about cursors on the view?
>The easy way is to define your cursors as READ ONLY. That is, in fact,
>good style.

>
Can you make me a (simple) example of READ ONLY "cursors on the view"?
>

CREATE TABLE T(c1 int);
CREATE VIEW v AS SELECT * FROM T;
DECLARE cur1 CURSOR FOR SELECT * FROM V FOR READ ONLY;
OPEN cur1;
....
CLOSE cur1;


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

[alessandro menchini]

Re: Help on "create view" (no locking required)

CREATE TABLE T(c1 int);
CREATE VIEW v AS SELECT * FROM T;
DECLARE cur1 CURSOR FOR SELECT * FROM V FOR READ ONLY;
OPEN cur1;
...
CLOSE cur1;

Thank you for the example!

Ok...now (maybe) i understand...

But...i'm developing an application with Borland Delphi 7,
and i don't know how to use this syntax into an SQL query.

Is your solution equivalent to open an SQL query like this? :

SELECT * FROM V FOR READ ONLY

[Brian Tkatch]

Re: Help on "create view" (no locking required)


alessandro menchini wrote:

CREATE TABLE T(c1 int);
CREATE VIEW v AS SELECT * FROM T;
DECLARE cur1 CURSOR FOR SELECT * FROM V FOR READ ONLY;
OPEN cur1;
...
CLOSE cur1;

>
Thank you for the example!
>
Ok...now (maybe) i understand...
>
But...i'm developing an application with Borland Delphi 7,
and i don't know how to use this syntax into an SQL query.
>
Is your solution equivalent to open an SQL query like this? :
>
SELECT * FROM V FOR READ ONLY

Instead of a SELECT statement, you can use a block:

BEGIN
DECLARE cur1 CURSOR FOR SELECT * FROM V FOR READ ONLY;
OPEN cur1;
CLOSE cur1;
END

Although, using * is probably not a good idea, and the COLUMNs should
be listed explicitly.

B.

[Serge Rielau]

Re: Help on "create view" (no locking required)

alessandro menchini wrote:

>CREATE TABLE T(c1 int);
>CREATE VIEW v AS SELECT * FROM T;
>DECLARE cur1 CURSOR FOR SELECT * FROM V FOR READ ONLY;
>OPEN cur1;
>...
>CLOSE cur1;

>
Thank you for the example!
>
Ok...now (maybe) i understand...
>
But...i'm developing an application with Borland Delphi 7,
and i don't know how to use this syntax into an SQL query.
>
Is your solution equivalent to open an SQL query like this? :
>
SELECT * FROM V FOR READ ONLY
>

Yes. You can simply tag the FOR READ ONLY to the end of your SELECT
string. There may be some option in your driver to have Delphi/DB2 to
that for you unless specified otherwise.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

[aj]

Re: Help on "create view" (no locking required)

Serge Rielau wrote:

Anyway to a view read only without affecting it's performance
simply cross-join with SYSIBM.SYSDUMMY 1
>
CREATE VIEW V AS SELECT T.* FROM T, SYSIBM.SYSDUMMY 1
>
DB2 will toss the bogus join out after it decides it to be read only.
>
Cheers
Serge

Here's another approach:
create view V as
select T.*
from T,table(values( 1)) as dummy(dummy) ;

aj