DB2 Performance Summary Part 2

Make sure that you are using the correct database design

• Use partitioning for robust tables that play central parts in the processing to help ease locking and encourage parallelism. Also leverage database partition 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 sized and a majority of read access profile.
• Do you have any simple tablespaces that should be changed to segmented tablespaces for better 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 performance. Having extra indexes over busy tables can be a huge 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 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 review:
• Analyze the types of access paths that are active in your environment.
• The number of invalid plans and packages
• Use of table compression for large tables with frequent Select statements run against them.

Come to the Midwest DB2 User Group meeting March 4th in Chicago to hear my latest presentation “Java DB2 Performance with Data Studio and pureQuery”.  Get the full details at www.mwdug.org.