|
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 | |
Share:
|
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 | | |
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 | | |
Hi,
I work with Fayez. We tried the Reopt-once, no affect. Still the same
problem.
Regards,
Alka | | |
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 | | |
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 | | |
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 | | |
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 | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
5 posts
views
Thread by Shay |
last post: by
|
3 posts
views
Thread by PipHans |
last post: by
|
1 post
views
Thread by Aston |
last post: by
|
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
|
2 posts
views
Thread by kanda |
last post: by
| | | | | | | | | | | |