OpenAdmin tool for Informix's Health Advisor plug-in

Analyzing the health of your IBM Informix database servers

This article is a deep dive into the Health Advisor plug-in for the OpenAdmin Tool for Informix®. As the graphical user interface for monitoring and administering the Informix database server, the OpenAdmin Tool for Informix (OAT) is an ideal vehicle for a health check system for your database server. With the introduction of the Health Advisor plug-in, you can use OAT to regularly analyze the health and performance of your Informix database server. This article covers how the Health Advisor plug-in works, how to use it, and how it can benefit an Informix DBA. It also provides a step-by-step guide to customizing the Health Advisor by adding your own health checks and alarms.

Share:

Erika Von Bargen (vonbarg@us.ibm.com), Team Lead, OpenAdmin Tool for Informix, IBM

Erika Von Bargen photoErika Von Bargen has been a part of the IBM Informix development organization since 2004. She is currently the team lead for the OpenAdmin Tool for Informix product and one of its primary software developers. She is based in the IBM Lenexa lab.



21 March 2013

Also available in Chinese

Introduction

The OpenAdmin Tool for Informix is a web-based console for monitoring and administering one or more IBM® Informix database servers. OAT greatly simplifies administration by allowing you to drill down on resource usage, view performance statistics, and remotely perform administrative actions, to name just a few. Furthermore, OAT's built-in Plug-in Manager lets you extend and even customize OAT functions to meet your business needs.


The Health Advisor plug-in

The Health Advisor is a plug-in that is included with the OpenAdmin Tool (version 2.74 or later). The Health Advisor analyzes the state of the Informix database server by running a series of 48 checks. The Health Advisor then generates a report that contains the results and recommendations. The Health Advisor report can be run manually on demand or configured to run on a specific schedule with the report sent to the DBA using email.

As an automated health check system, the Health Advisor plug-in can provide a tremendous benefit to the Informix DBA. By scheduling the Health Advisor to run regularly, the DBA can ensure that the database server is regularly monitored for potential problem areas. The Health Advisor's automated email report ensures that the DBA is alerted early, thereby allowing potential issues to be identified and fixed before they become actual problems that slow down performance or otherwise adversely impact the operation of the database server. Moreover, the Health Advisor report not only highlights problem areas, it also provides recommendations to ensure that the DBA has the information he or she needs to act on the issues identified. Lastly, the Health Advisor plug-in is customizable. You can add to the existing health checks by writing your own alarms to customize the Health Advisor to suit your business needs.

While the Health Advisor plug-in is not intended as a replacement for an in-depth analysis of a system, it is a very useful tool to supplement your existing practices and can help ensure that your Informix database server is running optimally.


How the Health Advisor works

Use the Health Center > Health Advisor link on the OAT menu to navigate to the Health Advisor plug-in.

The Health Advisor is implemented on the database server as a set of tables and stored procedures deployed in the sysadmin database. This infrastructure for the Health Advisor is created on the database server instance when you first access the Health Advisor page in OAT. All tables and stored procedures relating to the Health Advisor start with the prefix "hadv_".

When a Health Advisor health check runs, it runs using a stored procedure on the database server. OAT is the infrastructure for setting up and configuring the Health Advisor, as well as for viewing the report for on-demand runs. But the actual execution of all health checks takes place within the sysadmin database on the database server.

The Health Advisor requires Informix version 11.50.xC7 or later.


Health check alarms

The Health Advisor is made up of a series of 48 alarms that are checked each time the Health Advisor runs. For convenience, the alarms are split into five different categories - Configuration, ER, OS, Performance, and Storage – and can be filtered or sorted by category. In the report, if the alarm condition is met on the database server, each alarm shows up as either a red alarm or a yellow alarm to indicate the severity of the problem. You can enable or disable each alarm, depending on the needs of your system or environment.

In the spirit of customization, many alarms have thresholds that can be defined to control when the alarm is triggered. Often you will be able to set both a red alarm threshold and a yellow alarm threshold. For example, in the alarm that checks for low free space, you can configure the threshold (as a percentage of free space) that triggers a red alarm and the threshold that triggers a yellow alarm. You might choose to set 10% as the yellow alarm free space threshold and 5% as the red alarm free space threshold.

Some alarms even have exceptions that can be defined. Let's stay with the free space check as the example. For this alarm check, you might want to exclude certain dbspaces. Suppose you have one dbspace devoted to your logical logs and one devoted to your physical log. While these spaces are more than 90% used, you are not concerned because they will not grow in size. In such a scenario, you might want to specify exclusions to the free space check for the logical log and physical log dbspaces so that you are not getting "false" red or yellow alarms for those particular dbspaces.

Alarms can be managed from the Alarms tab on the OAT Health Advisor page, as shown in Figure 1 (see larger image).

Figure 1. Alarms page in OAT
Scrren shot of the default profile page in OAT.

