Monitor DB2 for Linux, UNIX, and Windows databases with Data Studio Health Monitor

This tutorial introduces the IBM® Data Studio Health Monitor tool for IBM DB2® for Linux®, UNIX®, and Windows® databases. It walks you through the steps for monitoring the health of your databases, drilling down into the alert details, and changing the out-of-the-box default thresholds. It also describes the advanced health monitoring features that are available in the Optim™ Performance Manager 4.1.0.1 release, and the seamless integration in the Data Studio and Optim Database Administrator offerings.

Share:

Dimple Bhatia (dimple@us.ibm.com), Advisory Software Engineer, IBM Silicon Valley Lab

Photo of author Dimple BhatiaDimple Bhatia is an advisory software engineer at the IBM Silicon Valley Lab in San Jose, Calif. She is currently the lead on the Data Studio Web Console which is a web-based tool, including health monitoring and job management. She is also working on the web tooling infrastructure used for all web-based tools in the Optim umbrella. Before joining the InfoSphere Warehouse tooling team, she worked as the lead on the Federation Catalog and on migration in WebSphere Federation Server. She holds a master's degree in computer engineering from Syracuse University in New York.



Saphian S Kono (skono@us.ibm.com), QA Software Engineer, IBM China

Photo of Saphian KonoSaphian Kono joined IBM in 1998 as a developer and switched to become a QA engineer in 2005. He has worked as the QA lead for multiple projects including Data Studio Health Monitor. He has a B.S. Degree in Computer Science from the University of California, San Diego.



Pavitra Jalasutram (pjalasu@us.ibm.com), QA Software Engineer, IBM China

Photo of Pavitra JalasutramPavitra Jalasutram is a Software Engineer at IBM. She has been working on Quality Assurance testing for different Data Studio products including DSHM. She has a Master's degree in Software Engineering from San Jose State University.



Sriram Srinivasan (sriram@us.ibm.com), Senior Software Engineer, IBM China

Photo of Sriram SrinivasanSriram Srinivasan is the web tooling infrastructure architect for the Optim/Data Studio products, at the IBM Silicon Valley Lab in San Jose, California. Before this he was the architect for Infosphere Warehouse tooling, and worked on various Informix and Red Brick products. He has an M.S. degree in Computer Science from the University of Cincinnati.



16 December 2010

Also available in Chinese

First steps

Introduction

In October 2010, IBM extended its Data Studio offering with a new health monitor that allows you to monitor the health of your DB2 for Linux, UNIX, and Windows databases, and view alerts, application, utilities, and storage. Data Studio Health Monitor (DSHM) is a server process that you install separately. It includes a browser-based client, but you can seamlessly launch it from the Data Studio or Optim Database Administrator Eclipse client.

The health monitoring features are also available in Optim Performance Manager 4.1.0.1, and include more advanced functionality. See the Resources section for more information about Optim Performance Manager. This tutorial will take you on a tour of the free DSHM function.

DSHM can be downloaded from developerWorks at http://www.ibm.com/developerworks/downloads/im/data/.

Installation of Data Studio Health Monitor is quick and easy, and you do not have to install as a root user. No additional software needs to be installed on the machine where DSHM is installed, or on the machines where the monitored databases reside. See the Resources section for information on the installation and configuration process. You can also choose to skip any configuration steps marked "optional", until you are ready to take advantage of them.

Getting started

After you have installed Data Studio Health Monitor, you will find the scripts you need to start or stop the server in the installation /bin directory. The Config/ directory has some of the property files that are needed by the server, but you shouldn't have to manually update these files. The logs/ directory is where you will find the install logs, as well as logs created during the regular operation of the server.

Start the server. From Windows, you can start the server from the menu option under Programs->IBM Data Studio->Start Data Studio Health Monitor 2.2.1, or directly from Control Panel. From Linux/UNIX, you need to use the start.sh script from the DSHM installation /bin directory. You do not need to run the DSHM server as root. If your system administrator sets up an init.d script for DSHM to be automatically started on system startup, it is recommended that it still be started as the install user (from sudo), and not as root. If you decide to start this process as a user other than the one who installed it, just make sure you have permissions to read, write, and execute in the installation directory (including the Config and logs directories).

