Static cursors in DB2

[Gustavo Randich]

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

[Serge Rielau]

Gustavo Randich wrote:

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

[Ian]

Serge Rielau wrote:

[snip]

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?

[Gustavo Randich]

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).

[Serge Rielau]

Gustavo Randich wrote:

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.
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 mt*@us.ibm.com
The goal is not to burden customers with this.

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

[Gustavo Randich]

Serge Rielau wrote:

- 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.