IBM Data Server Manager Overview and Performance Analysis

IBM Redbooks Analytics Support Web Doc

Abstract

IBM® Data Server Manager is a web-based, integrated database management tools platform that manages IBM DB2® for Linux, UNIX, Windows, and IBM z/OS® databases. With IBM Data Server Manager, you can monitor, analyze, tune, and administer DB2 databases.

This IBM Redbooks® Analytics Support web doc describes ways to do performance analysis that can be used to drill down into SQL performance to understand the underlying issues and take corrective measures to optimize the outcome.

This document provides an overview of Data Server Manager and includes information that helps Data Server Manager administrators to troubleshoot SQL performance issues.

This document applies to IBM Data Server Manager Version 2.1 and later.

Contents

Overview

IBM® Data Server Manager is a simple and intuitive database administrator (DBA) tool for monitoring, analyzing, tuning, and administrating IBM DB2® for Linux, UNIX, Windows databases. It is easy to install (it can be installed as a non-root user) and can be running in few minutes. A web browser serves as the client to provide users insights into the databases. You can use IBM Data Server Manager to monitor both DB2 for IBM z/OS® databases and IBM BigInsights® by using the IBM Big SQL technology. Along with the cloud databases, this function makes IBM Data Server Manager one of the premiere tools to help manage and monitor many different IBM platforms.

There are two editions that are available:

  • IBM Data Server Manager Base Edition offers limited database administration and basic performance monitoring and can be downloaded at no charge.
  • IBM Data Server Manager Enterprise Edition must be paid for and is license-driven, but has advance monitoring features, query tuning, and expert advice and recommendations.


IBM Data Server Manager installation

