By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,559 Members | 2,992 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,559 IT Pros & Developers. It's quick & easy.

Help on "create view" (no locking required)

P: n/a
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?

Dec 6 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.SYSDUMMY1

CREATE VIEW V AS SELECT T.* FROM T, SYSIBM.SYSDUMMY1

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/Fo...Forum2006.html
Dec 6 '06 #2

P: n/a
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"?

Dec 6 '06 #3

P: n/a
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/Fo...Forum2006.html
Dec 6 '06 #4

P: n/a
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

Dec 6 '06 #5

P: n/a

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.

Dec 6 '06 #6

P: n/a
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/Fo...Forum2006.html
Dec 6 '06 #7

P: n/a
aj
Serge Rielau wrote:
Anyway to a view read only without affecting it's performance
simply cross-join with SYSIBM.SYSDUMMY1

CREATE VIEW V AS SELECT T.* FROM T, SYSIBM.SYSDUMMY1

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
Dec 6 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.