Connect to the DSHM server from a web browser with the Adobe FlashPlayer plug-in installed, using a URL with the following information: http://<hostwhereDSHMisinstalled>:<port>/datatools. The default port number for DSHM is 11080, but you can modify it at install time.

During the installation process, an administrator (usually 'admin', but you can change this) is created. The first time you log in, you will need to log in to the Web console as the administrator, using the password you provided during the installation. Figure 1 shows you the Welcome page after you log in.

Figure 1 - Data Studio Health Monitor web console on first login
The welcome page when first logging into Data Studio Health Monitor.

Identifying databases to monitor

From the Manage Database Connections page, as shown in Figure 2, you will identify the databases that you wish to monitor. You can enter the database information directly, or import a list of databases from comma-separated files. From the samples folder, use the DatabaseConnectionsImportCSV.txt file in the install directory as an example for imports.

Once a database is added, health monitoring is automatically enabled, and you are ready to monitor your databases!

Note that the credentials provided here are used only for periodic background monitoring of the database. Any interactive connections to the database require you to enter your credentials. For more information, see the Database Privileges section.

Figure 2 - The Manage Database Connections page
Description of the Manage Database Connections page.

Monitoring the health of databases

The DSHM server monitors the health of the databases by periodically querying the database and reporting health status through a set of pages accessible from the Health folder in the Task Manager menu. The Task Manager menu provides various tasks or options that you can use to either set up the system, or to view database health information. The tasks are grouped into folders that reflect the type of tasks they contain. For example, the Setup folder identifies tasks that you can use to alter specific system and database settings, whereas the Health folder contains menus that deal with database health monitoring.

The ability to identify problems and generate alerts is central to the DSHM health monitoring capability. A default configuration of thresholds and settings is used to identify problems of interest that you have identified in the databases. The Health alert configurations section will show you how to alter the default configurations.

Alerts are stored in the repository database, or in the file system if a Configuration Repository database has not been identified. From the Task Manager, you can look at the Health Summary and Alert List pages as a way to interactively view the alerts.

The Health Summary provides a grid of summary health information for each monitored database, as shown in Figure 3.

Figure 3 - The Health Summary grid
A figure that shows the Health Summary grid.

The alert icons in the grid cells identify a database status (Normal, Warning, or Critical) across categories such as data server, connections, storage, and recovery. A double dash -- in a cell indicates that no alerts were issued in the selected timeframe. For example, see the time direction selection in the upper left hand corner of Figure 3. Each icon represents a summation of one or more individual alerts that were encountered for the selected duration of that specific database. For example, if you had two Storage alerts for a database, one Critical and one Warning, then the alert summary icon would identify this as Critical. When you click on the cell, you can drill-down to the individual alerts themselves that detail the problems, including any appropriate actions you should take.

The selected timeframe, as shown in Figure 4, governs the alerts that are shown. The per-database, per-category summarizations include all of the alerts, as well as highlighting only those alerts that occurred in that time period. Also note that the Data Server Status category is special, since its summary represents the latest state of the database at the end of the time period.

Recent versus historical view

You can choose to view alerts for specific time periods. For example, if you look at the Recent view, you can see the most recent alerts that are produced. If you set your recent summary for 60 minutes, as shown in Figure 4, your Recent view will give you a summary of alerts that occurred during the last hour. The Refresh summary is set for 5 minutes, which means the status will be checked every 5 minutes. You should set the refresh time to be more frequent than the total summary period. Note that it is possible that no alerts are raised during the most recent period.

Figure 4 - Time duration selection and summary options
A figure that describes time duration selection and summary options in the Alert List page.

The historical setting allows you to view alert summaries by a specific time period. In this way you can review alerts that may have occurred while you were away, for example, overnight or over a weekend.

Alerts