Table 1 lists the alarms that come with the Health Advisor plug-in.

Table 1. Health Advisor alarms
CategoryAlarm
ConfigurationDBSPACETEMP
DUMPDIR
Dynamic Log Allocation (DYNAMIC_LOGS)
LTXHWM
Logical Log File Storage
LRU Min Dirty
LTAPEDEV
NOAGE
ONDBSPACEDOWN
Physical Log Storage
SINGLE_CPU_VP
TAPEDEV
ERATS Files
ER DDRBLOCK
ER Node Down
RIS Files
OSOS Free Memory
OS Online Processors
OS Virtual Processors
OS Open File Limit
OS Semaphore IDs
OS Semaphores
OS Shared-Memory IDs
OS SHMMAX
OS shmNumSegs
PerformanceCKPT Triggered by LLOG
CKPT Triggered By PLOG
Critical Section
Disk Flush
Longest Wait
Buffer Reads per Table
Buffer Writes per Table
Sequential Scans per Table
Buffer Reads Cached
Buffer Writes Cached
Disk Sorts
Foreground Writes
Rollbacks
LLOG Pages per I/O
Lock Waits
PLOG Pages per I/O
Sequential Scans
StorageChunk I/O Operations
Chunk I/O Reads
Chunk I/O Writes
Dbspace Free Space
Offline Chunks
Row Size

Profiles

The Health Advisor plug-in has a concept called profiles. What is the purpose of profiles when monitoring the health of your database server? Profiles give you the flexibility and power to monitor what you want, when you want. You can create multiple profiles, each configured to monitor a specific item or set of items. Then you can schedule each profile to run its health checks at specific times and email you the results when there is a problem.

The idea of profiles is best conveyed through some examples. Suppose that you want to monitor your database server's dbspace usage once a day. You can create a dbspace profile, enable only the space-related alarms, and schedule it to run once a day. Suppose that you want to monitor read and write cache rates every 10 minutes because you want to be notified immediately if there is a caching issue. You can create a cache profile with only the cache alarms enabled and schedule it to run every 10 minutes. If you want to monitor database server checkpoints every hour, you can create a checkpoint profile and schedule that profile to run every hour.

As you can see, the ability to create profiles, each with a different configuration and schedule, gives you a lot of flexibility to have various aspects of your database server monitored at the frequency that meets the needs of your business.

Health Advisor profiles are managed on the Profile tab of OAT's Health Advisor plug-in page, as shown in Figure 2.

Figure 2. Profile page in OAT
Screen showing the Profile page in OAT, with a field to name the profile and add, load, and delete buttons.

When working in the Health Advisor in OAT, you always need to be working within a specific profile. The Load button lets you set or load a particular profile. All of the other tabs and buttons in OAT for configuring alarms and setting up the schedule and notification are then applied to the currently loaded profile. The title bar above the tabs shows the name of the current profile. In Figure 2, the name of the current profile is Default.


Reports and notification

There are two ways to run a Health Advisor report. You can run the report on demand through OAT by clicking the Run Health Advisor button at the top of the screen. This button is available from all tabs within the OAT Health Advisor pages. Clicking the Run Health Advisor button creates an HTML version of the report. As mentioned earlier, the health checks are executed using stored procedures on the database server itself. But you can use OAT to run the report on demand and view the results in HTML. Figure 3 shows part of the report as it is seen in OAT.

Figure 3. Health Advisor report run on demand in OAT
A sample Health Advisor Report Run On Demand in OAT

The second way of getting a Health Advisor report is to set up a schedule to have the health checks run at a specified time and frequency and have the report emailed to you. Figure 4 (see larger image) shows what the Health Advisor email report might look like.

Figure 4. Health Advisor report email
Sample Health Advisor Report Email

No matter how you run the Health Advisor, the report will contain only the red and yellow alarms that were generated by running all of the health check alarms that are enabled for that specific profile. If a certain alarm check passes, it does not show up in the report. The report only shows the potential problems (alarms) for your database server; it does not show the results of executing all of the alarm checks.

This notion of only reporting alarms makes a lot of sense if you think in terms of the profile example. If you have a cache profile running every 10 minutes that monitors read and write cache rates, you probably do not want an email report arriving in your inbox every 10 minutes that you have to review. You probably want an email only if there is a problem, and that is what the Health Advisor allows you to do.

This brings up the points of configuring a schedule and setting up notification preferences, which are handled on the Schedule and Notification tabs in OAT. On the Schedule tab, shown in Figure 5, you set the days on which you want the report to run and you specify start time, stop time, and frequency values. The scheduling of the Health Advisor task is done on the database server using Informix's Database Scheduler functions in sysadmin. If you want to use the Health Advisor from OAT only, meaning you do not want to set up a schedule and notification but only want to run the report on demand from OAT, you can disable the task on the Schedule tab. Otherwise, keep the schedule enabled and configure the rest of the fields based on your desired run times and frequency.

