Designing an alerts mechanism on the IBM InfoSphere DataStage and QualityStage Operations Console Database, Part 1: Alerts for common scenarios

Construct email alerts on the operations database using database triggers

The IBM® InfoSphere® DataStage® and QualityStage® Operations Console is a web-based application that is used to monitor the DataStage engine components in real time. It consists of a back-end database, DataStage Operations Database, where all the information about job runs and system resources is stored. This article describes an approach on how to construct email alerts on the operations database using database triggers. Also, you will learn how to use the data that is available in the operations database tables and how these tables get updated.

Share:

Raghavendra Dhayapule (raghavendra.dhayapule@in.ibm.com), Software Engineer, IBM

Photo of Raghavendar DhayapuleRaghavendra is a software engineer with the IBM InfoSphere Information Server product. He has over 10 years of product development experience, including firmware and operating system internals. Since mid-2008, he has been working with DataStage team and contributed to various product enhancements. Currently he is working on Operations Console development for IBM InfoSphere Information Server.



Len Greenwood (len.greenwood@uk.ibm.com), DataStage Core Architect, IBM

Len GreenwoodLen Greenwood was a member of the small development team that produced the first version of DataStage in 1996, prior to it being acquired from Ascential Software by IBM in 2005. It now forms a mainstay of the IBM InfoSphere Information Server suite. He has worked in the related areas of data and metadata integration for the past 15 years and is currently the main product architect for the core components of the DataStage and QualityStage development and production tools. He recently designed the database schema that underlies the Information Server Operations Console, used to monitor activity at the DataStage engine level.



28 February 2013

Introduction

The DataStage and QualityStage Operations Database (DSODB) is a back-end database for the IBM InfoSphere DataStage and QualityStage Operations Console web-based application. DSODB is designed as a relational database, and the schema for it is publicly documented. DSODB consists of several tables that store information for a job whenever a job is run.

When using DataStage, it is important that you know how the running jobs performed, and it is also good to be automatically alerted based on certain criteria. Because the job-related information gets stored in the DSODB tables as the jobs are running, you can set up database triggers on the tables to receive email alerts—after you understand how the tables get updated and how to use the data in these tables.

This article steps you through creating triggers for some common scenarios and sending an email alert to the specified user from within the trigger. The article also describes how the DSODB tables get updated and how to use the data that is available in these tables within the trigger.

More information on the DSODB schema, including the tables and columns can be found from the documents that are listed in Resources.

Knowledge prerequisites

To follow along with the article, you should have a basic understanding of the following areas:

  • DataStage and Operations Console
  • DB2 and database triggers
  • Database concepts

Software versions used

For the examples that are shown in this article, the following software configuration was used:

  • InfoSphere Datastage and Operations Console version 8.7
  • DB2 9.7 (bundled and installed with InfoSphere Information Server 8.7)
  • Windows™ 2008 operating system

The examples that are shown in this article are DB2 based, although you can use this article as a guide when using other database software such as Oracle or SQL Server.


Alert actions

The first part of this article series explains how to create email alerts using database triggers for some common and simple scenarios, such as:

  • Any job has failed.
  • A job is finished and has an elapsed time greater than N minutes.
  • A signal error if anyone is trying to delete a row from the HOST table.
  • A specified job is finished and has written less than N rows.

You can use the DB2® provided emailing facility and built-in stored procedure UTL_MAIL to send emails. To successfully send emails using this module, you must set a valid SMTP server address to the database configuration parameter SMTP_SERVER in the DB2 database that is used to hold the operational data.

To do this, issue the commands that are shown in Listing 1 at the DB2 command prompt.

Listing 1. Update SMTP_SERVER configuration parameter
C:\Users\dsadm>db2 connect to dsodbdb
    Database Connection Information
  Database server        = DB2/NT 9.7.4
  SQL authorization ID   = DSADM
  Local database alias   = DSODBDB
                
C:\Users\dsadm>db2 update db cfg using smtp_server <smtp server address>
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

Creating alerts

AlertFailedJobs

In this alert action, you should send email to the specified email ID if any of the running jobs failed. When a job is started, a row corresponding to this job is inserted into the JobRun table, and as the job continues to run this row is updated periodically. When the job finishes, the RunMajorStatus column is updated to 'FIN' (meaning the job is finished). If the job fails, there will be at least one fatal message, so the NumMessagesFatal column is updated to a number that is greater than 0. So for this alert action, you must create a trigger on update of DSODB.JobRun table's RunMajorStatus column and check for the conditions, RunMajorStatus = 'FIN' and NumMessagesFatal > 0.