While the Health Summary page provides an aggregated view, the Alert List page provides a detailed view of the individual alerts. By using the time selector, and sorting and applying filters, you can browse through the set of alerts that you are interested in.

Data Studio Health Monitor generates alerts for the following categories and situations:

  • Data Server Status - Creates an alert for different data server states including: Available, Unreachable, Quiesced, Quiesce Pending, and Rollforward.
  • Connections - An alert is generated when the number of connections to the database exceeds the threshold. This alert is disabled by default.
  • Storage - An alert is generated for the following situations:
    • Table Space utilization exceeds the threshold
    • Table Space container utilization exceeds the threshold. This alert is disabled by default.
    • Table Space container is inaccessible
    • Table Space is in Quiesce state
    • Table Space is Offline
  • Recovery - An alert is generated for the following situations:
    • Table Space is in Restore Pending or Rollforward Pending state
    • Table Space is in Backup Pending state
    • Table Space is in Drop Pending state
    • Primary HADR is disconnected

For example, look at the Data Server Status Alert category. When the MIR_DEV database is available, the Data Server status summary cell is green, as shown in Figure 5. Now let's say the database SALESDB has been quiesced. With the default configuration settings, a critical alert will be generated for the SALESDB database as shown in Figure 5.

Figure 5 - Alert summary for the Data Server Status category
A figure that describes the alert summary for the Data Server Status category.

You can view more information about the alert by clicking on the red icon under Data Server Status as shown in Figure 6.

Figure 6 - Data Server Status alerts and details for SALESDB
A figure that describes of the Data Server Status alerts and details for SALESDB.

Figure 6 also shows the list of individual alerts for the Data Server Status category. The Start Time and End Time columns show when the alerts originated, and if and when they ended. An alert without an end time indicates that the problem is still ongoing. For example, if the database is unquiesced, the next monitoring cycle for this database will identify this situation, and the critical alert will be closed. An end time is displayed, and the Health Summary grid shows the summary status as green. The Data Server Status is a special category that represents the status of the monitored database. It displays the status as green when the database is available and reachable.

As a second example, let's look at a Table Space Utilization alert. The SALESDB database has two warning alerts and one critical alert. The summary cell icon will identify this combined state as critical (see Figure 7). Click the Actions tab to see a link to the Current Table Spaces dashboard which lists the current table space status and storage information.

Figure 7 - Table Space Utilization alert
A figure that shows the Table Space Utilization alert.

Alert list view

The Alert list view provides a tabular listing of alerts instead of just the summary view showing either the recent or historical viewing options. You can also apply filter rules to keep track of the alerts you are interested in. To launch the Alert List view directly from the Task Manager, or from the shared health summary page, click the Open Full List button on an alert. From the Alert List view, the context sensitive launch from the shared health summary page will help you identify whether there were other alert types of interest that were raised for that database in that timeframe, as shown in Figure 8).

Figure 8 - Alert List
A figure that shows the Alert List.

Collaborating

You can add comments for an alert and share them with other users to help you keep track of your own investigations or responses to a problem, and collaborate better with other users. As shown in Figure 9, you can select an alert and click the Add Comment button on the Alert List page to add comments.

Figure 9 - Add a comment to an alert
A figure that shows how to add a comment to an alert.

Once a comment is added, you can select an alert from the grid, as shown in Figure 10, and then click the Comments tab to view it.

Figure 10 - Comments tab for an alert
A figure that shows the Comments tab for an alert.

You can select an alert and click the Send button to notify other users about the problem. The recipient can quickly access the specific alert by clicking the URL in the email. Prior to using the email notification facility, you need to have the DSHM Administrator provide the correct SMTP settings by clicking Task Manager > Setup > Services.


Use the Current health views

Data Studio Health Monitor allows you to view active connections and utilities, and to list table space details for following up on alert states.

Current application connections

The Current Applications Connections page, as shown in Figure 11, allows you to get a list of the applications that are currently connected to the selected database. Information about these connections, such as the Name, Idle time, and User authorization, is displayed. If a connection has been idle for a period of time, you can choose to disconnect, or force an application connection.