Figure 5. Schedule tab in OAT
Screen showing the Schedule tab in OAT

Use the Notification tab in OAT, shown in Figure 6, to configure your email notification preferences. Specify the To email address and From email address for the report. Use the When drop-down menu to choose if you want the report emailed to you every time the report runs (Always), emailed to you only if an alarm is generated (Any alarm), or emailed to you only if a red alarm is generated (Red alarm). Lastly, specify the email program configuration that will be used to send the email. Because the Health Advisor is run on the database server using the Database Scheduler, the email is sent from the host machine where the database server resides. Therefore, you must specify an email program configuration that is applicable to the host machine of your database server. For examples of how to configure the email program, refer to the context help for this page in OAT, which you can open by clicking the Help button in the upper-right corner of the page.

Figure 6. Notification tab in OAT
Notification tab in OAT showing the schedule for what alarms and frequency you want to use.

Customizing the Health Advisor by adding your own health check alarms

Hopefully, you will find the 48 alarms that come with the Health Advisor plug-in to be very useful. But every environment and every business is unique, so the ability to add your own custom alarms is extremely valuable. The following sections detail the steps for adding custom alarms and provide a few examples.

There are three steps involved in adding a new alarm:

  1. Create the alarm insert SQL file.
  2. Add group, description, and action messages to OAT.
  3. Create a new profile and run the report.

Step 1: Create the alarm insert SQL file

On the database server, the table sysadmin:hadv_gen_prof stores the alarms. In OAT, there is a sql directory within the Health Advisor plug-in (for example, htdocs/openadmin/plugin/ibm/hadv/sql within your Apache directory) that stores the SQL statements for installing the Health Advisor on the database server and for defining each alarm. There is a separate file for each alarm that defines the INSERT statement for each alarm. These alarm insert files follow the naming format ins_*.sql.

Adding a new alarm is as easy as adding a new ins_*.sql file to define your custom alarm. The easiest way to start is by copying an existing ins_*.sql file and making modifications as necessary for your custom alarm.

Table 2 explains the columns in the sysadmin:hadv_gen_prof table that you need to define for your alarm.

Table 2. Columns of the sysadmin:hadv_gen_prof table used for defining a Health Advisor alarm
PurposeColumn nameExplanation
Defining general information about the alarmprof_idProfile ID. There is a row in the sysadmin:hadv_gen_prof table for each alarm for each profile. The prof_id column specifies the profile ID that this alarm applies to. Set this column to -1 in your insert file. OAT will set this to the appropriate profile ID at insert time.
idUnique alarm ID. This is a serial column. Set this value to zero to have the ID auto-generated.
groupCategory name for the alarm. Existing groups are "Configuration", "ER", "OS", "Perf", and "Storage". You can create a new category by specifying a different group name here.
descShort description keyword for the alarm. This keyword will be used to pull the short description, long description, and action messages out of the OAT message files. (See step 2). You can use spaces, but for the purposes of creating the keyword that will be used by OAT, the spaces will be replaced by underscore characters.
nameNot used in OAT or the report. You can provide a descriptive name for your use or just set it to NULL.
ldescLong description. At insert time, the long description will be pulled out of the OAT message files. (See step 2). The message from the OAT message files will overwrite anything you specify in your insert file. A good practice is to set this to an empty string or the value "Updated from message files".
enable"Y" or "N" represents whether this alarm is enabled for the profile. Because the insert file represents the default values for the alarm when a new profile is created, specify "Y" to have the alarm enabled by default or "N" to have the alarm disabled by default.
temp_tab

If you want your alarm to store data about what triggered the alarm, you can specify a temp table name that will be used to hold detailed results in the sysadmin database.

The temp table name specified here will be the base temp table name. The prefix "t_red_" will be added to create the temp table name for red alarm results and the prefix "t_yel_" will be added to create the temp table name for the yellow alarm results.

When the report is generated, a SELECT * statement will be run against the red and yellow temp table as specified in this column and the result set will be appended to the report for the alarm. It is your responsibility as the creator the alarm to ensure that the data is inserted into the specified temp tables as part of the alarm execution.

An example of using the temp_tab column is the "Dbspace Free Space" alarm (ins_sto_chkspace_res.sql), which checks for dbspaces whose percentage of free space is below a threshold. That alarm inserts the dbspace name, size, number of free pages, and percent free values into a temp table in the sysadmin database using the base temp table name "sto_chkspace" so that the dbspaces with low free space appear in the report alongside the alarm.

If your alarm does not have an associated table, set this column to NULL. The base temp table name can have a maximum of 20 characters.

Defining the red alarm checkred_threshold

Red threshold column name. If your red alarm has a configurable threshold, set this column to the column name in this table that holds the configurable threshold. It should be red_lvalue, red_lvalue_param1, red_rvalue, red_rvalue_param1, or NULL.

