Blogs

Integrated Tools That Empower Database Management

Administer, monitor, and optimize enterprise-scale databases using IBM Data Server Manager

IT departments today tend to spend the bulk of their resources using a variety of tools and disparate consoles for labor-intensive, routine database maintenance tasks in mixed-server environments. By effectively integrating database management tools, organizations can streamline these kinds of maintenance and management processes to help free resources for business-enhancing projects and strategizing ways to make these processes resource efficient. To address these needs, IBM has developed the IBM® Data Server Manager tool that offers integrated and extensible database management tooling easily deployed in enterprise or cloud-based environments. The tool can be accessed remotely from any supported browser and provides a range of features that can enhance database management for enterprise-scale IT departments.

Simple, scalable, smart database management

Data Server Manager offers a simple, integrated web console that IT professionals can use to administer, monitor, and optimize the performance of IBM DB2® for Linux, UNIX, and Windows databases. A quick, lightweight setup is available for basic auto-discovery, real-time administration, and monitoring for hundreds of databases. Optionally, Data Server Manager can be extended to support enterprise environments with highly advanced monitoring and optimization features. These features include smart customized alerts and notifications based on trends with solution guidance, configuration tracking and comparison, expert advice for performance tuning, storage-saving opportunities, and historical data.

Administration

Dashboards in Data Server Manager enable database administrators (DBAs) to explore and manage instance and database objects, perform basic administration tasks based on object types, write and execute SQL scripts with basic query tuning, and schedule scripts for automating maintenance tasks. Diagnostics logs can also be viewed. For example, DBAs can compare tables between two databases, one in production and one in development. They can also view the tables’ properties and their associated objects such as columns and indexes and then generate data definition language (DDL) for the objects that are new or changed in the development environment to deploy them into production. Data Server Manager configuration tracking and comparison features enable DBAs to track the changes that are made to server and client configurations. Using this information, they can easily determine the root cause of performance degradations or receive alerts when configurations deviate from standard baselines.

Monitoring

Seamless monitoring of database environments in Data Server Manager include high availability and disaster recovery (HADR) and IBM DB2 with BLU Acceleration and pureScale® deployments. Customized monitoring profiles and a built-in smart analytics engine in Data Server Manager analyze historical data patterns, detect potential problems, trigger alerts, and send notifications to inform DBAs. Interactive and customized dashboards with historical data provide easy viewing of database health summaries with outliers that highlight the anomalies. DBAs can compare current or recent activities to prior points in time and visualize trends. By using guided workflows to analyze and identify the source of problems, DBAs can, if applicable, resolve problems using integrated query and workload tuning for expert recommendations. They can also find out if a configuration change in the data environment may be the cause of an emerging performance problem. For example, DBAs can use Statements view to identify the top consuming queries for the last few hours and drill down to time and I/O history charts to see exactly when the spike occurred (see Figure 1). Integrated Tools That Empower Database Management – Figure 1 Figure 1. Time history showing execution metrics for a statement over a specified time period DBAs can also use Data Server Manager to manage the lifecycle of DB2 Locking Event Monitor. A Locking view is provided for diagnosing locking or application performance problems caused by pervasive locking contention because of deadlock, lock timeout, or lock wait. It shows detailed information such as lock participants—the blocking application and waiting applications—along with lock type, locked objects, lock and wait times, SQL statements, and graphical presentation of the locking chains (see Figure 2). DBAs can either terminate the blocking application for immediate relief, or drill down to view the details that can help when implementing a permanent solution. DBAs can also generate a locking report for a specific period to keep track of database-locking problems. Integrated Tools That Empower Database Management – Figure 2 Figure 2. Locking view with graphical representation of a lock chain Moreover, DBAs can also search for any locks that may be on a particular database table, or all tables in a schema. This search capability is useful for cases in which some applications suddenly hang or their performance degrades, and IT teams need to quickly resolve the problem. For example, suppose a developer uses a tool to update a table with auto-commit turned off and then goes home for the day. Then, all the other applications start to time out. When called in to resolve this problem, the IT staff can use Data Server Manager to make a quick analysis. Using the tool’s locking view, they can search for a specific object on which applications are waiting, identify the locking application, and terminate it if needed.

Optimization

The Data Server Manager tool provides capabilities for optimizing database performance, storage, and application insight and control. Performance. DBAs can use Data Server Manager to reactively or proactively optimize the performance of databases and applications using single query or workload tuning with expert advice for improving performance, comparing access plans, and index impact analysis. For example, after a single query is selected for tuning and the result comes back with an index recommendation, the DBA can virtually analyze the impact of the new index. The tuning analytics engine examines whether this new index will affect any other statements in the package cache, correlate these statements, and determine if there is a set of optimal indexes that would benefit these related statements as a workload. Storage. The storage optimization capability in Data Server Manager enables DBAs to view real-time and historical storage metrics, analyze storage usage and trends, and take suggested actions to implement storage savings such as applying compression or reclaiming trapped storage. Data Server Manager also displays a heat map with colored temperature metrics to show access frequencies of a table or schema (see Figure 3). The temperature colors range from red, representing high access, to blue, representing low access. This feature helps DBAs optimize query performance and storage costs by using fast, costly storage only for frequently accessed, critical—or hot—data and dynamically moving infrequently accessed—warm or cold—data to slower, cost-effective storage. Integrated Tools That Empower Database Management – Figure 3 Figure 3. Storage view showing table performance metrics and a heat map Application insight and control. DBAs can also take advantage of Data Server Manager capabilities for centrally managing and controlling the behavior of client applications that connect to the monitored databases. For example, while monitoring databases, DBAs can drill down to the insight of application stacks including application time, driver time, network time, and data server time. They can throttle applications by reducing the maximum number of simultaneous connections to a database, manage workload-balancing settings, or redirect some applications to other database servers to isolate the current database for performance-tuning analysis. To minimize Data Server Manager resource consumption, DBAs can configure client application data to be collected on demand at the database level.

Cloud-based database management

The emergence and adoption of cloud infrastructure in many organizations provides the means for agile application development, optimized application performance, and easy administration and monitoring of databases. To support some of these processes, part of the Data Server Manager capabilities are included in the agile, cloud-based IBM dashDB data warehousing solution that offers powerful analytics. Data Server Manager helps organizations proactively and reactively manage performance across the entire data environment. It provides the ability for enterprise IT teams to use a single, unified web-based user interface for performing a variety of daily tasks. Through integrated dashboards and reports, Data Server Manager is designed to empower organizations to efficiently administer and manage the overall health of critical applications and database environments, optimize performance and resource utilization, meet service-level agreements (SLAs), and help reduce total cost of ownership (TCO). Please share any thoughts or question in the comments.