Note that in the Current Applications Connections page you will need to connect to the selected database with your own credentials to retrieve data and perform other actions, like forcing applications. If you do not have the right privileges on the database, then the operations will fail. The prompt screen provides you with the option to save your credentials for subsequent logons to make this easier.

Figure 11 - Current Application Connections
A figure that shows the current Application Connections.

Current table spaces

The Current Table Spaces page, as shown in Figure 12, displays live information about all the table spaces in the selected database. You can also view the table space container information for a selected table space.

Note that in the Current Table Spaces and Current Utilities pages, you will need to connect to the selected database with your own credentials to retrieve data and perform other actions, like forcing applications. If you do not have the right privileges on the database, then the operations will fail. The prompt screen provides you with the option to save your credentials for subsequent logons to make this easier.

Figure 12 - Current Table Spaces
A figure that shows the current Table Spaces.

Current utilities

The Current Utilities page, as shown in Figure 13, displays the status of any utilities currently running in the selected database, such as RUNSTATS or BACKUP.

Figure 13 - Current Utilities
A figure that shows the current Utilities.

Monitoring server configurations

The Data Studio Health Monitor has default out-of-the-box settings that let you get started immediately with very little effort. However, you may want to alter the default values, especially monitoring settings for individual databases, to get the best out of the features. This section describes these configuration settings and how to update them.

Configuration repository

It is highly recommended that you select a configuration repository database (DB2 LUW v9.5 or later) to more reliably store configuration and monitored data. If the repository database is not set up, the server will still function, but all information will be stored internally in the file system. Other tools, such as Optim Database Administrator or Optim Performance Manager, will not be able to access any data stored in the file system. To set up a configuration repository, select Configuration Repository from Task Manager > Setup.

In addition, you can control access to the server from the Console Security page when you select a configuration repository database.

Remember that when you select a database as the repository (or change to a different one), previously saved configuration and monitoring information will not automatically transfer over to the new database. While you can export the monitored database setting, and import it into another configuration repository, it may be easiest to select a repository database as early as possible.

Console security

When you install Data Studio Health Monitor, a single user credential is requested, and is used for login to the server. However, for day-to-day use, it is recommended that you set up Console Security so that you can grant appropriate privileges to users/groups/roles. You may delay this step until you are ready to publish this URL to more users.

There are two main authentication mechanisms:

  • Web container authentication. This is the install-time default security mechanism. You would have provided an administrator username and password during installation. Think of web container authentication, and this install-time user account, as just for maintenance or setup. Repository database authentication is the recommended mechanism for normal operation.
  • Repository database authentication. Any user who can connect to the repository database can log in to the web console. The user's credentials are authenticated directly against this database. To use the repository database authentication mechanism, you need to first set up a configuration repository. Typically the DBA of the repository database can then grant or revoke the Administrator, Operator, and Viewer privileges to other users. Only Administrators have special privileges, including the ability to change Console Security, change log levels, and so on.

Note that the Console security page is only concerned about access to the Data Studio Health Monitor server itself, but to work with individual databases, you are expected to have privileges on those individual databases. For more information, see the Database Privileges section.

Health alert configurations

By default, health monitoring is enabled automatically for all databases that are added to the Manage Database Connections page. The DSHM server checks the state of each database every 10 minutes, but you can disable monitoring entirely, or change the frequency of monitoring from the Health Alerts Configuration page. The currently selected warning and critical threshold values are also listed once you select a database.

Figure 14 - Health alerts configuration
A figure that describes the Health Alerts Configuration.

You can click Edit to modify the default threshold values, or disable alerts for an alert type. Editing alert configuration settings is restricted to users who have the Can Manage Alerts privilege for that database, as described in the Database Privileges section. For example, look at the editing of the Database Availability alert type configuration as shown in Figure 15.

Figure 15 - editing Database Availability alert type configuration
An Example of editing Database Availability alert type configuration.

Purge alerts interval