Set to NULL if this alarm does not have a configurable threshold for the red alarm level.

If a column name is specified, the value of this column will be modifiable from OAT.

red_lvalue_typeType of the left side of the comparison for the red alarm. The type can be either "SQL" or "VALUE".
red_lvalueValue of the left side of the comparison for the red alarm.
red_lvalue_param1Parameter for the left side of the comparison for the red alarm. If no parameter is needed, set the value to an empty string.
red_opOperator for the red alarm comparison. Allowable values: <, >, <=, >=, =, !=, or <>
red_rvalue_typeType of the right side of the comparison for the red alarm. The type can be either "SQL" or "VALUE".
red_rvalueValue of the right side of the comparison for the red alarm.
red_rvalue_param1Parameter for the right side of the comparison for the red alarm. If no parameter is needed, set the value to an empty string.
red_actionAction message for the report for when the red alarm condition evaluates to true. At insert time, the red alarm action message will be pulled out of the OAT message files. (See step 2). The message from the OAT message files will overwrite anything you specify in your insert file. A good practice is to set this to an empty string or to the value "Updated from message files".
Defining the yellow alarm checkyel_threshold

Yellow threshold column name. If your yellow alarm has a configurable threshold, set this column to the column name in this table that holds the configurable threshold. It should be yel_lvalue, yel_lvalue_param1, yel_rvalue, yel_rvalue_param1, or NULL.

Set the value to NULL if this alarm does not have a configurable threshold for the yellow alarm level.

If a column name is specified, the value of this column will be modifiable from OAT.

yel_lvalue_typeType of the left side of the comparison for the yellow alarm. The type can be either "SQL" or "VALUE".
yel_lvalueValue of the left side of the comparison for the yellow alarm.
yel_lvalue_param1Parameter for the left side of the comparison for the yellow alarm. If no parameter is needed, set the value to an empty string.
yel_opOperator for the yellow alarm comparison. Allowable values: <, >, <=, >=, =, !=, or <>
yel_rvalue_typeType of the right side of the comparison for the yellow alarm. The type can be either "SQL" or "VALUE".
yel_rvalueValue of the right side of the comparison for the yellow alarm.
yel_rvalue_param1Parameter for the right side of the comparison for the yellow alarm. If no parameter is needed, set the value to an empty string.
yel_actionAction message for the report for when the yellow alarm condition evaluates to true. At insert time, the yellow alarm action message will be pulled out of the OAT message files. (See step 2). The message from the OAT message files will overwrite anything you specify in your insert file. A good practice is to set this to an empty string or the value "Updated from message files".
Reserved for future useo_param4Not currently used. Set to NULL.
o_param5Not currently used. Set to NULL.
o_param6Not currently used. Set to NULL.
o_param7Not currently used. Set to NULL.
Defining exceptionsexc_desc

Exception list description. Use this column to indicate if your alarm has an exception list. Set to NULL if your alarm does not have an exception list. Set to a non-null value to indicate if your alarm has an exception list.

At insert time, the actual message for the exception list will be pulled from the OAT message files and stored in this column.

Exceptions are stored in the hadv_exception_prof table. If you use an exception list for your alarm, your alarm's SQL must take into consideration anything specified as an exception.

If you read through this table and still are not clear how to define an alarm, do not worry. It will all be much clearer after you work through a couple of examples. But before you move on to those examples, let me describe the columns for defining an alarm check and how they all work together.

The alarm checks really boil down to a comparison operation. There is one comparison for the red alarm check and one for the yellow alarm check. For each comparison, you define a left side of the comparison, a right side of the comparison, and a comparison operation (less than, greater than, less than or equal to, greater than or equal to, equal to, or not equal to). When the Health Advisor runs, it evaluates the red alarm comparison first. If that evaluates to true, it raises a red alarm for the report. If the red alarm comparison evaluates to false, it will move on to evaluate the yellow alarm comparison, and raise a yellow alarm in the report if it evaluates to true. If both the red alarm and yellow alarm comparisons evaluate to false, that alarm will not show in the report, meaning no alarm is raised.

Each side of the comparison can be either a constant or an SQL statement (specified as "VALUE" or "SQL" in the various *_type columns). If you use an SQL statement, that SQL statement can be either a query or a stored procedure call. In either case, it should return a single value that can be compared against the other side of the comparison. For queries, you might run a Count(*) query with a condition and compare the count to a certain value. Or you can define a stored procedure that has an integer return value and compare the return value to a constant to determine if the alarm should be raised. You can compare the result of an SQL statement to a value or you can compare the results of two SQL statements to each other. Whatever form your SQL statements take, they will be executed from the sysadmin database.

