Table of contents

Monitoring Db2 Big SQL

The Db2® Big SQL service provides a dashboard for monitoring and managing the service instance. This dashboard is powered by IBM® Db2 Data Management Console.

Before you begin

Before you use the Monitor dashboard capabilities in Db2 Big SQL, ensure that the IBM Db2 Data Management Console service is installed (see Db2 Data Management Console). Any Cloud Pak for Data user who is navigating the Db2 Data Management Console must hold the necessary permissions to complete actions.

About this task

Db2 Data Management Console is a browser-based console that helps you monitor and manage the performance of your Db2 Big SQL service instance by using one or more of the key performance indicators that are known as metrics.

Procedure

  1. From the instances list, find the instance that you want to monitor. Click the options menu and select Open to get a summary of the Db2 Big SQL service health, based on the following metrics:
    Metric Description
    Availability Indicates whether the service is up or down, and the relevant alerts.
    Responsiveness Indicates whether the system is responding well to the incoming requests.
    Throughput Indicates how much workload the system is handling.
    Resource usage Indicates the resource consumption of the service in terms of CPU, memory, storage, and log space.
    Time spent Indicates the percentage of time that is taken for service calls.
    Figure 1. A summary of the Db2 Big SQL service health
    Screen capture of a page from the Db2 Data Management Console showing an overview of the Db2 Big SQL service health
  2. Open the menu and click Statement to get performance data about queries that are running in Db2 Big SQL, based on the following metrics:
    Menu option Description
    In-flight executions See a list of the SQL statements that are currently running or that ran recently on Db2 Big SQL.

    You can use these metrics to identify costly or long running statements. You can cancel individual statements or disconnect a user or application if the statement needs to be stopped.

    Individual executions Displays the details of running SQL statements as system activity.
    Package cache See information about statements that previously ran on Db2 Big SQL. For example, you can see the number of executions and the total amount of CPU used for these executions.

    You can use these metrics to identify statements that use a disproportionate amount of system resources.

    Stored procedures Displays the aggregated execution metrics for procedures, external procedures, compiled functions, external functions, compiled triggers, and anonymous blocks invoked since the service was activated in Db2 Data Management Console.
    Figure 2. A list of Db2 Big SQL in-flight executions
    Screen capture of a page from the Db2 Data Management Console showing information about Db2 Big SQL in-flight executions
  3. Open the menu and click Locking to get lock data about applications that are running in Db2 Big SQL, based on the following metrics:
    Menu option Description
    Blocking and waiting connections Displays lock wait times, numbers of locks held, lock modes, and numbers of waiting connections for running applications.
    Connection statistics Displays connection, resource consumption, performance, and lock data for running applications.
    Locked objects with waiting connectionsd Displays locked object and waiting connections data for running applications.
    Find locked objects Displays data about locked objects that are impacting running applications.
    Figure 3. A list of the connection statistics for the Db2 Big SQL service
    Screen capture of a page from the Db2 Data Management Console showing a list of the connection statistics for the Db2 Big SQL service
  4. Open the menu and click Applications to get an overview of the service usage, based on the following metrics:
    Option Description
    Top consumers Displays the top consumers of the system resources.
    Connections See the different users or applications that have open connections to the service. A user or application can open multiple connections.

    You can use these metrics to find the source of disruptive behavior.

    Units of Work (UOW) Displays data about UOW activities, locks, and wait times that are impacting running applications.
    Figure 4. A list of connections to the Db2 Big SQL service
    Screen capture of a page from the Db2 Data Management Console showing a list of connections to the Db2 Big SQL service
  5. Open the menu and click Throughput to get an overview of query performance, based on the following metrics:
    Option Description
    Connection summary Displays a summary of all connections established to the console.
    Partition summary Displays data about each data partition that is used by Db2 Big SQL.
    WLM workload summary Represents the accumulation of all metrics for requests that were submitted by connections, and were mapped to the identified workload objects. This metric is based on Workload Manager.
    WLM service class summary Represents the accumulation of all metrics for requests that have executed under the indicated service subclass.
    Operating system time spent Displays performance data pertaining to operating system activities that support Db2 Big SQL.
    Figure 5. A summary of the connections to the Db2 Big SQL service
    Screen capture of a page from the Db2 Data Management Console showing a summary of the connections to the Db2 Big SQL service
  6. Open the menu and click Storage to get an overview of table read performance and storage sizes, based on the following metrics:
    Option Description
    Table performance Displays performance data for each table.
    Storage Displays logical and physical sizes (in kilobytes) for each table.
    Figure 6. Performance data for tables in the Db2 Big SQL service
    Screen capture of a page from the Db2 Data Management Console showing performance data for tables in the Db2 Big SQL service
  7. Open the menu and click Settings to get information about the following profiles:
    Option Description
    Monitor profile Displays information about the profile that was created to monitor historical data for your Db2 Big SQL connection. For details, see Monitor profile.
    Event monitor profile Displays information about the profile that was created to monitor specific event types that occur on Db2 Big SQL. For details, see Event monitor profile.