Product Manager IBM Database Tools
The article contributors are: Anson Kokkat and Marichu Scanlon.
Data Server Manager
Wouldn't it be nice to administer, monitor and tune your database environment all with one tool? Come take a look at the next generation tooling platform that IBM® rolled out in March 2015 to help optimize database performance, detect and solve performance problems, easily administer instances, databases and database objects, and manage data server configurations.
IBM® Data Server Manager is an integrated database management tools platform for DB2® for Linux, UNIX, and Windows databases. It was designed to be simple to set up, easy to use, and enterprise ready with the ability to manage hundreds of databases. It runs through a browser. You can set up the DSM server on your laptop, on the data server being monitored or a separate machine. There is nothing you have to install on the data server being managed.
With Data Server Manager, a DBA or a team of DBAs can monitor performance, track configuration changes, administer databases, and proactively or reactively optimize the database environment in one integrated end-to-end tool. Data Server Manager provides:
- An easy up and running experience with a simple setup, intuitive first experience, ability to auto-discover databases by instance, and predefined, customizable performance thresholds and alerts.
- A customizable enterprise dashboard that can scale to show metrics for hundreds of databases.
- Real-time and historical performance metrics with database at-a-glance metrics as well as detailed metrics for statements, locking, applications, workload, memory, I/O, and storage.
- Smart alert detection for scenarios like poorly performing queries, missing indexes, and buffer pool hit ratios.
- Configuration comparisons and change tracking for database objects and configuration settings.
- Administrative features for working with database objects and utilities and building and running SQL statements and commands.
- Advisors and access plan graphs that provide a wealth of recommendations and analysis for tuning poorly performing statements or workloads, including recommendations for indexes, statistics, and BLU optimization
- Analysis of storage access patterns and storage savings opportunities.
Problem determination and analysis
You can quickly find and address problems from the Data Server Manager Home page. The Home page provides an overview of all monitored databases.
Each tile on the Home page represents one database and shows some key metrics for that database.
You can drill down into a database to quickly identify and analyze database problems by reviewing real-time and historical performance metrics. The Database Overview page shows a time breakdown for the database and some key metrics. DBAs can then open additional pages to investigate detailed metrics in the following categories of potential performance issues: Statements, Locking, Applications, Workload, Memory, I/O, and Storage.
You might start with the Overview page, and then open the Statements page to identify a high-cost SQL statement. From that page, you can cancel a statement if needed. Next, specify a historical time frame, select the same statement and view details to see how the statement performed in the past.
The built-in alerting, notification, and action features enable management by exception so that you know when your database system is not performing as expected. You can see alert counts on the database tiles on the Home page and click to investigate the types of alerts and details. To find out more, click again to see the specific alert, relevant contexts and analysis, and suggested actions. You can also define custom alerts and specify a corresponding action.
You can also create alerts to notify you when access plans change.
Real-time and historical monitoring
Data Server Manager can be configured to collect both real-time and historical metrics for a database. You can use historical information to discover trends or investigate deviations from behavior that was observed in the past.
A time slider is provided along with easy-to-select time frames to choose from. You can pick a time frame and then use the time slider to move, widen, or narrow the time frame that they want to see.
Real-time Monitoring automatically collects selected metrics. There is nothing you have to set up or install on the data server. It enables calculation of mean and standard deviation for what is “normal” for each baselined metric and breaks that into 4 hour blocks. What is normal for a given day and time is defined by the baseline for the corresponding 4-hour block aggregated over the last five weeks. For example, 9:32 am, belongs to the Mon 8 am - 12 pm baseline calculated from the mean and standard deviation of the last five weeks. This provides a different normal baseline for each 4-hour weekly block.
Storage costs are a concern for any enterprise with a large or growing amount of data. To effectively manage storage consumption, enterprises must analyze how storage is accessed over time, reclaim trapped storage in tables, apply compression to tables, and drop seldom used tables.
With Data Server Manager, you can review storage accesses over time and identify how often instances, schemas, tables, indexes, and table spaces are accessed or modified. The following figure shows a heat map for viewing real-time storage and access status for database tables and schemas.
You can also view details for historical accesses and take suggested actions such as configuring adaptive or static compression to realize potential storage savings.
Statement and workload tuning
With Data Server Manager, you can format SQL statements so that they are easier to read, generate visual representations of access plans, and get recommendations for collecting statistics or improving indexes.
Access plan graphs show where statements are using the most time and show where tables are being scanned. Tuning advisors are also available to provide expert recommendations about statistics, indexes, and performance improvement. The following image shows an access plan graph in the background and a tuning results page in the foreground with a comparison of rows read, the top cost operations, and a link to four recommendations for collecting statistics.
You can use Data Server Manager for database management and maintenance tasks, including object, change, and authorization management, scripting, and job scheduling to automate changes to a database.
With the configuration tracking and comparison features of Data Server Manager, you can manage and monitor system configurations both reactively and proactively. You can track configuration changes for monitored databases, compare configurations between two databases, or clone configurations from one database to another.
- Tracking changes - Administrators can set the monitoring profile for any database to track configuration changes. DBAs can easily access the list of changes from the tool, filter the time frame for the changes, and drill down into specific database and database objects to see what changed. In addition, alerts can be set to trigger when configurations change.
- Comparing configurations - DBAs can compare the current configuration of a database or client to a previous configuration, or they can compare between different databases or clients. Data Server Manager can display differences between database, instance, and client configuration parameters.
- Cloning configurations - DBAs can use a Data Server Manager utility to clone the database configuration parameters of one database to one or more databases. The cloning feature is particularly useful when a configuration is considered the standard for best practices. The cloning feature can also be used to quickly reset the configurations of a database that was incorrectly changed.
In larger environments, fully understanding the range of client levels that are being deployed and managed can be a daunting challenge. After being deployed, many desktop clients are largely ignored, and so are upgraded only if upgrading is absolutely necessary. Therefore, DBAs must fully understand the many clients that they manage.
Data Server Manager can help you manage and control the client applications that connect to the enterprise databases. In addition to tracking and comparing server and client configurations, DBAs can enforce client properties, control clients and monitor end-to-end transaction response time.
You can turn on client data collection when needed to review and analyze client metrics. For each client IP, data can be tracked for the transaction response time components across the entire database application system, which includes client driver time, network time and data server time.
Client configuration tracking and enforcement
Just as Data Server Manager can track server configurations, it can also track client configurations. You can enforce best practices’ client configuration settings by first configuring one your application as your “best practices” application, and comparing this application’s connection, driver and data source properties to one or more applications. Any deviation from the “best practices” settings will trigger an alert. You can then quickly mitigate this undesirable setting by modifying the non-conforming application’s current configuration setting to the desired configuration setting at the next active connection. (Data Server Manager checks and enforces rules at transaction boundaries.)
Controlling clients with rules
With client rules, you can control the client, driver and data source properties of applications that connect to your database. (This requires installation of the Data Tools Runtime Client on each application source.)
You can define rules to perform important tasks from the tool, such as:
- Enforcing driver versions
- Throttling database connections by reducing the maximum number of simultaneous connections to a database.
- Redirecting a badly performing application to another server
- Isolate transactions in a pureScale environment to a “penalty box” member
In a client rule, you specify the conditions that will trigger the rule; and the desired action to take, for example redirect the application. Activating the rule tells Data Server Manager to start enforcing the rule.
Base and Enterprise Editions of Data Server Manager
The Base Edition of Data Server Manager includes everything you need to do basic administration, management and real time monitoring of your databases. You can also run SQL and see access plan graphs for individual statements. It is available for free download from IBM developerWorks and support is provided with your license entitlement to all DB2 for LUW editions. It is also included with the DB2 10.5 media and can be used with DB2 for LUW 9.7, 10.1 and 10.5.
The Enterprise edition of Data Server Manager allows you to create a historical repository to support historical and advanced monitoring, configuration management and query and workload tuning. Before Data Server Manager you had to use three separate products: Optim Performance Manager, Optim Query Workload Tuner and Optim Configuration Manager. The Data Server Manager Enterprise Edition is not available for sale directly. Instead it is part of the DB2 for LUW Advanced Workgroup and Enterprise Editions and included in the Performance Management Offering for DB2 for LUW.
You can use a single installation of Data Sever Manager to manage DB2 Express, Workgroup and Enterprise Editions (Data Server Manager Base) as well as DB2 Advanced Workgroup and Advanced Enterprise databases. Data Server Manager allows you to create monitoring profiles for groups of databases and it allows you to turn on and off the use of the historical repository database that is required for all the Enterprise features.
You can create one profile for your non-Advanced Edition databases that does not use the historical repository and another profile for your Advanced Edition databases that does.
You cannot use the Enterprise features without Repository Persistence turned on. You can select to turn Repository Persistence on or off for each monitoring profile.
What’s New with Data Server Manager 1.1.1
With the new release of Data Server Manager 1.1.1 available in Fix Central in June 2015, there are now new and improved capabilities to make it more complete. You can now create custom reports from your performance repository data. If you need to go beyond the standard reports already available with Data Server Manager you can now customize the historical data to meet your exact needs by running SQL against the performance repository database and exporting that data to other tools like Excel or Cognos. For DB2 pureScale, DPF and HADR, Data Server Manager now has more metrics so you can have a more complete view of the performance across your pureScale cluster. Completing the administration capabilities in Data Server Manager you can now create and alter views, aliases and triggers.
Many more new capabilities are listed in the What’s New in Version 1.1.1 Technote.
You can take the next steps with Data Server Manager by watching the series of videos on youTube, downloading the free Base Edition installation of Data Server Manager for Windows or Linux or exploring the articles available on developerWorks or IDUG.
- Data Server Manager Video Series
- Free Download: IBM Data Server Manager Base Edition
- IBM developerWorks article: Manage performance and administer DB2 databases with IBM Data Server Manager
- IDUG BLOG: Data Server Manager, Early User Experiences by Philip Nelson