The number one cause of DB2 performance problems today, as always, is Problem Exists Between Chair And Keyboard!
- Poorly coded SQL
Many performance problems can be traced back to inappropriately coded SQL Code it correctly from the beginning and tune what is already out there.
- Improper indexing
Optimize performance via indexing by workload, not by object.
- Bad program design
Coding DB2 SQL for Performance in your application programs from the outset can eliminate many future problems.
- Bachelor programming syndrome
Yes, the dreaded "Fear of Committing" can cause performance problems due to concurrency issues.
- Improperly defined buffer pools
Defining effective buffer pools for your DB2 workload is important. There are many things you can do to identify the proper settings and sizing of your DB2 buffer pools.
- Index / table space needs to be
Reorganization of indexes and table spaces can improve performance. Follow the Five R's to assure optimal DB2 application performance.
- Improperly designed database
Designing database structures correctly -- from the beginning -- is the way to go for efficiency and efficacy.
- Copied code syndromeYou know the drill...
Developer: "Help me... I copied this from another programand it works there... so why not here"...
DBA: OK, what do you think this code does?
Developer: "I don't know, that's why I copied it... Can't you just make it work?"
- RUNSTATS not up-to-date (or not
How can you expect for the DB2 Optimizer to do its thing on your SQL without statistics about your data and environment? Again, The Five R's!
And if a Top Ten list does not provide enough detail for you, then splurge for a copy of my book: DB2 Developer's Guide, 6th edition. Recently updated for DB2 10 for z/OS, the book delivers over 1600 pages full of DB2 tips, tricks, guidelines, and details... It rates 5 stars on Amazon!