db2advis with stored procedures

[mandible]

Hello I was wondering if I"m doing something wrong with using db2advis
I have 2 stored procedures are are massive, I created an input file to
call both of these stored procedures and I dont' seem to get any index
help, but if I put the main body of these stored procs in a view and
do a select * from that view I get a tonne of index help.

this is my input file
I have run it with the views uncommented and get great results but I
really would prefer to be able to
run db2advis against the stored procedures. I'm running v8.2 fixpak 4
===============
input.sql
call amp.getproc1(5138,0);
--select * from amp.viewproc1;
call amp.getproc2(4109);
--select * from amp.viewproc2;
===============

this is the commands I run
db2 connect to mydatabase
db2 reorgchk
cd /home/db2inst1/sqllib/misc
db2 -tvf EXPLAIN.DDL
cd /home/db2inst1
db2advis -d mydatabasre -n mydatabase -m IC -i input.sql -script
/home/db2inst1/output.txt

[Jean-Marc Blaise]

Hi,

You should try to use Serge's DB2 profiler:
http://www-128.ibm.com/developerwork...dm-0406rielau/.
It will be a great help, as you are in 8.2+; I hope you are using SQL/PL
:-).

I have eliminated some bottlenecks at a customer site, and it was really
easy thanks to this tool. Be carefull that the event monitor used behind my
have a great impact, but when you will get the timing, you'll be able to
focus on the right code.

Hope this helps,

Jean-Marc

"mandible" <el*********@gmail.com> a écrit dans le message de
news:11**********************@j33g2000cwa.googlegr oups.com...

Hello I was wondering if I"m doing something wrong with using db2advis
I have 2 stored procedures are are massive, I created an input file to
call both of these stored procedures and I dont' seem to get any index
help, but if I put the main body of these stored procs in a view and
do a select * from that view I get a tonne of index help.

this is my input file
I have run it with the views uncommented and get great results but I
really would prefer to be able to
run db2advis against the stored procedures. I'm running v8.2 fixpak 4
===============
input.sql
call amp.getproc1(5138,0);
--select * from amp.viewproc1;
call amp.getproc2(4109);
--select * from amp.viewproc2;
===============

this is the commands I run
db2 connect to mydatabase
db2 reorgchk
cd /home/db2inst1/sqllib/misc
db2 -tvf EXPLAIN.DDL
cd /home/db2inst1
db2advis -d mydatabasre -n mydatabase -m IC -i input.sql -script
/home/db2inst1/output.txt