|
|
|
|
|
|
| Author |
Message |
Gustavo Randich
*nix forums beginner
Joined: 24 Feb 2005
Posts: 14
|
 Posted: Fri Feb 25, 2005 6:55 pm Post subject:
Re: Static cursors in DB2
|
|
|
Serge Rielau wrote:
| Quote: | - Using the "for myrow as select..." syntax you proposed solved the
problem, both with **AND WITHOUT** read-only declaration.
Even if you extend your example to more rows? If yes I really want to
try it because that would be AI.
From over 4000 rows it begins to "lose" rows even with READ ONLY. To
try it I simply copy-paste the INSERTs to produce 4700+ rows of data. |
In the meantime I'm not so worried because Informix 7.x loses rows at a
much greater rate! It seems like static cursors are an impossible
thing. |
|
| Back to top |
|
 |
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Fri Feb 25, 2005 3:57 pm Post subject:
Re: Static cursors in DB2
|
|
|
Gustavo Randich wrote:
| Quote: | Thank you Serge, you are always alert. It's very nice and clear the
theory, but below is the result of empirical research :)
- Using READ ONLY in the cursor declaration did not solve the problem
(try it).
Hmm, der may be more variables involved here.
- Using the "for myrow as select..." syntax you proposed solved the
problem, both with **AND WITHOUT** read-only declaration.
Even if you extend your example to more rows? If yes I really want to |
try it because that would be AI.
| Quote: | PD: I'm not using MTK, I'm using a home-made translator (using C, Lex,
Yacc, etc.). I've already programmed one for Informix->SQL Server and
one for Informix->Oracle, with full stored-procedure translation and
mimic of Informix behaviours. We code everything in Informix and use it
to port our projects automatically (tons of code).
... presumably not cheap. If you have improvement proposals for the MTK |
feel free to send a note to mtk@us.ibm.com
The goal is not to burden customers with this.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
Gustavo Randich
*nix forums beginner
Joined: 24 Feb 2005
Posts: 14
|
| Posted: Fri Feb 25, 2005 2:49 pm Post subject:
Re: Static cursors in DB2
|
|
|
Thank you Serge, you are always alert. It's very nice and clear the
theory, but below is the result of empirical research :)
- Using READ ONLY in the cursor declaration did not solve the problem
(try it).
- Using the "for myrow as select..." syntax you proposed solved the
problem, both with **AND WITHOUT** read-only declaration.
PD: I'm not using MTK, I'm using a home-made translator (using C, Lex,
Yacc, etc.). I've already programmed one for Informix->SQL Server and
one for Informix->Oracle, with full stored-procedure translation and
mimic of Informix behaviours. We code everything in Informix and use it
to port our projects automatically (tons of code). |
|
| Back to top |
|
|
Ian
*nix forums Guru Wannabe
Joined: 02 Aug 2005
Posts: 262
|
| Posted: Fri Feb 25, 2005 5:57 am Post subject:
Re: Static cursors in DB2
|
|
|
Serge Rielau wrote:
[snip]
| Quote: | We call this "self hosing". No locking protects you there.
|
"Self Hosing" -- that must the Toronto lab equivalent of "eating
your own dog food" :-)
Take off, eh? |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Thu Feb 24, 2005 9:37 pm Post subject:
Re: Static cursors in DB2
|
|
|
Gustavo Randich wrote:
| Quote: | Hello,
I need to know what's wrong in this translation from Informix. Note the
difference in the results, which is due to the UPDATE statement.
ORIGINAL PROGRAM (INFORMIX):
---------------------------
create table a(a int)
;
insert into a values(1)
;
insert into a values(1)
;
insert into a values(2)
;
create temp table c(a smallint)
;
create procedure pepe()
define b smallint;
foreach select a into b from a where a in(1,2)
update a set a = 0 where a = 1;
insert into c values(b);
end foreach;
end procedure
;
execute procedure pepe()
;
select * from c
;
drop procedure pepe
;
drop table a
;
drop table c
;
RESULTS:
-------
1
1
2
TRANSLATION TO DB2:
------------------
create table a (a integer)
@
insert into a values (1)
@
insert into a values (1)
@
insert into a values (2)
@
declare global temporary table session.c (a smallint) on commit
preserve rows
@
create procedure pepe()
begin
declare b smallint;
begin
declare f_foreach_pepe_1 integer default 0;
declare c_foreach_pepe_1 cursor for select a from a where a in (1,
2);
declare continue handler for not found set f_foreach_pepe_1 = 1;
open c_foreach_pepe_1;
fetch_loop_pepe_1:
loop
fetch c_foreach_pepe_1 into b;
if f_foreach_pepe_1 = 1 then leave fetch_loop_pepe_1; end if;
begin
update a set a = 0 where a = 1;
insert into session.c values (b);
end;
end loop;
close c_foreach_pepe_1;
end;
end
@
call pepe()
@
select * from session.c
@
drop procedure pepe
@
drop table a
@
drop table session.c
@
RESULTS:
-------
1
1
First of: *wew* is this code generated by the MTK? |
Here is what I would write:
create procedure pepe()
define b smallint;
for myrow AS select a from a where a in(1,2) do
update a set a = 0 where a = 1;
insert into c values(myrow.a);
end for;
end
%
Having said that here is the explanation for the behaviour.
The cursor over a is not explicitly declared as READ ONLY.
For that reason DB2 will fetch the rows one by one rather than fetching
rows in a batch to be able to support positioned UPDATE/DELETE (WHERE
CURRENT OF ).
So the cursor is actually affected by the update inside the loop which
nips its tail.
We call this "self hosing". No locking protects you there.
Now, I'm curious what would happen in IDS if you had, say, 10000 rows.
Will the entire resultset be buffered before the UPDATE happens, or will
IDS simply expose this behavior a bit later than DB2?
To the best of my knowledge enforcing materialization of resultsets from
cursors is not common for most RDBMS. It only happens as a side-effect
due to e.g. a SORT operation which is entirely optimizer dependent.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab |
|
| Back to top |
|
|
Gustavo Randich
*nix forums beginner
Joined: 24 Feb 2005
Posts: 14
|
| Posted: Thu Feb 24, 2005 8:34 pm Post subject:
Static cursors in DB2
|
|
|
Hello,
I need to know what's wrong in this translation from Informix. Note the
difference in the results, which is due to the UPDATE statement.
ORIGINAL PROGRAM (INFORMIX):
---------------------------
create table a(a int)
;
insert into a values(1)
;
insert into a values(1)
;
insert into a values(2)
;
create temp table c(a smallint)
;
create procedure pepe()
define b smallint;
foreach select a into b from a where a in(1,2)
update a set a = 0 where a = 1;
insert into c values(b);
end foreach;
end procedure
;
execute procedure pepe()
;
select * from c
;
drop procedure pepe
;
drop table a
;
drop table c
;
RESULTS:
-------
1
1
2
TRANSLATION TO DB2:
------------------
create table a (a integer)
@
insert into a values (1)
@
insert into a values (1)
@
insert into a values (2)
@
declare global temporary table session.c (a smallint) on commit
preserve rows
@
create procedure pepe()
begin
declare b smallint;
begin
declare f_foreach_pepe_1 integer default 0;
declare c_foreach_pepe_1 cursor for select a from a where a in (1,
2);
declare continue handler for not found set f_foreach_pepe_1 = 1;
open c_foreach_pepe_1;
fetch_loop_pepe_1:
loop
fetch c_foreach_pepe_1 into b;
if f_foreach_pepe_1 = 1 then leave fetch_loop_pepe_1; end if;
begin
update a set a = 0 where a = 1;
insert into session.c values (b);
end;
end loop;
close c_foreach_pepe_1;
end;
end
@
call pepe()
@
select * from session.c
@
drop procedure pepe
@
drop table a
@
drop table session.c
@
RESULTS:
-------
1
1 |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 2:09 am | All times are GMT
|
|
Loans | Credit Card | Debt Consolidation | General Webmaster Chat | Bankruptcy
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