Now that you know the general idea behind the alarm check comparison, let's walk through the columns for the red alarm comparison and show how they relate to each other. Three columns – red_lvalue_type, red_lvalue, and red_lvalue_param1 – represent the left side of the comparison. You define the type ("SQL" or "VALUE") in the red_lvalue_type column, the actual left side of the comparison (either your SQL statement or constant value) in the red_lvalue, and optionally specify a parameter in the red_lvalue_param1 column. The parameter in the red_lvalue_param1 only applies if you are using an SQL statement and you want to send a parameter or configurable threshold as part of the SQL statement itself. If you use the parameter, your SQL statement should have the string "%lparam1%" somewhere inside of it, for example, in the WHERE clause. The "%lparam1%" string will be dynamically replaced with the value of red_lvalue_param1 at execution time. This is useful because the parameter can then become your configurable threshold and you can change or configure this threshold from OAT without modifying the alarm stored in the table in the sysadmin database.

After the three columns that represent the left side of the comparison comes the red_op column where you specify the comparison operation (<, >, <=, >=, =, !=, or <>). There are three more columns—red_rvalue_type, red_rvalue, and red_rvalue_param1—that represent the right side of the comparison and function exactly like the columns defining the left side.

Use the red_threshold column to specify if you have a configurable threshold for the red alarm that should be exposed in the OAT interface. The value of the red_threshold column is the column name that represents the configurable threshold. The only column names that make sense as thresholds are red_lvalue, red_lvalue_param1, red_rvalue, or red_rvalue_param1. This means that the configurable threshold is either the left or right side of your comparison or the parameter to the SQL statement representing either the left or right side of your comparison.

The yellow alarm check is defined as a separate comparison from the red alarm check. However, it follows the same idea, and all of the same columns are repeated for the yellow alarm.

Step 2: Add description, action, and category messages to OAT

All of the messages that are shown in OAT are pulled from the message files. These include the category name, the alarm name or description shown on the Alarms tab, and the alarm action/recommendation shown in the report. The message files make OAT and the Health Advisor translatable into other languages. Consequently, you must add any string or message associated with your custom alarm to the OAT message files, even if you are only writing your alarm in English.

The OAT messages are stored in XML files in the lang/language_code directories within the Health Advisor plug-in. So, for example, you find the English messages in the htdocs/openadmin/plugin/ibm/hadv/lang/en_US directory within your OAT Apache installation. When you create a custom alarm, you create a new file called lang_custom.xml and place it in this directory. All messages relating to your custom alarm go in this file. If you add multiple custom alarms, all messages for all of your alarms go in the single lang_custom.xml file.

For each alarm, you need to add four messages to the message file: the short description, the long description, the red alarm action/recommendation, and the yellow alarm action/recommendation. For each message, your message tag must be based on the keyword defined in the desc column of your insert file for the alarm. You will add the following four lines to the lang_custom.xml file where keyword is the keyword that you defined for the alarm in the desc column of your insert file. The text within the CDATA section is the text displayed in the Health Advisor pages in OAT.

Listing 1. Code listing for lang_custom.xml file
<dsc_keyword><![CDATA[Short description of your alarm]]></dsc_keyword>
<msg_keyword><![CDATA[Long description of your alarm]]></msg_keyword>
<act_r_keyword><![CDATA[Action/recommendation message for the report if a red alarm is 
generated]]></act_r_keyword>
<act_y_keyword><![CDATA[Action/recommendation message for the report if a yellow alarm is 
generated]]>
</act_y_keyword>

You might also need to add a message for the category name for the alarm. If you used one of the five existing Health Advisor categories (Configuration, ER, OS, Perf, or Storage) in the group column of your insert file, you can skip this part. If you created a new category, add this line to your lang_custom.xml file, where group is the name specified in the group column of your insert file.

Listing 2. Code listing for adding a new category to the lang_custom.xml message file
<group><![CDATA[Category name]]></group>

English is the default language for OAT. You should always add your new messages to the English (en_US) directory. Optionally, if you want to support other languages, add a lang_custom.xml file with the same tags to any other language directories that you want to support.

Step 3: Create a new profile and run the report

To put your custom alarm into practice, you need to create a new Health Advisor profile from the Profile tab in OAT. Because the ins_*.sql files are read when a profile is first created, your new alarm does not show up in any existing profiles. Your new alarm only shows up when a new profile is created and all of the ins_*.sql files are processed to insert the alarms into the sysadmin:hadv_gen_prof table for the new profile.

After you create the new profile, load that profile and you should see your new alarm when you click the Alarms tab. If you specified configurable red or yellow alarm thresholds, you should be able to modify them through the OAT interface. When you run the report, either on demand through the OAT interface or using a schedule through the Database Scheduler, your new alarm will be evaluated and you should see your alarm in the report if the alarm condition is present.


Example 1: Number of Sessions alarm

Take a look at a few examples because that is really the easiest way to understand how this works. The first example creates a Number of Sessions alarm to check that the current number of connected sessions does not exceed a certain configurable threshold.

