Passing Job Status Change Information

Passing job status change attributes such as new status, job name, job type, notification rule, or rule owner to PL/SQL procedures or OS commands/scripts allows you to customize automated responses to status changes. For example, you many want to call an OS script to open a trouble ticket for an in-house support trouble ticket system if a critical job fails to run. In this case you will want to pass status such as Problems or Aborted to the script to open a trouble ticket and escalate the problem.

Passing Information to a PLSQL Procedure

The notification system passes job status change information to a PL/SQL procedure via the MGMT_NOTIFY_JOB object. An instance of this object is created for every status change. When a job changes status, the notification system calls the PL/SQL procedure associated with the notification rule and passes the populated object to the procedure. The procedure is then able to access the fields of the MGMT_NOTIFY_JOB object that has been passed to it.

The following table lists all job status change attributes that can be passed:

When a job status change occurs for the job, the notification system creates an instance of the MGMT_NOTIFY_JOB object and populates it with values from the status change. The following status codes have been defined as constants in the MGMT_JOBS package and can be used to determine the type of status in the job_status field of the MGMT_NOTIFY_JOB object.

Job Status Codes

Example: PL/SQL Procedure Using a Status Code

CREATE TABLE job_log (jobid RAW(16),
occured DATE);

PL/SQL Procedure

CREATE OR REPLACE PROCEDURE LOG_PROBLEM_JOBS(status_change IN MGMT_NOTIFY_JOB)
IS
BEGIN
-- Log all failed jobs
IF status_change.job_status = MGMT_JOBS.FAILED_STATUS
THEN
BEGIN
INSERT INTO job_log (jobid, occured)
VALUES (status_change.job_guid, SYSDATE);
EXCEPTION
WHEN OTHERS
THEN
-- If there are any problems then get the notification retried
RAISE_APPLICATION_ERROR(-20000, 'Please retry');
END;
COMMIT;
END IF;
END LOG_PROBLEM_JOBS;

Using a PL/SQL Procedure:

1. Create the PL/SQL procedure on the repository database using the following procedure specification:

   PROCEDURE p(status_change IN MGMT_NOTIFY_JOB)

   If an error is encountered during the running of the procedure, the Notification System can be instructed to retry the sending of the notification to the procedure by raising a user defined exception that uses the error code -20000. See the example above. The procedure initially retried after one minute, then two minutes, then three minutes and so on, until the notification is a day old, at which point it will be purged.

2. Add this PLSQL procedure as a notification method that can be called in Notification Rules. See Adding a PLSQL Procedure.

   Make sure to use a fully qualified name that includes the schema owner, package name and procedure name. The procedure will be executed by the repository owner and so the repository owner must have EXECUTE permission on the procedure.

3. Associate the PL/SQL procedure with a notification rule.

Passing Information to an OS Script or Executable

The notification system passes severity information to an OS script or executable via system environment variables. Conventions used to access environmental variables vary depending on the operating system:

• UNIX: $ENV_VARIABLE

• MS Windows: %ENV_VARIABLE%

The notification system sets the following environment variables before calling the script.

Your script may reference some or all of these variables.

Using an OS Script or Executable

• Create an OS command on the repository host machine.

  Create an OS Command on each Management Service machine that connects to the Management Repository. The OS Command should be an absolute pathname and must be the same on each Management Service host machine e.g. /u1/bin/logSeverity.sh. The command is run by the user who started the Management Service. If an error is encountered during the running of the OS Command, the Notification System can be instructed to retry the sending of the notification to the OS Command by returning an exit code of 100. The procedure is initially retried after one minute, then two minutes, then three minutes and so on, until the notification is a day old, at which point it will be purged.

  The following parameter in emoms.properties controls how long the OS Command can execute without being killed by the Management Service. This is to prevent OS Commands from running for an inordinate length of time and blocking the delivery of other notifications. By default the command is allowed to run for 30 seconds before it is killed. See (add a pointer to the other doc that discusses all these parameters)

  # The amount of time in seconds after which an OS Command started by the

  # Notification System will be killed if it has not exited

  # em.notification.os_cmd_timeout=30

• Add this OS command as a notification method that can be called in Notification Rules. See Adding an OS Command.

• Define a notification rule (choose the targets and conditions for which you want to be notified), and associate the OS command with the rule. See Defining Notification Rules.

Related Topics

Enterprise Manager Information Roadmap

Copyright © 1996, 2007, Oracle. All rights reserved.