You control the retention period for alerts. The default is 3 days, as shown in Figure 16, but you can increase it to a maximum of 7 days. Only alerts that have ended will be deleted from the repository when they hit this expiry age. Any alerts that are still on-going will be retained, whatever its age.

Figure 16 - Purge alerts interval
A figure that shows the Purge Alerts Interval.

Database privileges

The Data Studio Health Monitor server typically monitors multiple databases from different organizational units in an enterprise, so it is important that the users and privileges boundaries defined in these databases are respected. For example, there are two databases called SALES and PAYROLL defined in the system. Just because the DBA for PAYROLL is able to log in to the Data Studio Health Monitor server, it does not mean she can modify alert settings for the SALES database.

A DBA can use Manage Privileges to grant or revoke Can Manage Alerts privileges to other users of that database. For example, a DBA for the SALESDB can grant Can Manage Alerts privilege to another DBA so that person can edit the alert configuration for the SALESDB database, as shown in Figure 17.

Figure 17 - Can Manage Alerts privilege
A figure showing how to grant and revoke the Can Manage Alerts privilege on the Manage Privileges page.

Taking DSHM for a spin

When you first work with the health monitoring features, it is quite possible that it would not show any problems with your databases. This section will focus on creating a variety of error scenarios so that you can get a better understanding of how this product works, and how you can take advantage of its features.

To keep things simple for this exercise, you will create these error scenarios in a controlled manner, and lower thresholds to more quickly cause situations to be flagged as alerts. This section includes scenarios for the following alert types:

  • Database Availability. This generates a data server status alert.
  • Connections. This generates a connections alert.
  • Table Space Quiesced. This generates a storage alert.
  • Table Space Backup Pending. This generates a recovery alert.

Adjust the monitoring frequency

Before starting with the error scenarios, navigate to the Health Alerts Configuration page from the Task Manager. When a database is selected, you will see a refresh interval option, as shown in Figure 18. The default refresh value is 10 minutes, which means that the monitoring of this database will happen every 10 minutes. For this exercise, you will change this value to 1 minute, so you will not have to wait as long to see the alerts being generated.

Figure 18 - Changing the monitoring refresh interval
A figure that describes the place where the refresh interval can be modified.

Adjust the page refresh rates

In the Health Summary page, there is also an option to change refresh intervals. Click the down arrow next to the Recent xx minutes label. A dialog appears as shown in Figure 4, that lets you change how often the Health Summary page will auto-refresh itself to display recently found alerts. The default is set to 5 minutes. For this tutorial, you can change it to 1 minute to minimize testing wait times.

Alternatively, you can also click the Refresh icon on the toolbar, as shown in Figure 3 to manually refresh the Health Summary, or Alert List grid.

Database availability

The Database Availability Alert checks the following status types for a database: Normal, Quiesce Pending, Quiesced, Rollforward, and Unreachable. By default, a warning alert is generated if the database is in Quiesce Pending or Rollforward state. A critical alert is generated if the database is in Quiesced or Unreachable state.

The following error scenario will put the database in Quiesced state, so that a critical alert will be generated.

  1. Open up a DB2 command window.
  2. Connect to the database with the following command, where <db> is the database name:
    • db2 connect to <db>
  3. Quiesce the database with the following command:
    • db2 quiesce db immediate

Navigate to the Health Summary page. Wait for one minute to see the Data Server Status change from Normal (green) status to Critical (red) status. The Critical Count should also now be raised by 1. Click the Critical Status icon, or the Critical Count Number, to get more information on the alert.

Updating the alert configuration

Even with the test database still in Quiesced state, you can still test out some additional features.

For example, perform the following steps to generate only a warning for a database in Quiesced state, instead of the currently defined critical alert.

  1. Navigate to the Health Alerts Configuration page from the Task Manager.
  2. Select the database that is in Quiesced state.
  3. Highlight the Database Availability row and click Edit.
  4. Ensure that the Enabled checkbox is selected.
  5. In the Critical section, clear the Quiesced checkbox.
  6. In the Warning section, select the Quiesced checkbox.
  7. Click OK.