Listing 2 shows the code to create the trigger for this alert action.

Listing 2. Trigger code to alert for failed jobs
CREATE TRIGGER DSODB.ALERTFAILEDJOBS AFTER  UPDATE OF RUNMAJORSTATUS ON DSODB.JOBRUN  
    REFERENCING  NEW AS NEWROW  FOR EACH ROW  MODE DB2SQL WHEN 
    ( NEWROW.NUMMESSAGESFATAL > 0 AND NEWROW.RUNMAJORSTATUS = 'FIN' )
BEGIN ATOMIC
                
DECLARE hostName VARCHAR(255);
DECLARE projectName VARCHAR(255);
DECLARE jobName VARCHAR(255);
DECLARE message VARCHAR(1500);
                
SET (hostName, projectName, jobName) = (SELECT DISTINCT Z.HOSTNAME, Y.PROJECTNAME, 
    Y.JOBNAME FROM DSODB.JOBRUN X, DSODB.JOBEXEC Y, DSODB.HOST Z WHERE 
    X.JOBID = Y.JOBID AND Y.HOSTID = Z.HOSTID AND Y.JOBID = NEWROW.JOBID);
                
SET message = 'Job failed, details: Host name = ' || hostName || '; Project name = '
    || projectName || '; Job name = ' || jobName || '; Invocation ID = ' 
    || NEWROW.INVOCATIONID || '; Run end time stamp = ' 
    || NEWROW.RUNENDTIMESTAMP;
                
CALL UTL_MAIL.SEND( <Sender Email>,  <Recipients Email>, NULL, NULL,
    'Job Failed Alert Triggered', message);
END

Within the trigger body, you get the host name, project name, and job name of the triggered row (NEWROW) by querying the tables DSODB.Host, DSODB.JobExec, and DSODB.JobRun. You also call the UTL_MAIL module with the required values to send email.

The jobType column in the DSODB.JobExec table indicates whether the job is a server job (SRV), a parallel job (PAR), or a sequence job (SEQ). So, if you want to send alerts for a particular job type (for example, parallel jobs), then you must get the jobType and compare this value to 'PAR'. After comparing, you must call the UTL_MAIL from within the trigger body, as shown in Listing 3.

Listing 3. Alert failed parallel jobs
DECLARE jobType CHARACTER(3);
                
SET (hostName, projectName, jobName, jobType) = (SELECT DISTINCT Z.HOSTNAME, 
    Y.PROJECTNAME, Y.JOBNAME, Y.JOBTYPE FROM DSODB.JOBRUN X, 
    DSODB.JOBEXEC Y, DSODB.HOST Z WHERE X.JOBID = Y.JOBID AND 
    Y.HOSTID = Z.HOSTID AND Y.JOBID = NEWROW.JOBID);
                
If ( jobType = 'PAR' ) THEN 
    SET message = 'Parallel Job Failed: Host Name = ' || hostName 
        || '; Project Name = ' || projectName || '; Job Name = ' 
        || jobName || '; Invocation id = ' || NEWROW.INVOCATIONID 
        || '; Run end time stamp = ' || NEWROW.RUNENDTIMESTAMP;
    CALL UTL_MAIL.SEND( <Sender Email>, <Recipients Email>, NULL, NULL, 
        'Job Failed Alert Triggered', message);
END IF;

ExceededElapsedTime

In this alert action, you can send details of a job that is finished but has taken more than N minutes (say 60 minutes) to complete the run. The elapsed time for a particular job run is stored in the ElapsedRunSecs column in the DSODB.JobRun table. In fact, the ElapsedRunSecs column is updated every 10 seconds until the job is complete. The job completion information is available in the RunMajorStatus column. Therefore, the trigger involves checking the finish status on the RunMajorStatus column and the value in the ElapsedRunSecs column. The trigger code for this alert action is shown in Listing 4.

Listing 4. Alert if job elapsed time is exceeded
CREATE TRIGGER DSODB.EXCEEDEDELAPSEDTIME AFTER  UPDATE OF RUNMAJORSTATUS 
    ON DSODB.JOBRUN  REFERENCING  NEW AS NEWROW  FOR EACH ROW  MODE DB2SQL 
    WHEN ( NEWROW.RUNMAJORSTATUS = 'FIN'  AND NEWROW.ELAPSEDRUNSECS > 60 * 60)
