By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,611 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,611 IT Pros & Developers. It's quick & easy.

Slow stored procedure after upgrade to 8.2

P: n/a
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
Share this Question
Share on Google+
7 Replies


P: n/a
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

P: n/a

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

P: n/a
Hi,

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

Regards,
Alka

Apr 28 '06 #4

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.