Step 1: Create the alarm insert SQL file

Suppose you want a red alarm if the number of currently connected sessions exceeds 5000 and a yellow alarm if the number of currently connected sessions exceeds 2000. Also, you want the numbers 5000 and 2000 for the red and yellow thresholds to be configurable.

The easiest way to create a new ins_*.sql file for this or any alarm is to copy from an existing one. So copy the existing ins_perf_prof_fgwrites.sql file to create one named ins_sessions_numsessions.sql to represent the new Number of Sessions alarm. Listing 3 shows what the insert file looks like for this alarm. The text highlighted in bold are the key columns to pay attention to. The rest are the same as the original file.

Listing 3. Code listing for the ins_sessions_numsessions.sql insert sql file for example 1
insert into hadv_gen_prof values (
-- Profile id (prof_id)
        -1,
-- Alarm id (id)
         0,
-- Category Type (group)
         "Sessions",
-- Short Description Keyword (desc)
         "Num Sessions",
-- Name (name)
         null,
-- Long Description (ldesc)
         "Updated from Message Files",
-- Enable (enable)
         "Y",
-- Temp Table Results (temp_tab)
         "",
----------------------------------------------------------------
-- Red threshold column name (red_threshold)
         "red_rvalue",
                
-- Red Alarm - Left check type (red_lvalue_type)
         "SQL",
-- Red Alarm - Left check value (red_lvalue)
         "select count(*) from sysmaster:syssessions",
-- Red Alarm - Left param1 (red_lvalue_param1)
         "",
                
-- Red Alarm – Operator (red_op)
         ">",
                
-- Red Alarm - Right check type (red_rvalue_type)
         "VALUE",
-- Red Alarm - Right check value (red_rvalue)
         "5000",
-- Red Alarm - Right param1 (red_rvalue_param1)
         "",
                
--Red Alarm – Action (red_action)
         "Updated from Message Files",
                
----------------------------------------------------------------
-- Yellow threshold column name (yel_threshold)
         "yel_rvalue",
                
-- Yellow Alarm - Left check type (yel_lvalue_type)
         "SQL",
-- Yellow Alarm - Left check value (yel_lvalue)
         "select count(*) from sysmaster:syssessions",
-- Yellow Alarm - Left param1 (yel_lvalue_param1)
         "",
                
-- Yellow Alarm – Operator (yel_op)
         ">",
                
-- Yellow Alarm - Right check type (yel_rvalue_type)
         "VALUE",
-- Yellow Alarm - Right check value (yel_rvalue)
         "2000",
-- Yellow Alarm - Right param1 (yel_rvalue_param1)
         "",
                
--Yellow Alarm – Action (yel_action)
         "Updated from Message Files",
                
----------------------------------------------------------------
-- Params 4 – 7 (Reserved for future use)
null,null,null,null,
-- Exclusion list description (exc_desc)
null
);

In the general alarm information section of this insert file, a new category is specified in the group column whose keyword is Sessions. The alarm desc is Num Sessions, so the keyword for this alarm for the OAT message files is Num_Sessions.

For the red alarm, the left side of the comparison is the SQL statement select count(*) from sysmaster:syssessions, the comparison operator is greater than, and the right side of the comparison is 5000. So the comparison is: (select count(*) from sysmaster:syssessions) > 5000. If this evaluates to true, you will get a red alarm in the report. You can see that the red alarm checks if the number of currently connected sessions is greater than 5000. You have specified red_rvalue as the column that represents the configurable threshold. So the number 5000 is just the default threshold for this alarm; it can be modified later through the OAT interface.

The yellow alarm check uses the same comparison. The only difference is that the default threshold for the yellow alarm is 2000.

Step 2: Add description, action, and category messages to OAT

To add the description, action, and category messages to OAT, create a lang_custom.xml file in the htdocs/openadmin/plugin/ibm/hadv/lang/en_US directory. Listing 4 shows the contents of that file.

Listing 4. Example 1 code listing for lang_custom.xml
<?xml version="1.0" encoding="UTF-8"?>
<lang module="lang_custom">
<Sessions><![CDATA[Sessions]]></Sessions> 
<dsc_Num_Sessions><![CDATA[Number of Sessions]]></dsc_Num_Sessions>
<msg_Num_Sessions><![CDATA[Red alarm: Checks that the number of sessions is below the 
red alarm threshold. Yellow alarm: Checks that the number of sessions is below the yellow 
alarm threshold.]]></msg_Num_Sessions> 
<act_r_Num_Sessions><![CDATA[There are a large number of sessions currently connected to 
the database server.  Consider removing stale connections.]]></act_r_Num_Sessions>
<act_y_Num_Sessions><![CDATA[There are a large number of sessions currently connected to 
the database server.  Consider removing stale connections.]]></act_y_Num_Sessions>
</lang>