BEGIN ATOMIC
                
DECLARE hostName VARCHAR(255);
DECLARE projectName VARCHAR(255);
DECLARE jobName VARCHAR(255);
DECLARE message VARCHAR(1500);
                
SET (hostName, projectName, jobName) = (SELECT DISTINCT Z.HOSTNAME, Y.PROJECTNAME, 
    Y.JOBNAME FROM DSODB.JOBRUN X, DSODB.JOBEXEC Y, DSODB.HOST Z 
    WHERE X.JOBID = Y.JOBID AND Y.HOSTID = Z.HOSTID AND Y.JOBID = NEWROW.JOBID);
                
SET message = 'Job elapsed time exceeded, job details: Host Name = ' || hostName 
    || '; Project Name = ' || projectName || '; Job Name = ' || jobName 
    || '; Invocation id = ' || NEWROW.INVOCATIONID || '; Run end time stamp = ' 
    || NEWROW.RUNENDTIMESTAMP;
                
CALL UTL_MAIL.SEND( <Sender Email>,  <Recipients Email>, NULL, NULL, 
    'Job Elapsed Time Exceeded Alert Triggered', message);
END

In the previous trigger, you can collect the host name, project name, and job name of the triggered row and send email with the details.

SignalHostDelete

In this alert action, you are able to send an email and signal error if anyone is trying to delete a row from the HOST table. If anyone deletes a row from this table, because of the cascading delete constraints, all the data of the jobs that ran on this system and all the related resource usage records are deleted from the ODB tables at the same time. To avoid accidentally deleting a row from this table, you can set a trigger, and this trigger, if set, will not let anyone remove rows from the HOST Table. Listing 5 lists the code for this alert action.

Listing 5. Trigger code to alert HOST table row deletion
CREATE TRIGGER DSODB.SIGNALHOSTDELETE AFTER  DELETE  ON DSODB.HOST  REFERENCING 
    OLD AS OLDROW  FOR EACH ROW  MODE DB2SQL 
BEGIN ATOMIC 
                
DECLARE message VARCHAR(500); 
DECLARE subject VARCHAR(500); 
                
SET subject =  'Signal Host Delete Alert Triggered: Trying to delete 
    Host table entry with the host name = ' || OLDROW.HOSTNAME 
    || ' and host id = ' || OLDROW.HOSTID; 
                
SET message = 'Delete unsuccessful - cannot remove entries from Host table';
                
CALL UTL_MAIL.SEND(<Sender Email>,  <Recipients Email>, NULL, NULL, 
    subject, message);
SIGNAL SQLSTATE '78001'  (message ); 
END

Note: The SIGNAL SQLSTATE statements within the trigger body report error conditions and roll back any changes that are made by the trigger. The SIGNAL statement in Listing 5, after the delete trigger, rolls back the deleted row in the HOST table.

TooFewRowsProduced

