Unanswered: update on performance tuning db2 problem
I finally figured out how to tune our DB2 slow running stored procedures. Turns out the new p680 RS6000 servers running our DB2 UDB 7.2 on AIX 5.2L 32 bit did not have a bunch of AIX parameters tuned correctly. In addition, we found that the stored procs were running a lot of sorts and using hash joins which affected performance. Basically tuning the AIX and DB2 sortheap, dbheap and bufferpool params helped quite a bit as well as setting the CPUSPEED and associated memory parameters. I basically received a quick rampup in serious tuning the database engine and O/S. Next step, to tune the slow running queries using visual explain we need additional indexes to fix the issues with the hash joins. Tuning is tricky because of so many variables with our application! More an art than science.