HP OpenVMS Systems

There are several Rdb monitoring and alarming utilities and tools available today:

• RMU/SHOW STATISTICS Utility

  The RMU/SHOW STATISTICS utility, one of the most powerful and useful tools available to database administrators (DBAs), analyzes performance characteristics of a database. However, the ability to analyze performance problems is only one aspect of a performance analysis tool. The DBA also needs to detect problems in a timely manner, then analyze the problems and immediately perform corrective actions.

• PATROL Knowledge Module for Rdb

  PATROL monitors and manages the resources in the environment using information from special files known as Knowledge Modules (KM) that are loaded in the console. If PATROL detects a problem with a particular computer or application that it is monitoring, these modules provide information for PATROL to attempt to fix the problem. If the problem escalates or requires human intervention, PATROL displays a warning or alarm condition for every resource affected by the problem.

• Oracle Rdb Trace and Rdb Expert

  Oracle Trace is a layered product that gathers and reports event-based data from OpenVMS layered products and application programs that contain its service routine calls. Oracle Trace provides Oracle Expert for Rdb along with data for optimizing existing Rdb databases. Event-based data can be collected from products that contain Oracle Trace service routine calls. In addition, Oracle Trace routine calls can be added to other user-developed applications to collect datafrom them. This process of adding Oracle Trace service routine calls to an application is called instrumenting the application.

• Internally-Developed Solutions

  Internally-developed solutions consist of the RMU/SHOW STATISTICS Utility along with alarming and notification features based on rules customized by the database administrator.

This article is written for those who implement enterprise solutions that involve HP customer's Oracle Rdb and OpenVMS production environment configurations.

• Develop software scripts, as necessary, to facilitate the monitoring and alarming of important databases by focusing on specific areas previously identified by the customer. These include (but are not limited to) the following areas:
  • database status (i.e., up or down)
  • elapsed transaction processing time
  • transaction rate
  • file system fragmentation
  • record locking
  • record level fragmentation
  • critical transaction rate
  • lock rate
  • transaction duration
  • number of fetches
  • number of stores
  • number of erases
• Document all developed software and the installation and deployment processes that be performed in the customer production environment.

In some cases, the DBA is not always available to constantly monitor the utility output, especially for 24*7 production databases. Post-processing recorded data is certainly not timely enough to prevent or resolve potential real-time database downtime situations, particularly with real-world, mission-critical requirements. The DBA needs the ability to be notified automatically when time-critical or interesting events occur to the database.

Using the RMU/SHOW STATISTICS configuration file, you can persistently define special events that specify the action to be performed when something of interest occurs to the runtime database. An event is an identification of a particular statistic value on which the RMU/SHOW STATISTICS utility will perform some user-defined action. In other words, an event is signaled when a statistic's value exceeds a user-defined set of thresholds.

Using events, the DBA can be automatically notified by an RMU/SHOW STATISTICS utility server running on behalf of a particular database. You define an event by specifying a threshold against a specific statistic and by optionally specifying the attributes the event is to have.

Each entry in the configuration file uses the general format variable=value;. The equal-sign (=) separating the variable and value is required. Note also that each definition is terminated with a semicolon character (;).

User-defined events are specified using the EVENT_DESCRIPTION variable. Events themselves are not named; rather, they are defined on behalf of a specific statistic for a given threshold. The EVENT_DESCRIPTION variable's value is a free-format description of the user-specified event. The event definition consists of three required, position-dependent components and an optional component. The following example describes the general format:

Even though an event may be enabled, it may not be active. For an event to be active, you must also specify either a program to be invoked or one or more operator classes to be notified.

During runtime, events can be disabled only by the RMU/SHOW STATISTICS utility or by importing a new configuration file that explicitly disables an event.

Certain statistic fields have leading blanks. This white space is considered part of the statistic name and is necessary to identify a unique statistic. However, the use of leading blanks is often difficult to discern in the configuration file. Therefore, the underscore character (_) or dash character (-) can be used in place of spaces in statistic names that have leading spaces. For example, the statistic field name " file extend" can also be specified as "_ _ file_extend" or "- - file-extend". This method improves the readability of difficult statistic field names.

Most general events are defined using the summary statistics screens. However, it is sometimes necessary to define an event on a specific table or index, or even a particular partition of a table. The AREA attribute allows you to specify this type of drill-down event and indicate the name of a particular storage area. When this clause is specified, the statistic field selected must be from the IO Statistics (by file) or Locking Statistics (by file) screens. The identified statistic name can be also qualified with the LAREA attribute to specify the name of a particular logical area, such as a table, btree index, hash index, or blob. When this clause is specified, the statistic field selected must be from the Logical Area screens. Further, if the selected logical area is partitioned across multiple storage areas, the AREA clause can also be used to identify a specific partition against which to define the event.