In this action, you can create an alert by sending an email if a specific job is finished and produced less than N rows (that is, a job that hasn't produced as much output as you expect). This alert again involves checking the RunMajorStatus column for the finish condition. Inside the trigger body, you can fetch the host name, project name, and job name from the ODB tables and compare them with the required job details on which you would like to monitor the total rows produced. Listing 6 shows the trigger code for this alert.

Listing 6. Trigger code to alert job runs that produced insufficient rows
CREATE TRIGGER DSODB.TOOFEWROWSPRODUCED AFTER  UPDATE OF RUNMAJORSTATUS ON 
    DSODB.JOBRUN  REFERENCING  NEW AS NEWROW  FOR EACH ROW  MODE DB2SQL 
    WHEN ( NEWROW.RUNMAJORSTATUS = 'FIN' )
BEGIN ATOMIC
                
DECLARE hostName VARCHAR(80);
DECLARE projectName VARCHAR(255);
DECLARE jobName VARCHAR(255);
DECLARE message VARCHAR(1500);
                
SET (hostName, projectName, jobName) = (SELECT DISTINCT Z.HOSTNAME, Y.PROJECTNAME, 
    Y.JOBNAME FROM DSODB.JOBRUN X, DSODB.JOBEXEC Y, DSODB.HOST Z WHERE 
    X.JOBID = Y.JOBID AND Y.HOSTID = Z.HOSTID AND Y.JOBID = NEWROW.JOBID);
                
IF (hostName = <HostName> AND projectName = <ProjectName> AND jobName =  <JobName> 
    AND NEWROW.TotalRowsProduced < <TotalRows>) THEN 
SET message = 'The job has produced too few rows, job details: Host name = ' || hostName 
    || '; Project name = ' || projectName || '; Job name = ' || jobName 
    || '; Invocation id = ' || NEWROW.INVOCATIONID || '; Run end time stamp = ' 
    || NEWROW.RUNENDTIMESTAMP;
CALL UTL_MAIL.SEND( <Sender Email>,  <Recipients Email>, NULL, NULL, 
    'Too few rows produced alert triggered', message);
END IF;
END

If you have multiple engine installations on the same host, then you need to use Host ID instead of Host Name in the code in Listing 6, because the host name is the same for different engine installations on the same machine. The value for the host ID needs to be taken from the HOST table matching the host name and the installed engine directory of the job being monitored.

The method in Listing 6 is optimal if you have a small number of jobs. If you want to monitor several conditions, you can create another table to hold many conditions. So, for the example in Listing 6, create a table, TooFewRowsProducedConfig, with details for HostName, ProjectName, JobName, and TotalRows for all of the jobs. Then, inside the trigger body, get the host name, project name, and job name of the triggered job run by querying the ODB tables. You must query the TooFewRowsProducedConfig table for this particular tuple and get the total rows. If Totalrows is available in the config table, then compare this value with the TotalRowsProduced value from the triggered job run. Listing 7 shows the code for creating the TooFewRowsProducedConfig table. Listing 8 then shows how to set up the alert.

Listing 7. Command to create TooFewRowsProducedConfig table
CREATE TABLE DSODB.TOOFEWROWSPRODUCEDCONFIG ( HOSTNAME VARCHAR (80)  NOT NULL , 
    PROJECTNAME VARCHAR (255)  NOT NULL , JOBNAME VARCHAR (255)  NOT NULL , 
    TOTALROWS BIGINT  NOT NULL)
Listing 8. Trigger code for TooFewRowsProduced alert action
CREATE TRIGGER DSODB.TOOFEWROWSPRODUCED AFTER  UPDATE OF RUNMAJORSTATUS ON DSODB.JOBRUN  
    REFERENCING  NEW AS NEWROW  FOR EACH ROW  MODE DB2SQL 
    WHEN ( NEWROW.RUNMAJORSTATUS = 'FIN' )
BEGIN 
                
DECLARE newRowHostName VARCHAR(80); 
DECLARE newRowProjectName VARCHAR(255); 
DECLARE newRowJobName VARCHAR(255); 
DECLARE message VARCHAR(1500);
DECLARE expectedRows BIGINT;
                
SET (newRowHostName, newRowProjectName, newRowJobName) = (SELECT DISTINCT Z.HOSTNAME, 
    Y.PROJECTNAME, Y.JOBNAME FROM DSODB.JOBRUN X, DSODB.JOBEXEC Y, DSODB.HOST Z 
    WHERE X.JOBID = Y.JOBID AND Y.HOSTID = Z.HOSTID AND Y.JOBID = NEWROW.JOBID);
                
SET ( expectedRows ) = (SELECT DISTINCT X.TOTALROWS FROM DSODB.TOOFEWROWSPRODUCEDCONFIG X
    WHERE X.HOSTNAME = newRowHostName AND X.PROJECTNAME = newRowProjectName 
    AND X.JOBNAME = newRowJobName);
                
IF (expectedRows IS NOT NULL) THEN 
  IF( NEWROW.TotalRowsProduced < expectedRows ) THEN
    SET message = 'The job has produced too few rows, job details: Host Name = ' 
    || newRowHostName || '; Project Name = ' || newRowProjectName || '; Job Name = ' 
    || newRowJobName || '; Invocation id = ' || NEWROW.INVOCATIONID 
    || '; Run end time stamp = ' || NEWROW.RUNENDTIMESTAMP 
    || '; Actual Rows = ' || NEWROW.TotalRowsProduced 
    || '; Expected Rows = ' || expectedRows ;
                
    CALL UTL_MAIL.SEND( <Sender Email>,  <Recipients Email>, NULL, NULL, 
    'Too few rows produced alert triggered', message);
  END IF;
END IF;
END

Conclusion

In this article, you have learned how the DataStage Operations Database tables get updated as well as how to construct email alerts on the operations database using database triggers.

Resources

Learn

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=859498
ArticleTitle=Designing an alerts mechanism on the IBM InfoSphere DataStage and QualityStage Operations Console Database, Part 1: Alerts for common scenarios
publish-date=02282013