Once the monitoring refresh occurs on the Health Summary page, you will see the Data Server Status change from Critical (Red) to Warning (Yellow). The Warning Count should now be raised by 1. Do not be alarmed if the Critical Count is still showing 1. The counts are a cumulative (summary) history of how many alerts have occurred within the past xx minutes. If you select the critical alert, you will also see that there is now an end time for it - meaning that the alert has closed. The start-end time duration is the time during which this problem was present.

Finally, you can do the following steps to unquiesce the database to get it back into normal status:

  1. Open up a DB2 command window.
  2. Connect to the database with the following command, where <db> is the database name:
    • db2 connect to <db>
  3. Unquiesce the database with the following command:
    • db2 unquiesce db

Once the monitoring refresh occurs on the Health Summary page, you should see the Data Server Status change from Warning (yellow) back to Normal (green). The previous warning alert should now also be ended. Even though the situation has returned to normal, the fact that the database was quiesced in the first place is an important event, which is reflected in the summary counts, as well as in the Alert List view for that time duration.

Connections

The Connections Alert is used to warn DBAs when there are too many connections to a database at the same time. By default, the Connections Alert is turned off, but preset to generate a warning alert if the number of connections detected is greater than or equal to 100, and a critical alert if greater than or equal to 150. Typically, DBAs need to decide what number constitutes a critical level, and what constitutes a warning level. What may be a perfectly reasonable limit for one database, may not be so for another database.

For this exercise, do the following steps to lower the thresholds for the alert, so you don't have to create over 100 connections to trigger it.

  1. Navigate to the Health Alerts Configuration page from the Task Manager.
  2. Select the database that you want to use for this scenario.
  3. Highlight the Connections row and click Edit.
  4. Select the Enabled checkbox (if it is not already selected).
  5. Lower the values for Warning threshold to 1, and Critical threshold to 5.
  6. Click OK.

Connect to this monitored database from the DB2 command line, or from Data Studio/Optim Database Administrator, and leave the connections active. Once the monitoring refresh occurs on the Health Summary page, you should see the Connections status change from No Alerts to either Critical (red) or Warning (yellow), depending on how many connections are currently active on that database.

Closing the connections to below the threshold value should close the alerts. Note that the critical or warning summary icon would still be present to indicate that there had been problems in that selected duration, but that these alerts now have an end time. This is unlike the Status summary icon with the Data Server Status alert category.

Remember to reset this alert parameter (or disable the alert) to whatever is appropriate for your database when you finish with this tutorial, because the thresholds chosen for this tutorial are unrealistically low.

Table Space Quiesced

The Table Space Quiesced Alert is used to warn you when a table space is in Quiesced state.

Do the following steps to put a table space into Quiesced state:

  1. Open up a DB2 command window.
  2. Connect to the database with the following command, where <db> is the database name:
    • db2 connect to <db>
  3. Quiesce the table space with the following command, where <tb> is the table name:
    • db2 quiesce tablespaces for table <tb> exclusive

Once the monitoring refresh occurs on the Health Summary page, you should see the Storage status change from No Alerts to Warning (yellow). To unquiesce the table space, issue the command: db2 quiesce tablespaces for table <tbname> reset.

Table Space Backup Pending

The Table Space Backup Pending Alert is used to warn you when a table space is in Backup Pending state. By default, a critical alert is generated when this is detected.

Do the following steps to put a table space into Backup Pending state:

  1. Open up a DB2 command window.
  2. Set up the database for archive logging. You can skip to step 3 if you have already done this.
    • db2 connect to <db> (where <db> is the database name)
    • db2 quiesce database immediate force connections
    • db2 unquiesce database
    • db2 connect reset
    • db2 update db cfg for <db> using logarchmeth1 "DISK:<path>" logprimary 13 logsecond 4 logfilsiz 1024 (where <db> is the database name and <path> is a directory to place the logs)
    • db2 backup database <db> to "<path>" with 2 buffers buffer 1024 parallelism 1 without prompting (where <db> is the database name, and <path> is a directory to place the backup)
  3. Connect to the database with the following command, where <db> is the database name:
    • db2 connect to <db>
  4. Load from a file into a table on your database with the following command, where <file> is the name of a file (you can use any file for this) and <tb> is the name of the table:
    • db2 load from <file> of del modified by codepage=850 insert into <tb>

