DB2 Performance Summary Part 3

Make sure that the interface with CICS works well to improve performance:

  • Validate the settings between your DB2 CICS transaction configurations. Make sure the maximum workload from one TOR, AOR or DOR is not overwhelming another CICS processing partner.
  • If your system is heavily dependent on CICS transactions:
    • Make sure that you are reviewing the CICS Statistics DB2 Connections reports on a regular basis.
    • Update the RDO/RCT settings so that the DB2 workload is using mainly dedicated and reused threads.
    • Insure that your RDO/RCT settings use a minimum of pool threads.
    • Make sure that your workload never has to wait for a thread to execute work in your DB2 CICS environment.

Handle DB2 security correctly to improve DB2 performance:

  • Make sure that you aren’t duplicating security checking with both DB2 and another security product.
  • Use group ids for your DB2 authorizations as much as possible so that plan, package and CICS thread authorizations can be reused.

Review DB2 bufferpool activity to improve DB2 performance:

  • A DB2 bufferpool should be sized correctly for its activity. Data objects and index objects are in different bufferpools. Data objects with similar access characteristics are defined to the same bufferpools.
  • EDM pools maintain enough free space.
  • RID pool is adequately sized and RID pool overflows are not happening in your environment.
  • Is your Dynamic Statement Cache Pool set to the appropriate size for your system? Are your settings encouraging SQL caching?

Check the health of your database objects to improve DB2 performance:

  • Are there DB2 tablespaces and tables that need to be reorganized? Check for a large amount of data set extents and a large number in FAROFFPOS.
  • In order to use indexes optimally,
    • Check to insure that indexes leverage uniqueness
    • Make sure that clustering indexes make sense and that the table is clustered in the most popular application processing sequence order and maintains a CLUSTERRATIO of 95% or higher
    • Make sure that index columns are in optimum order for your most frequently executed application WHERE clauses.
    • Make sure that all defined indexes are being used
  • Do you have a reorganization schedule an appropriate freespace that keeps DB2 tables cleaned up with a minimum of outage?

If you do the intensive review suggested here, you’ll find that you will become the “go to” person for DB2 performance. In these economic times, saving a corporation money will make a positive impression within the company.

_______________________________________________________

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.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>