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.
Related Blog Posts
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:
- Run setup.bat or setup.sh, which are in the ibm-datasrvrmgr directory.
- 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.
- Open your web browser and enter one of the following lines to log in to the IBM Data Server Manager web UI:
Figure 1 shows how to add a database for monitoring to collect performance data.
Figure 1. Add Database Connection page
Figure 2 shows monitored database information that is provided to configure a database for monitoring.
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:
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.
Figure 3. Parameters display options on tile
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.
Figure 4. Alerts for the monitor database
Figure 5 shows how to view the details about the alert.
Figure 5. View details about the alert
Figure 6 shows briefly what is wrong with the SQL performance.
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.
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.
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.
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.
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.
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.
Figure 12. Alert for exceeding the SQL threshold values
Figure 13 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.
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.
For more information, see the following resources:
Data Server Manager IBM Knowledge Center:
Server Authentication Reference:
IBM Data Server Manager community:
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.