To install IBM Data Server Manager, complete the following steps:
  1. Run setup.bat or setup.sh, which are in the ibm-datasrvrmgr directory.
  2. Provide a user ID and password to administer the product. This user ID is independent of the operating system user ID and does not need to exist.
  3. Open your web browser and enter one of the following lines to log in to the IBM Data Server Manager web UI:
    After you have logged in successfully, you can add a database for monitoring.

    Figure 1 shows how to add a database for monitoring to collect performance data.

    This figure shows how to add a database connection.
    Figure 1. Add Database Connection page

    Figure 2 shows monitored database information that is provided to configure a database for monitoring.

    This figure shows the Edit Datbase Connection page.
    Figure 2. Edit Database Connection page

    If on the monitored database the Database Manager configuration parameter Database manager authentication is set to SERVER, then select Clear text password for the JDBC security field. For more information, see the Server Authentication Reference, found at:

    https://ibm.biz/BdrUK7

    Perform a test connection to make sure that the provided credentials are correct. If the test connection is successful, save it by clicking OK.

    The user sees a tile on the home page for each monitored database with three metrics by default. You can add more metrics as wanted.

    Figure 3 shows an option to choose from the drop-down menu, which is displayed on the home page tile.

    This figure shows the parameters display options on the active tile.
    Figure 3. Parameters display options on tile


    Performance analysis

    IBM Data Server Manager has smart alerts to keep you updated about what is occurring on the database. The alerts are counted in the red box, and if you click it, it displays all the alerts.

    Figure 4 shows the shortcut to see the alerts notification. You also can click the database tile and click the Notification tab to see all the alerts.

    This figure shows the alert notifications for a monitored database.
    Figure 4. Alerts for the monitor database

    Figure 5 shows how to view the details about the alert.

    This figure shows how to view details about an alert.
    Figure 5. View details about the alert

    Figure 6 shows briefly what is wrong with the SQL performance.

    This figure shows some brief information about what is wrong with the SQL.
    Figure 6. Brief information about what is wrong with the SQL

    Figure 7 shows the details about how much CPU, wait, and execution time the SQL took.

    This figure shows details about the CPU, Wait, and Execution time for the SQL
    Figure 7. Details about the CPU, wait, and execution time for the SQL

    Figure 8 shows the recommended action that Data Server Manager suggests to correct the performance.

    This figure  shows the recommended action that Data Server Manager suggests to correct the performance
    Figure 8. Suggested action

    Figure 9 shows that too many table scans took place and that this scan is probably the reason behind the bad SQL performance.

    This figure shows the access plan for the SQL.
    Figure 9. Access plan for the SQL

    You see that too many table scans are being done. Either indexes must be created or indexes that were already there are not anymore, which caused the SQL performance impact.

    With IBM Data Server Manager, you can enable the Track Changes function to see what changed since the last scan so that you can make a more informed decision. To enable this function, click Administrator -> Configuration -> Track Changes.

    Figure 10 shows the information about the database objects that were changed.

    This figure shows the IBM Data Server Manager Track Change feature being used to capture an object update.
    Figure 10. IBM Data Server Manager Track Change feature being used to capture an object update

    These dropped indexes indicate the cause for the many table scans and the reason for the impact on performance.


    Setting the threshold value for SQL performance

    In IBM Data Server Manager, you can set the threshold value for SQL so that if any of the SQL is running for a long time, the user is notified.

    Figure 11 shows how to set the threshold values in IBM Data Server Manager monitoring alerts for SQL.

    This figure shows the IBM Data Server Manager SQL alert setting.
    Figure 11. IBM Data Server Manager SQL alert setting

    Figure 12 shows the type of alert that IBM Data Server Manager generates when the SQL run time exceeds the threshold values.

    This figure shows the alert for exceeding the SQL threshold values
    Figure 12. Alert for exceeding the SQL threshold values

    Figure 13 shows what might be wrong with the SQL.

    This figure shows what might be wrong with the SQL.
    Figure 13. What might be wrong with the SQL

    Figure 14 shows the details of statement status, activity time, lock wait time, CPU used, client application name, application ID, who ran the SQL, and what were the set threshold values.

    This figure shows the details in the parameters values for the alert.
    Figure 14. Details in the parameters values for the alert

    Based on the IBM Data Server Manager alert and recommendation, you can either terminate the SQL or adjust the threshold value to make sure that the SQL running time reflects the expected value.

    Similarly, there might be other reasons for the bad performance of the SQL, such as the statistics not being up to date. You might need to run runstats, update the tables, and perform a reorg to correct or improve the performance.


    Licensing information starting with IBM Data Server Manager V2.1

    Starting with IBM Data Server Manager V2.1 the following licensing considerations apply:
    • If an IBM Data Server Manager Enterprise Edition license has been applied and the DB2 version is Version 10.5.0 or earlier, then all databases that are configured in IBM Data Server Manager have enterprise capability.
    • If the database is IBM DB2 Advanced Enterprise Server Edition, IBM DB2 Advanced Workgroup Server Edition, DB2 Developer Edition, or DB2 Workgroup or Enterprise Edition and has the Performance Management offering, then the configured databases have enterprise capabilities.
    • All other databases have access to only the IBM Data Server Manager Base Edition capabilities.


    New features in IBM Data Server Manager V2.1.1

    Here are some other features that are new as of IBM Data Server Manager V2.1.1:
    • You can use the enhanced SQL editor to save your favorite scripts so that they can be used later or given to others to be used as a template for writing other SQL scripts.
    • You can generate SELECT and INSERT statements directly from database objects pages.
    • There are new monitoring and alerts services for high availability disaster recovery (HADR) clusters in standard DB2 configurations, which help you identify the health of primary and secondary databases.


    Related information

    For more information, see the following resources:

    Data Server Manager IBM Knowledge Center:
    http://www.ibm.com/support/knowledgecenter/SS5Q8A/product_welcome.html

    Server Authentication Reference:
    https://ibm.biz/BdrUK7

    IBM Data Server Manager community:
    https://ibm.biz/dataservermanager

    Special Notices

    This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.

    Profile

    Publish Date
    04 October 2016


    Rating: Not yet rated


    Author(s)

    IBM Form Number
    TIPS1347