View Application Performance Data

As a database administrator or application developer, you determine the root cause of poor application performance on your Db2 subsystems. The Mainframe Team Center - Database Management for Db2 for z/OS user interface lets you view and monitor curernt or historical collection data from one or more CA Detector data stores. When you view collection data through the CA Detector ISPF panels, you can view data from only one data store at a time.
dm20
As a database administrator or application developer, you determine the root cause of poor application performance on your Db2 subsystems. The
Mainframe Team Center - Database Management for Db2 for z/OS
user interface lets you view and monitor curernt or historical collection data from one or more CA Detector data stores. When you view collection data through the CA Detector ISPF panels, you can view data from only one data store at a time.
The collected data helps you perform the following tasks:
  • Identify heavily used components within applications.
  • Understand overall Db2 workload characteristics.
  • Pinpoint performance problems that are deeply embedded within your application activity.
Average or total metrics can be viewed according to the following performance categories:
  • SQL statements
    Shows the SQL calls that were issued during the collection interval.
  • Dynamic SQL statements
    Shows the dynamic SQL activity. Use this option to view the most frequently used dynamic SQL statements or most resource-intensive.
  • Packages
    Shows resource use from an application package point of view. You can use this information to identify which packages are using the most resources. You can also view the Db2 plans that have used the package and view the SQL calls originating from the package.
  • Plans
    Shows resource use from a plan name perspective. You can use this information to identify which plans are most frequently used and analyze resource use by plan name.
  • User ID
    Shows application activity by Db2 connection ID.
  • Correlation ID
    Shows application activity by correlation ID, such as batch job name or CICS transaction name.
  • Connection type
    Shows application activity by connection type, such as TSO or CICS.
  • Location
    Shows application activity by remote location or IP address.
  • End User ID, Transaction ID, and Workstation ID
    Shows application activity by end user ID, transaction ID, and workstation ID, which can be specified for distributed and RRSAF connections.
  • SQLCODES
    Shows the SQL return code that is set by Db2 after each SQL statement is executed. A review of this data lets you quickly determine which SQL codes are occurring most frequently. Select
    View Detail
    from the Action drop-down to display the SQL statements that generated the SQLCODE and the SQLCA area for the SQL call that issued the return code. This option lets you view the data columns with less scrolling. If needed, you can export the related SQL and SQLCA details to CSV format for further analysis.
For descriptions of these metrics, see the CA Detector online help.
Use the following procedure to view Db2 application performance data.
Follow these steps:
  1. Log in to the user interface.
    By default, the interface opens to the Performance functionality.
  2. Select a Db2 subsystem:
    1. Select an SSID or data sharing group name from the drop-down list or type the SSID or data sharing group name.
      Note:
      If there is only one valid environment for the SSID, it is auto-selected and the field is locked for entry or selection.
    2. Select an environment from the drop-down list or type the environment name.
    3. Set
      Use Group Name
      to ON to use the data sharing group name as the connected Db2 subsystem.
      The Db2 subsystem or data sharing group is selected.
  3. Select a performance category. The following categories are available:
    • CONNECTION TYPE
    • CORRELATION ID
    • DYNAMIC SQL STATEMENTS
    • END USER ID
    • LOCATION
    • PACKAGES
    • PLANS
    • SQL STATEMENTS
    • SQLCODES
    • TRANSACTION ID
    • USER ID
    • WORKSTATION ID
  4. Specify the interval range:
    1. Select
      Current Interval
      (real time) or
      Historical Interval
      .
    2. If Historical Interval is selected, specify the Interval Begin and Current Data/Time.
      Only collection data from completed intervals within the selected time frame is displayed. For example, if you select a time range of 10/12/19-8:42 to 10/13/19-8:41, data from 9:00 a.m. on October 12, 2019 to 8:00 am on October 13, 2019 is displayed.
    3. Select the
      Auto Collapse
      button to collapse the wizard bar after the next step.
  5. Select View Range to display the selected Db2 performance data.
    By default, all available records (up to 10,000) for the Db2 subsystem or data sharing group are returned. The records are sorted in descending order according to highest CPU. You can change the default and make other changes to configure the displayed data. Use the refresh icon to refresh the data as needed.
    Important!
    Depending on the number of records at your site, selecting ALL could make this feature become unresponsive.
  6. Review the displayed data to determine which metrics are impacting your application performance and require further action.
    To analyze the data further, select a row of data and then select an action from the
    Action
    drop-down to view related information.
    The data appears in a list or popup window. From a list, you can select a row of data and then select another action to drill down and view more information. For example, if you are viewing package information, you can select a package from the records list and a high-value in the CPUPCT column, and then select
    Action, View associated dynamic SQL statement
    . From the new data that is displayed, you can then select a statement from the list and select
    Action, View dynamic SQL statement text
    . Complete the selected action and close the window (if applicable).
    You can also use the links in the breadcrumb data and/or tabs to navigate between the performance categories that are displayed.
  7. Repeat this procedure as needed to review additional application performance data.