Once the monitoring refresh occurs on the Health Summary page, you should see the Recovery status change from No alerts to Critical (red).


Accessing health monitoring features from Optim Database Administrator (ODA)

You can use Optim database administrator, or Data Studio eclipse, as a client to the Data Studio Health Monitor server. From the Current pages, you can view health monitoring information from the health summary pages as well as view live information about your databases. Not all of the features of Data Studio Health Monitor are accessible from Eclipse ODA, for example, when setting up Console security or when viewing logs. A Web browser is required to access all of the features. From ODA, you can connect to only one Data Studio Health Monitor server at one time. A Data Studio Health Monitor server is identified by its primary URL, and you can choose to connect to a different server by entering that URL of the server.

Prerequisites:

  • ODA must be version 2.2.3 or later.
  • The DSHM 2.2.1 server has to be installed, either on the same machine as ODA, or on a remote machine.
  • The server location and appropriate credentials must be available for ODA users.

Setup points from ODA

  1. Start ODA and select a workspace to open it.
  2. From the Task Launcher panel, specify the DSHM server details by clicking the Monitor tab as shown in Figure 19. This will help you through the available monitoring tasks.
    Figure 19 - Task Launcher in ODA
    A figure that shows the Task Launcher in ODA.
  3. Click Configure the Data Studio Health Monitor URL and preferences to open the preferences window as shown in Figure 20, and set up the connection to the DSHM server.
    Figure 20 - DSHM launch point from Preferences
    A figure that shows the DSHM launch point from Preferences.
    As with all Eclipse-based products, this preferences dialog is optional, and can also be accessed from the Window > Preferences menu.
  4. Expand Data Management and select Data Studio Health Monitor.
  5. When you access a health monitoring feature, if you have not previously entered the URL, your credentials, or selected the Save password checkbox, then you will be prompted to enter them to login into the server, as shown in Figure 21.
    Figure 21 - Connection dialog to Data Studio Health Monitor
    A figure that shows the Connection dialog to Data Studio Health Monitor.
  6. To identify the DSHM server, enter the URL (required). It is the same as the URL used with the Web browser, and resembles this format: http://<hostwhereDSHMisinstalled>:<port>/datatools. You should also fill in the appropriate user name and password fields.
  7. Optionally, you can select the Save Password checkbox to save the password for subsequent logins to the DSHM server. If the Save Password option is cleared, then whenever a Monitoring feature is launched, you must enter your credentials as shown in Figure 21.
  8. Optionally, you can select the Launch Data Studio Health Monitor in an external browser checkbox to launch the DSHM server in an external Web browser to take advantage of other features, such as context sensitive help, or to alter alert configurations.
    Figure 22 - DSHM Health Summary shown within ODA
    A figure that shows the DSHM Health Summary within ODA.

    Figure 22 shows how the Health Monitor features will open up inside the Monitor tab within the Eclipse workspace.

  9. Click OK.

Launching Data Studio Health Monitor in ODA

You can launch the various health monitoring tasks inside of ODA from either the Task Launcher panel, from a selected database in the Administration explorer, or from the Administration explorer view toolbar.

When launched from the Administration explorer, the selected database serves as the content when the monitoring task is launched. For the Application Connections, Table Space Usage, and Utilities tasks, such a launch causes the same database to be pre-selected in the monitor page.

  • From the Monitor tab of Task Launcher, if you select any one of the view health summary, view alerts list, view application connections or view table space storage usage monitoring tasks, then the appropriate page will open up in the DSHM Monitor tab.
  • From the Administration Explorer, right-click on a database to find the Monitor menu group. As shown in Figure 23, under the Monitor menu you find the following health monitoring options: Health summary, Alerts, Application Connections, Table spaces, and Utilities.
  • From the View menu option in Administration Explorer, the same Monitor menu is available as shown in Figure 24.
