468,321 Members | 1,761 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

db2advis with stored procedures

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
1 3567
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.

Similar topics

2 posts views Thread by Kent Lewandowski | last post: by
1 post views Thread by norab | last post: by
5 posts views Thread by Tim Marshall | last post: by
1 post views Thread by Anurag | last post: by
2 posts views Thread by hello_db2 | last post: by
reply views Thread by db2dbdba | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.