467,154 Members | 949 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

Slow stored procedure after upgrade to 8.2

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
  • viewed: 2134
Share:
7 Replies
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

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
Hi,

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

Regards,
Alka

Apr 28 '06 #4
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
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
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by mamo74@gmail.com | last post: by
1 post views Thread by Evgeny Gopengauz | last post: by
16 posts views Thread by efiryago@gmail.com | last post: by
3 posts views Thread by Jennyfer J Barco | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.