8.1 crash - simple code

[andreyp#Antispam]

We are test 8.1 for migration from 7.2. Without success.
A lots of sql procedures can't be compiled because db2 crash.

One of simple code for garantee crash

CREATE PROCEDURE TEST(IN PARAM_1 VARCHAR(512))
LANGUAGE SQL
BEGIN

declare res_01 cursor with hold with return to client for
with tt (vvalue, pparam, ccount) as (values('', PARAM_1, 0)
union all
select substr(pparam, 1, locate(',', pparam)-1) as
vvalue,
substr(pparam, locate (',', pparam)+1) as
pparam,
ccount+1 as ccount
from tt
where locate (',', pparam) > 0 and ccount<50
)
select * from tt for read only;
open res_01;
END
@

Our cfg - trial 8.1 fixpack 5 under redhat 7.3

Informational tokens are "DB2 v8.1.0.48", "s040212", "MI00069", and FixPak
"5".

Andy

P.S. And restore/upgrade 7.2 -> 8.1 DOESN'T WORK!!!

[Jean-Marc Blaise]

Hi folks,

It seems the following can be a good workaround:

with tt (vvalue, pparam, ccount) as (values('', coalesce(PARAM_1,''), 0)
Besides, if you cast all parameters in the values, among then:
smallint(0) and try this on CLP, you get:
SQL0347W The recursive common table expression "JMARC.TT" may contain an
infinite loop. SQLSTATE=01605
That's not true because there is a test ccount<50.

Could this be corrected too ?

Hope this helps,

Jean-Marc
<andreyp#Antispam@mapsitnA#it4profit.com> a écrit dans le message de
news:40**********************@news.nl.uu.net... We are test 8.1 for migration from 7.2. Without success.
A lots of sql procedures can't be compiled because db2 crash.

One of simple code for garantee crash

CREATE PROCEDURE TEST(IN PARAM_1 VARCHAR(512))
LANGUAGE SQL
BEGIN

declare res_01 cursor with hold with return to client for
with tt (vvalue, pparam, ccount) as (values('', PARAM_1, 0)
union all
select substr(pparam, 1, locate(',', pparam)-1) as
vvalue,
substr(pparam, locate (',', pparam)+1) as
pparam,
ccount+1 as ccount
from tt
where locate (',', pparam) > 0 and ccount<50
)
select * from tt for read only;
open res_01;
END
@

Our cfg - trial 8.1 fixpack 5 under redhat 7.3

Informational tokens are "DB2 v8.1.0.48", "s040212", "MI00069", and FixPak
"5".

Andy

P.S. And restore/upgrade 7.2 -> 8.1 DOESN'T WORK!!!