DB2 Performance Summary Part 2

Make sure that you are using the correct database design to improve DB2 performance:

• Use partitioning for robust tables that play central parts in the processing to help ease locking and encourage parallelism. Also leverage database partitioning for large tables (over 500,000 rows) which might benefit from spreading out the I/O to different partitions and different devices.
• Use database compression for those databases that have a large row sizes and a majority of read access profiles.
• Do you have any simple tablespaces that should be changed to segmented tablespaces for better DB2 performance? Are your segmented tablespaces properly sized?
• Do you have frequently updated tables with a large number of indexes? Double check to see if any of these indexes can be eliminated to improve DB2 performance. Having extra indexes over busy tables can be a huge DB2 performance overhead. Deleting two or three extra indexes can sometimes cut one-quarter or one-half of a second off your transaction response time. So make sure you only have the indexes that are being used defined on your tables.

Use application programming and SQL techniques correctly

• It is important to drive processing to the SQL engine as much as possible. Review applications in your company for:
  • Lack of SQL Joins
  • SQL Sub queries
  • Table expressions
  • CASE expressions
  • Limited fetch
• Review applications in your company for:
  • Excessive use of tablespace scans
  • Excessive use of list prefetch and index scans
• Use the new DB2 pureQuery for DB2 Java applications to improve DB2 performance, security and statically bind the application access path within your environment.
• Do you have too many locks against your tables? How many lock escalations happened today? The answer to these questions lies in looking at the order of your application processing and your programming methodologies so that concurrent transactions can occur easily.
• Data performance reviews need to be exhaustive in order to achieve all the possible savings possible. Data access reviews are an important part of these reviews. Also:
  • Analyze the types of access paths that are active in your environment.
  • Review the number of invalid plans and packages
  • Check the use of table compression for large tables with frequent Select statements run against them.

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.