Articles & Content

Archives

Monitoring SQL Performance Using the DB2 Statement Cache (G01)

Topic: 2008 EU

Subtopic: DB2 for z/OS



DATE: 2008-10-13 (11:15 - 12:15)
SPEAKERS: Mike Perry (BMC Software)

This session discusses a low cost method of detecting and diagnosing SQL performance problems. The V8 EXPLAIN STMTCACHE and the V9 EXPLAIN MONITORED STMTS statements form the basis of the technique. You will learn how to use the DSN_STATEMENT_CACHE_TABLE to find unexpected statistics values, to obtain the statement text and to execute an EXPLAIN PLAN statement. The session reveals the cost of collecting the statistics.

EXP. LEVEL: Intermediate

OBJECTIVES:

Describe the DB2 statement cache, including structure and contents. Includes discussion on DB2 9 changes

Show how cached performance metrics are activated and accessed for analysis

Identify techniques for detecting exception conditions for statements in the cache

Show how SQL statement text from the cache can be analyzed using EXPLAIN processes

Describe keeping a performance database to be used for long term analysis



This file is being provided by IDUG. We would encourage you to join IDUG to get full access to all of our files and resources. Joining IDUG is FREE and signing up is simple. Click here to join! or login!

Download File
Click to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.