Using Explain reports to monitor access paths

The OMEGAMON for Db2 Performance Expert Explain reports are built on the information that is supplied by the DB2 SQL Explain function and information taken from the system catalog tables. An Explain report provides information about the exact access path that is used by DB2 for any particular SQL statement and its effect on DB2 performance.

Because Db2 is a relational database system, masses of data are stored in the form of table columns with no predetermined row order. Data can also be spread across several tables, partitions, and Db2 instances. The arrangement of data, and the complexity of many queries means that the method (access plan) used, and the route taken to the data (access path) cannot always be determined at application development time, especially when dynamic SQL is used.

The access plan and access path used by DB2 are important factors in DB2 performance. However, for any particular query, the access plan and access path are influenced by many factors, such as whether indexes are used and, if so, how many, whether data is clustered, joins are used, the locking strategy used, and so on.