Db2 Application Performance Analysis

Open the Program CPU Use report and expand .SYSTEM. If the .Db2 pseudo-module shows significant activity, you can examine invoked modules or analyze CPU and wait attributable to SQL statements.

Examining Invoked Modules

To examine Db2 service modules invoked during measurement, expand .Db2 to see a detailed breakdown of activity for all the Db2 system service modules consuming CPU. Then open the Attribution of CPU Time report and continue expanding it to determine exactly which SQL statements are responsible for invoking those Db2 system service routines. You then may want to continue your analysis by focusing on CPU use of SQL statements.

Examining SQL CPU Use

To examine SQL-related CPU use, open the Activity by Query report:

Identify which Queries, Procedures, or Packages are consuming a large amount of CPU time. Expand a resource-consumptive item to view the distribution of CPU activity within a Query that invoked Db2 system services. See which SQL statement numbers are associated with high CPU and examine the Run Time Statistics and text of the statement. You may be able to discover inefficiencies in the SQL code. If you requested that Strobe create the SQL analysis reports, click the SQL statement analysis button to view them and learn more about how to improve your code.
After you isolate the SQL statements that are demanding the most resources, look at the Services invoked section to identify Db2 system services that are associated with a particular SQL statement.

Analyzing Db2 Wait

To see what is causing wait, open the Wait Time by Module report and expand .SYSTEM. Db2 Wait Time shows up under .NUCLEUS. When .NUCLEUS is expanded, you will probably see an MVS Module IEAVEPS1 (Post Service) at which point you can go to Attribution of Wait Time to see what in Db2 is causing the wait.

If wait incurred by .Db2 is significant, examine the Activity by Query report to identify which Queries, Procedures, or Packages are associated with a large amount of wait. Expand an item to view the distribution of wait within a Query that invoked Db2 system services. See which SQL statement numbers are associated with high wait and examine the Run Time Statistics and text of the statement. You may be able to discover inefficiencies in the SQL code. If you requested that Strobe create the SQL analysis reports, click the SQL statement analysis button to view them and learn more about how to improve your code.
After you isolate the SQL statements that are demanding the most resources, look at the Services invoked section to identify Db2 system services that are associated with a particular SQL statement.
If pseudo-modules other than .Db2 incur significant wait, see the tips for CICS and IMS.