Notice that the <Sessions> tag defines the text for the new category name. Furthermore, this file defines the short description, long description, and red and yellow action messages based on the Num_Sessions keyword that you defined in the desc column of the insert file.

Step 3: Create a new profile and run the report

Now go to the Profile tab, create a new profile, and the new alarm is deployed. Figure 7, Figure 8, and Figure 9 show this new custom alarm in OAT. Figure 7 shows the new Sessions category and the new Number of Sessions alarm.

Figure 7. Number of Sessions alarm in OAT
Number of Sessions alarm in OAT

Figure 8 shows the Modify Threshold page for this new alarm.

Figure 8. Modify Thresholds screens in OAT for the Number of Sessions alarm
Modify Thresholds screens in OAT for the Number of Sessions alarm

Figure 9 shows how the Number of Sessions alarms looks in the report if the yellow alarm condition is true.

Figure 9. Number of Sessions alarm in the report for the yellow alarm condition
Number of Sessions alarm in the report for the yellow alarm condition

Example 2: Table Extents alarm

In the second example, you'll create a Table Extents alarm that checks for tables whose number of extents exceeds a certain configurable threshold. This example shows how to use a stored procedure to execute the alarm check and how you can have results pertaining to the alarm shown in the report by using the temp table column.

Step 1: Create the alarm insert SQL file

For this alarm, create a new file called ins_perf_tabextents.sql to define the INSERT statement for the alarm.

This alarm will be based the following SQL query, where 100 in the having clause represents the configurable threshold.

Listing 5. Query for tables with a large number of extents
select trim(dbsname) as dbsname, 
trim(tabname) as tabname, 
sum(size) as size, 
count(*) as nextents 
from sysmaster:sysextents e 
group by 1, 2
having count(*) > 100
order by 4 desc, 1, 2

The Health Advisor comes with a predefined function that you can use to execute a query and store the results in a temp table for later use in the report. This function is called hadv_stage_data. This function takes three arguments: the first argument is not used, so just pass an empty string; the second argument is the query; and the third argument is the temp table name to store the results. The hadv_stage_data function returns the count of the number of rows that are returned by the query and that are inserted into the temp table.

For the custom alarm to check for tables with a large number of extents, you can use the hadv_stage_data function with the query shown in Listing 5. The full contents of the ins_perf_tabextents.sql file for this alarm are shown in Listing 6.

Listing 6. Code listing for the ins_perf_tabextents.sql insert sql file for example 2
insert into hadv_gen_prof values (
-- Profile id (prof_id)
        -1,
-- Alarm id (id)
         0,
-- Category Type (group)
         "Perf",
-- Short Description Keyword (desc)
         "Table Extents",
-- Name (name)
         null,
-- Long Description (ldesc)
         "Updated from Message Files",
-- Enable (enable)
         "Y",
-- Temp Table Results (temp_tab)
         "hadv_tab_extents",
----------------------------------------------------------------
-- Red threshold column name (red_threshold)
         "red_lvalue_param1",
                
-- Red Alarm - Left check type (red_lvalue_type)
         "SQL",
-- Red Alarm - Left check value (red_lvalue)
         "execute function hadv_stage_data('','select trim(dbsname) as dbsname, 
         trim(tabname) as tabname, sum(size) as size, count(*) as nextents 
         from sysmaster:sysextents e 
         group by 1, 2 having count(*) > %lparam1% 
         order by 4 desc, 1, 2', 't_red_hadv_tab_extents');",
-- Red Alarm - Left param1 (red_lvalue_param1)
         "100",
                
-- Red Alarm – Operator (red_op)
         ">",
                
-- Red Alarm - Right check type (red_rvalue_type)
         "VALUE",
-- Red Alarm - Right check value (red_rvalue)
         "0",
-- Red Alarm - Right param1 (red_rvalue_param1)
         "",
                
--Red Alarm – Action (red_action)
         "Updated from Message Files",
                
----------------------------------------------------------------
-- Yellow threshold column name (yel_threshold)
         "yel_lvalue_param1",
                
-- Yellow Alarm - Left check type (yel_lvalue_type)
         "SQL",
-- Yellow Alarm - Left check value (yel_lvalue)
         "execute function hadv_stage_data('','select trim(dbsname) as dbsname, 
         trim(tabname) as tabname, sum(size) as size, count(*) as nextents 
         from sysmaster:sysextents e 
         group by 1, 2 having count(*) > %lparam1% 
         order by 4 desc, 1, 2', 't_yel_hadv_tab_extents');",
-- Yellow Alarm - Left param1 (yel_lvalue_param1)
         "75",
                
-- Yellow Alarm – Operator (yel_op)
         ">",
                
-- Yellow Alarm - Right check type (yel_rvalue_type)
         "VALUE",
-- Yellow Alarm - Right check value (yel_rvalue)
         "0",
-- Yellow Alarm - Right param1 (yel_rvalue_param1)
         "",
                
--Yellow Alarm – Action (yel_action)
         "Updated from Message Files",
                
----------------------------------------------------------------
-- Params 4 – 7 (Reserved for future use)
null,null,null,null,
-- Exclusion list description (exc_desc)
null
);