The following table explains the semantics for specifying the AREA and LAREA clauses to qualify a statistic field name:

The AREA and LAREA clauses are attributes and must follow the Threshold Name component of the event definition.

Up to eight different thresholds can be specified for a particular statistic field, although each individual event name must be specified in its own EVENT_DESCRIPTION variable definition. The threshold name clauses are as follows:

• MAX_RATE− The maximum "current" occurrence-per-second rate collected. This threshold only increases as each event is signaled.
• MAX_CUR_TOTAL − The maximum "total" value collected since the database was opened. This threshold only increases as each event is signaled.
• MIN_CUR_RATE− The lowest rate currently being sustained. This threshold remains constant.
• MAX_CUR_RATE− The highest rate currently being sustained. This threshold remains constant.
• MIN_AVG_RATE− The lowest average rate. This threshold only decreases as each event is signaled.
• MAX_AVG_RATE− The highest average rate. This threshold only increases as each event is signaled.
• MIN_PER_TX− The lowest per-transaction rate. This threshold only decreases as each event is signaled.
• MAX_PER_TX− The highest per-transaction rate. This threshold only increases as each event is signaled.

• AREAstorage_area_name− Defines the name of a particular storage area. When this clause is specified, the statistic field selected must be from the "IO Statistics (by file)" or "Locking Statistics (by file)" screens, unless the LAREA clause is also specified. This clause is not ignored when disabling the event.
• LAREA logical_area_name− Defines the name of a particular logical area, such as a table, btree index, hash index, or blob. When this clause is specified, the statistic field selected must be from the "Logical Area" screens. This clause is not ignored when disabling the event.
• INITIAL value− Defines the initial value of the "current" event threshold. The default value is zero (0) for MAX_XXX thresholds and "very big number" for MIN_XXX thresholds. The default value guarantees that at least one event will be signaled, thereby initializing the new "current" threshold value.
• EVERY value− Defines the value by which the initial threshold will be incremented or decremented when an event is signaled. If this value is the default value zero (0) for any event except the MIN_CUR_RATE and MAX_CUR_RATE events, then the event will be signaled only once.
• LIMIT value− Defines the maximum number of times the event can be signaled. If the value is the default value zero (0), events can be signaled indefinitely providing that the EVERY clause is specified with a non-zero value.
• SKIP value− Defines the number of event notifications to ignore before performing an actual notification. This clause is extremely useful for the MIN_CUR_RATE and MAX_CUR_RATE events, as the thresholds for these events are not reset upon being signaled. The default value zero (0) ensures that all events are notified.
• NOTIFY oper_class_list− Defines the quoted comma-separated list of operators to be notified for all events defined on the specified statistic. Valid operator keywords are CENTRAL, DISKS, CLUSTER, SECURITY and OPER1 through OPER12.
• INVOKE program_name− Defines the user-supplied program to be invoked for all events defined on the specified statistic. On OpenVMS, the program name is specified as a DCL process global symbol known to the RMU/SHOW STATISTICS utility.

The continuation character is not limited to the EVENT_DESCRIPTION variable; it can be used for any configuration variable.

Comments can be embedded in continued lines if they start at the beginning of the next line. The following example demonstrates two event descriptions containing embedded comments. The comment in the second event description takes precedence over the line continuation character.

Once an event has been signaled, it will only be re-signaled if the EVERY attribute clause was specified with a non-zero value. The current threshold value, originally initialized to the INITIAL value, will be advanced for MAX_XXX thresholds and declined for MIN_XXX thresholds. The exceptions to this rule are the current rate thresholds MIN_CUR_RATE and MAX_CUR_RATE, which are never advanced nor declined. The MIN_XXX thresholds disable themselves once the INITIAL value reaches zero (0), while the MAX_XXX thresholds never disable themselves.

Once an event has been disabled, it can be re-enabled only by importing a new configuration file or by manually using the Statistics Event Information screen, Re-enable all disabled events configuration sub-menu option. Individual events cannot be re-enabled on line.

As the RMU/SHOW STATISTICS utility identifies a statistic field whose current value or average value is changing, it examines any defined event thresholds established for that statistic field. This manner of examination minimizes the impact of event analysis, since the analysis is performed as part of the normal statistics collection process.

When the utility determines that a specified event threshold has been exceeded, an event is signaled. The signaling of the event means that any specified programs will be invoked and any specified operators will be notified. The event notification occurs immediately.

If you defined a program that will be invoked when an event is signaled, the program will be invoked with eight parameters. Some of the parameters contain multiple words that must be quoted if the parameters are passed to other utilities.

The parameters passed to invoked programs are as follows:

• P1 − This parameter is the date and time the event occurred. This parameter contains embedded blanks.
• P2 − This parameter is the statistic field name. This parameter may contain embedded blanks.
• P3 − This parameter is the event name.
• P4 − This parameter is the current event numeric value, expressed to the nearest tenth.
• P5 − This parameter is the word above or below.
• P6 − This parameter is the current event threshold value.
• P7 − This parameter is the event occurrence count.
• P8 − This parameter is the optional physical area and/or logical area name for the statistic field.

The P8 parameter is either null (blank) or contains the name of the affected storage area and/or logical area. The following example contains a log file sample output where an event for a partitioned logical area was signaled. Note that when both the storage area and logical area names are specified, they are separated by a period (.).

Using events to notify the DBA when a process terminates prematurely is very easy to accomplish. The following steps describe how this can be achieved using the RMU/SHOW STATISTICS utility User-Defined Events:

1. Identify the operation. Because you are going to define a new event, specify the ENABLEoperation keyword.
2. Identify the statistic name to which the event will be assigned. Use the "process failures" statistic from the "Recovery Statistics" screen, which is located in the "AIJ Information" sub-menu. This statistic is available even if you are not using after-image journaling.
3. Identify the threshold name to use. Use the MAX_CUR_TOTALthreshold, since this represents the current number of processes that have failed.
4. Identify the event attributes to use. This is probably the hardest part of defining an event. You want to be alerted to anyprocess failure, so you must set theINITIAL attribute to zero (0). Since you want to be notified on each and every process failure, set theEVERY attribute to one (1) and theLIMITattribute to zero (0).
5. Define how you will be alerted about the event. Since you want to be sent mail, use theINVOKEclause. Invoking a program on OpenVMS requires that you define a "DCL process-global symbol" to identify the actual DCL script, as is demonstrated by the following example:
   
   

   
   

6. Write the program to be invoked. Since you want to be sent mail with a clear description of the event actually signaled, use the simple DCL script in the following example:

   
   

7. Combine all of this information into the configuration file entry. The following example contains the final event description as you would enter it in the configuration file:

   
   

8. Invoke the RMU/SHOW STATISTICS utility as a server, using the configuration file. Be sure to use the /CLUSTER command qualifier if you want to be notified of cluster-wide events. The following example demonstrates the command-line to perform this operation:

   
   

Because applications increasingly require 24*7 availability, the rate at which DBAs are expected to react to potential downtime increases accordingly. The RMU/SHOW STATISTICS utility User-Defined Events provides the means by which DBAs can be automatically alerted when such critical situations arise, therefore enabling timely corrective actions.

Drill-Down Event− An event defined on a specific storage area, logical area, or logical area partition statistic.

Event− The identification of a particular statistic value on which the RMU/SHOW STATISTICS utility is to perform some user-defined action.

Oracle Rdb− A high-end client/server relational database for Alpha AXP and VAX.

Statistic Name− The valid statistic field for which the event is to be enabled or disabled.

Summary Event− A general-purpose event defined on a "summary" statistic field.

Threshold Name− The columns in the numeric version of the statistics screens for the specified statistic to be enabled or disabled.

Oracle Trace provides Oracle Expert for Rdb along with data that it uses to optimize existing Rdb databases. Event-based data can be collected from products that contain Oracle Trace service routine calls. In addition, Oracle Trace routine calls can be added to other user-developed applications to collect data from them. The process of adding Oracle Trace service routine calls to an application is called instrumenting the application.

The Oracle Trace software operates with minimum performance impact on the system. It can run with both the development and production versions of your application to give you information about the behavior of your application.

Using Oracle Trace, the event data collected from applications can be used for different purposes, including the following:

• Tuning and performance improvements of applications
• Planning for hardware resources, (i.e., capacity planning, and so on)
• Tuning the performance of the databases
• Debugging applications
• Logging errors

Oracle Trace provides RdbExpert information that RdbExpert uses to produce more efficient database designs. Database administrators no longer have to guess about the database workload because Oracle Trace collects actual workload information.

Several forms of the Rdb_Mon utility currently exist. One form is a standalone configuration, comprising a number of DCL command procedures, where the tool is not integrated with any other enterprise management system. Another form integrates with an enterprise-wide management system. Currently, the latter from has been integrated with the Heroix Enterprise Management products RoboMon, RoboEDA, and so on. Rdb_Mon can be customized to be included and integrated under any other suite of enterprise management products.

» http://documents.bmc.com/supportu/documents/55/16/5516/5516.pdf - PATROL Knowledge

Module for Rdb User Guide

Kostas G. Gavrielidis works in HP Services Customer Support and has been with HP for more than 20 years. Currently, and for the last 10 years, he is involved with the MSE proactive consulting projects for our customer production Database Management systems, and works on the analysis and performance improvements for SAP R/3, Oracle, Rdb, Ingres, SYBASE, SQL Server on UNIX, OpenVMS, and Windows platforms.

For more information on this article and to make suggestions and comments for improvements, please email the author