Slow stored procedure after upgrade to 8.2

fayez

Hi.
going from 8.1.2 to 8.1.11, make our stored procedures very slow, here
is an example:
select a, b from t1 a
join t2 b on a.bkr=b.bkr
and (a.act_id=b.act_id or (substr(a.act, 1, 2)=b.act
and (substr(a.act, 1, 1)<='z' and (substr(a.act, 1,
1))>='a') ))
join t3 c on b.bkr=c.bkr
join t4 r on c.bkr=r.bkr
join t5 e on a.fi=e.fi
where a.bkr=ipbkr_num

Apr 28 '06 #1

Serge Rielau

fayez wrote:

Hi.
going from 8.1.2 to 8.1.11, make our stored procedures very slow, here
is an example:
select a, b from t1 a
join t2 b on a.bkr=b.bkr
and (a.act_id=b.act_id or (substr(a.act, 1, 2)=b.act
and (substr(a.act, 1, 1)<='z' and (substr(a.act, 1,
1))>='a') ))
join t3 c on b.bkr=c.bkr
join t4 r on c.bkr=r.bkr
join t5 e on a.fi=e.fi
where a.bkr=ipbkr_num
.
.
this select to insert to temp_table never finish, but:
1- if I remove .. and (a.act_id=b.act_id or (substr(a.act, 1, 2)=b.act

and (substr(a.act, 1, 1)<='z' and (substr(a.act, 1,
1))>='a') ))
finish fast as before upgrade.
2- if I replace: where a.bkr=ipbkr_num to where a.bkr='AAA'
finish fast as before upgrade.

For starters let's try REOPT ONCE.
Check out the SET_ROUTINE_OPTS() procedure to the option and the
recreate the procedure.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 28 '06 #2

Alka

Serge Rielau wrote:

fayez wrote:

Hi.
going from 8.1.2 to 8.1.11, make our stored procedures very slow, here
is an example:
select a, b from t1 a
join t2 b on a.bkr=b.bkr
and (a.act_id=b.act_id or (substr(a.act, 1, 2)=b.act
and (substr(a.act, 1, 1)<='z' and (substr(a.act, 1,
1))>='a') ))
join t3 c on b.bkr=c.bkr
join t4 r on c.bkr=r.bkr
join t5 e on a.fi=e.fi
where a.bkr=ipbkr_num
.
.
this select to insert to temp_table never finish, but:
1- if I remove .. and (a.act_id=b.act_id or (substr(a.act, 1, 2)=b.act

and (substr(a.act, 1, 1)<='z' and (substr(a.act, 1,
1))>='a') ))
finish fast as before upgrade.
2- if I replace: where a.bkr=ipbkr_num to where a.bkr='AAA'
finish fast as before upgrade.

For starters let's try REOPT ONCE.
Check out the SET_ROUTINE_OPTS() procedure to the option and the
recreate the procedure.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 28 '06 #3

Alka

Hi,

I work with Fayez. We tried the Reopt-once, no affect. Still the same
problem.

Regards,
Alka

Apr 28 '06 #4

Kiran Nair

If you still have the 8.1 machine up ..you can try taking explain and
hence the access plan of the statement and compare with the access plan
in new installation

Regards,
Kiran Nair
Alka wrote:

Hi,

I work with Fayez. We tried the Reopt-once, no affect. Still the same
problem.

Regards,
Alka

Apr 28 '06 #5

fayez

I have some good news !

We took the runstats from production which has old db2 (8.1.2) using
db2look for all the tables in our database, import those runstats in
TEST box which has the new upgraded db2 8.2.
Thanks Serge for your time. and thanks Kiran for your reply..

Cheers,
Fayez

Apr 29 '06 #6

Serge Rielau

fayez wrote:

I have some good news !

We took the runstats from production which has old db2 (8.1.2) using
db2look for all the tables in our database, import those runstats in
TEST box which has the new upgraded db2 8.2.
Thanks Serge for your time. and thanks Kiran for your reply..

Just to clarify. Are you describing the mistake or the solution?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 29 '06 #7

Knut Stolze

fayez wrote:

I have some good news !

We took the runstats from production which has old db2 (8.1.2) using
db2look for all the tables in our database, import those runstats in
TEST box which has the new upgraded db2 8.2.
Thanks Serge for your time. and thanks Kiran for your reply..

If this is the "solution" (as Serge asked for a clarification), I'd
recommend that you get to the bottom of the problem instead of working on
the symptoms. The next update or runstats or whatever could lead again to
the performance issue and you'll have to start over.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Apr 29 '06 #8