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

db2advis with stored procedures

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

May 1 '06 #1
Share this Question
Share on Google+
1 Reply


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

May 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.