Figure 23 - Health monitor launch points from database context menu
A figure that shows the Health monitor launch points from database context menu.
Figure 24 - Health monitor launch points from the View menu.
A figure that shows the Health monitor launch points from the View menu.

Working with ODA identified databases in DSHM

The launch mechanism in the Eclipse workspace verifies if the selected database from Administration explorer is already present in the DSHM server. If it does not exist, it will automatically add it to the list of databases being managed and monitored by the server. However, you need to provide credentials with the right privileges before you can monitor in the Health Monitor server. Go to the Manage Database Connections page to provide these credentials and verify other parameters.


Advanced health monitoring in Optim Performance Manager (4.1.0.1)

The health monitoring features described in the previous sections are also available in Optim Performance Manager (OPM) 4.1.0.1. OPM includes more alert types and dashboards, and also has additional health monitoring features in the Current Applications Connections and Current Table Spaces pages, above and beyond what is available in DSHM. The Email and SNMP-based alert notification feature is also available only in OPM.

Additional features in the Current Applications Connections page

As shown in Figure 25, there is additional information in the application connections grid, including the number of failed statements, dynamic statements, and static statements.

Figure 25 - Current Applications Connections in OPM
A figure that shows the Current Applications Connections in OPM.

In OPM, when you select an application, you can also view the application's SQL details by clicking the View SQL Details button. Additional details such as the SQL statements, Statement Operation type, and Elapsed Time are also viewable, are shown in Figure 26.

Figure 26 - SQL details for the Application
A figure that shows the SQL details for the Application.

You can view the SQL statement text in the area below the grid, as shown in Figure 27. You can also view the complete SQL statement text by clicking the Show All Text button. Note that if the SQL statement text is too long (exceeds 4000 characters), then the SQL statement text will be launched in another Web browser window. Otherwise it will be launched in a pop-up view.

Figure 27 - SQL statement text
A figure that shows the SQL statement text.

Alternatively, you can also click the SQL tab, as shown in Figure 28 to view the running SQL statements for all connections in the selected database.

Figure 28 - Current running SQL for all connections
A figure that shows the current running SQL for all connections.

As shown in Figure 29, when you select a SQL statement, you can see the related details such as the Agent ID, Application name, and Authorization ID.

Figure 29 - SQL statement details
A figure that shows the SQL statement details.

Additional features in the Current Table Spaces page

In OPM, additional information such as the Usable Pages, Used Pages, Free Pages, and Total Size (KB) is displayed, as shown in Figure 30.

Figure 30 - Current Table Spaces page
A figure that shows the current Table Spaces page.

Email and SNMP alert notifications

In OPM, you can also choose to subscribe to alert notifications sent via email, or sent via SNMP to another tool, as shown in Figure 31. With this feature, you can wait until you receive notifications about problems, or interesting events regarding your database.

Figure 31 - Alert notifications page
A figure that shows the Alert notifications page.

You can choose to subscribe one or more email address for Warning or Critical alerts, or both. You can also choose to receive reminders if a problem persists for a lot longer than anticipated. To avoid notifications at inappropriate times, you can also set a blackout time.

The alert notification email includes specific details about the alert, and a URL to the alert itself. As part of the email notification, you can also include additional comments as part of the Notes field, as shown in Figure 32.

Figure 32 - Add a new alert notification
A figure that shows the add a new Alert notification.

Conclusion

In this tutorial, you've gotten a detailed look at the health monitoring features in Data Studio Health Monitor, and learned how to alter configurations to better suit your monitoring requirements for each database. You also learned how to try out some of the alerting features of DSHM, as well as how to invoke the monitoring features from within Optim Database Administrator, or Data Studio Eclipse environment.

Acknowledgements

Special thanks to Holly Hayes for her input on this tutorial.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=600720
ArticleTitle=Monitor DB2 for Linux, UNIX, and Windows databases with Data Studio Health Monitor
publish-date=12162010