Notice the use of the existing performance category Perf for the group column of the alarm. Because you named the alarm Table Extents the keyword associated with this alarm for the OAT message file is Table_Extents. Also, notice the defining of hadv_tab_extents as the base temp table name in the temp_tab column. This means that associated red and yellow temp table names are t_red_hadv_tab_extents and t_yel_hadv_tab_extents, respectively.

In the red alarm definition section, you see the execution of the hadv_stage_data function with the Table Extents query passed as the second parameter. The third parameter specifies that the temp name for the red alarm results is t_red_hadv_tab_extents. Because this function returns the count of the number of rows, you compare to see if the return value of the function is greater than (red_op column) the value zero (red_rvalue_type and red_rvalue columns). If the count returned by the hadv_stage_data function is greater than zero, then you know that some tables have a total number of extents that exceed the threshold, so the red alarm should be raised.

Notice that you have used a configurable threshold. You have specified that the red_threshold is the column named red_lvalue_param1. That column currently holds the default value of 100, but this will be configurable through OAT. Then the HAVING clause of the query passed to the function call uses %lparam1% for its comparison. Recall that at runtime, the current value of the red_lvalue_param1 column will be substituted for %lparam1%.

The yellow alarm definition parallels the red alarm definition, just with a lower threshold (75) and a different temp table name (t_yel_hadv_tab_extents).

Step 2: Add description, action, and category messages to OAT

For step 2, add the messages for the Table Extents alarm that will be displayed in OAT. Add these messages to the lang_custom.xml file that you created in example 1. Listing 7 shows the additional messages for the alarm checking for tables with a large number of extents.

Listing 7. Example 2 code listing for lang_custom.xml
<dsc_Table_Extents><![CDATA[Table Extents]]></dsc_Table_Extents>
<msg_Table_Extents><![CDATA[Red alarm: Checks that there are no tables whose number 
of extents exceeds the red alarm threshold. Yellow alarm: Checks that there are no tables 
whose number of extents exceeds the yellow alarm threshold.]]></msg_Table_Extents> 
<act_r_Table_Extents><![CDATA[Some tables exceed %lparam1% extents.  Consider 
defragmenting these tables to improve performance.]]></act_r_Table_Extents>
<act_y_Table_Extents><![CDATA[Some tables exceed %lparam1% extents.  Consider 
defragmenting these tables to improve performance.]]></act_y_Table_Extents>

Notice that the act_r_Table_Extents and act_y_Table_Extents action messages for the red and yellow alarms use the parameter %lparam1%. This value is replaced at runtime with the current value of the configurable threshold stored in red_lvalue_param1 and yel_lvalue_param1, as shown in Figure 11.

Step 3: Create a new profile and run the report

Finally, create a new profile to deploy the new Table Extents alarm and run the report to test it. Figure 10 and Figure 11 show the alarm as shown in its Modify Thresholds page in OAT and the alarm as shown in the report.

Figure 10. Modify Thresholds screens in OAT for the Table Extents alarm
Modify Thresholds screens in OAT for the Table Extents alarm
Figure 11. Table Extents alarm the report for the red alarm condition
Table Extents alarm the report for the red alarm condition

The code for both the Number of Sessions and Table Extents alarms are attached in the Download.


Conclusion

The Health Advisor plug-in provides Informix DBAs with a very valuable tool for monitoring the health of their Informix database server. The ability to create profiles and set up schedule and notification preferences allows you the flexibility to monitor what you want at the frequency you want and ensures that potential problem areas can be identified and addressed early. The ability to add new custom alarms that are tuned to your system and environment provides a powerful mechanism to adapt the Health Advisor plug-in to your business needs.

This article provided two examples of adding custom alarms to the Health Advisor, but the possibilities are endless. The 48 existing Health Advisor alarm checks provide you 48 additional examples to draw from. Because OAT is an open source tool, the source code and INSERT sql files for all of the existing Health Advisor alarms are available to you so you can customize the Health Advisor into a health monitoring system that is intricately tuned to the needs of your business.


Download

DescriptionNameSize
Source code for custom alarm exampleshealthadvisor_examplealarms2KB

Resources

Learn

Get products and technologies

  • Download the Informix Client SDK to get started today. OAT is included in the Linux® x86 (32-bit), Linux x86-64 (64-bit), Windows™ x86 (32-bit only), and Mac OS X x64 (64-bit only) Informix Client SDK downloads.

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=861854
ArticleTitle=OpenAdmin tool for Informix's Health Advisor plug-in
publish-date=03212013