469,626 Members | 883 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Instance crash: playing with cursor's holdability

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.sysdummy1;
open c1;
end@

create procedure cursor_test2(out p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCATOR 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.

Apr 16 '07 #1
10 2969
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.sysdummy1;
open c1;
end@

create procedure cursor_test2(out p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCATOR 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
Apr 20 '07 #2
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!!!

Apr 23 '07 #3
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
Apr 23 '07 #4
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
Apr 23 '07 #5
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
Apr 23 '07 #6
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
Apr 23 '07 #7
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.sysdummy1;
open c1;
end@

create procedure cursor_test2(out p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCATOR 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
Apr 24 '07 #8
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.sysdummy1;
open c1;
end@

create procedure cursor_test2(out p_out char(1))
language sql
begin
DECLARE loc1 RESULT_SET_LOCATOR 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 sqlaAssocLocator, according to the stack traceback).

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Apr 24 '07 #9
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
Apr 24 '07 #10
Hello Mr Nelken,

My name's Andrzej Dobrucki
I believe we met about 10 years ago in Kielce, Poland
I was wondering how you're doing
You can reach me at my g-mail given above

Have a great day
A.D.

Apr 25 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Krice | last post: by
8 posts views Thread by Jean-Marc Blaise | last post: by
8 posts views Thread by Lana Zapornikova | last post: by
2 posts views Thread by Mike | last post: by
reply views Thread by BD Jones | last post: by
2 posts views Thread by Joe Salmeri | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.