Instance crash: playing with cursor's holdability

[4.spam]

Hello.

v8.2.7, windows xp.

---
create procedure cursor_test()
language sql
dynamic result sets 1
begin
declare c1 cursor
--with hold
with return for
select * from sysibm.sysdummy 1;
open c1;
end@

create procedure cursor_test2(ou t p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCA TOR VARYING;

call cursor_test();
commit;
ASSOCIATE RESULT SET LOCATOR (loc1)
WITH PROCEDURE cursor_test;
ALLOCATE C1 CURSOR FOR RESULT SET loc1;
OPEN C1;
FETCH C1 INTO p_out;
CLOSE C1;
end@
---

statement
call cursor_test2(?) ;
leads to instance crash.
All works as expected when I comment "commit" statement in
cursor_test2 procedure or declare c1 cursor as "with hold" in
cursor_test procedure.

Sincerely,
Mark B.

[Knut Stolze]

4.****@mail.ru wrote:

Hello.

v8.2.7, windows xp.

---
create procedure cursor_test()
language sql
dynamic result sets 1
begin
declare c1 cursor
--with hold
with return for
select * from sysibm.sysdummy 1;
open c1;
end@

create procedure cursor_test2(ou t p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCA TOR VARYING;

call cursor_test();
commit;
ASSOCIATE RESULT SET LOCATOR (loc1)
WITH PROCEDURE cursor_test;
ALLOCATE C1 CURSOR FOR RESULT SET loc1;
OPEN C1;
FETCH C1 INTO p_out;
CLOSE C1;
end@
---

statement
call cursor_test2(?) ;
leads to instance crash.
All works as expected when I comment "commit" statement in
cursor_test2 procedure or declare c1 cursor as "with hold" in
cursor_test procedure.

DB2 instances should never ever crash. Period. So you should open a PMR to
get this investigated and fixed.

Does the problem still occur with the latest FP applied? How about Version
9? Maybe that would be an easier and quicker solution...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[4.spam]

DB2 instances should never ever crash. Period. So you should open a PMR to

get this investigated and fixed.

Does the problem still occur with the latest FP applied? How about Version
9? Maybe that would be an easier and quicker solution...

FP14 (v8.2.7) is the latest fixpack.
I can't open PMR or check it with v9.
But it's a serious problem and I appeal to anyone who can open PMR to
do it.

P.S.:
In addition:
This simple statement:
values treat(1 as int);
crashes not only v8.2.7 but v9.1.2 too (both on windows)...
Help, somebody!!!

[Jan M. Nelken]

4.****@mail.ru wrote:

In addition:
This simple statement:
values treat(1 as int);
crashes not only v8.2.7 but v9.1.2 too (both on windows)...
Help, somebody!!!

I will work on this one - although it is an illegal construct instance
should not crash.

With your cursor one - it's similar scenario: commit closes all open
cursors except those defined WITH HOLD.

In addition - it is *not* recommended to issue COMMIT from the SP. But -
instance should not crash.

Jan M. Nelken

[Jan M. Nelken]

4.****@mail.ru wrote:

This simple statement:
values treat(1 as int);
crashes not only v8.2.7 but v9.1.2 too (both on windows)...
Help, somebody!!!

Seems Knut was faster in opening defect about this access violation.
Stay tuned...

Jan M. Nelken

[Knut Stolze]

Jan M. Nelken wrote:

4.****@mail.ru wrote:

>This simple statement:
values treat(1 as int);
crashes not only v8.2.7 but v9.1.2 too (both on windows)...
Help, somebody!!!

Seems Knut was faster in opening defect about this access violation.
Stay tuned...

Apparently, I was. ;-)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Jan M. Nelken]

4.****@mail.ru wrote:

This simple statement:
values treat(1 as int);
crashes not only v8.2.7 but v9.1.2 too (both on windows)...
Help, somebody!!!

Expectd behaviour could be (probably in one of the future versions of DB2):

$ db2 "values treat(1 as int)"
SQL20026N The type "SYSIBM.INTEGER " is not a structured type or is not an
instantiable structured type. SQLSTATE=428DP
Jan M. Nelken

[Jan M. Nelken]

4.****@mail.ru wrote:

Hello.

v8.2.7, windows xp.

---
create procedure cursor_test()
language sql
dynamic result sets 1
begin
declare c1 cursor
--with hold
with return for
select * from sysibm.sysdummy 1;
open c1;
end@

create procedure cursor_test2(ou t p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCA TOR VARYING;

call cursor_test();
commit;
ASSOCIATE RESULT SET LOCATOR (loc1)
WITH PROCEDURE cursor_test;
ALLOCATE C1 CURSOR FOR RESULT SET loc1;
OPEN C1;
FETCH C1 INTO p_out;
CLOSE C1;
end@
---

statement
call cursor_test2(?) ;
leads to instance crash.
All works as expected when I comment "commit" statement in
cursor_test2 procedure or declare c1 cursor as "with hold" in
cursor_test procedure.

My DB2 V9 correctly states that cursor specified is not open:

D:\Working>db2 call cursor_test2(?)
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
Will retry on V8.

Jan M. Nelken

[Knut Stolze]

Jan M. Nelken wrote:

4.****@mail.ru wrote:

>Hello.

v8.2.7, windows xp.

---
create procedure cursor_test()
language sql
dynamic result sets 1
begin
declare c1 cursor
--with hold
with return for
select * from sysibm.sysdummy 1;
open c1;
end@

create procedure cursor_test2(ou t p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCA TOR VARYING;

call cursor_test();
commit;
ASSOCIATE RESULT SET LOCATOR (loc1)
WITH PROCEDURE cursor_test;
ALLOCATE C1 CURSOR FOR RESULT SET loc1;
OPEN C1;
FETCH C1 INTO p_out;
CLOSE C1;
end@
---

statement
call cursor_test2(?) ;
leads to instance crash.
All works as expected when I comment "commit" statement in
cursor_test2 procedure or declare c1 cursor as "with hold" in
cursor_test procedure.

My DB2 V9 correctly states that cursor specified is not open:

D:\Working>db2 call cursor_test2(?)
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501

V8 FP14 crashes indeed when DB2 tries to associate the locator with the
cursor internally (in sqlaAssocLocato r, according to the stack traceback).

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Jan M. Nelken]

4.****@mail.ru wrote:

I can't open PMR or check it with v9.
But it's a serious problem and I appeal to anyone who can open PMR to
do it.

Why you cannot open PMR?

I opened a defect 376747 for this issue against V8 FP14. It may make to
the next Fixpack - FP16 is the earliest opportunity.

If you really need it earlier - then special build could be constructed
- but for that we really would like to see PMR.

This simple statement:
values treat(1 as int);
crashes not only v8.2.7 but v9.1.2 too (both on windows)...

Knut opened defect 376729 for this. It is being worked upon.
Jan M. Nelken