Receive email notification about remote database errors

Use the following procedure to send email notification when an error occurs at a remote database. You can use email or a paging system to receive the notifications.

To set up SQL Remote to send email notification of errors (SQL)

Connect to the consolidated database as user Cons.

Create a stored procedure that notifies the DBA user by email that an error has occurred.

For example, execute the following to create the sp_LogReplicationError procedure:

CREATE PROCEDURE cons.sp_LogReplicationError
 ( IN error_text LONG VARCHAR )
BEGIN
 DECLARE current_remote_user CHAR( 255 );
 SET current_remote_user = CURRENT REMOTE USER;
 // Log the error
 INSERT INTO cons.replication_audit
  ( remoteuser, errormsg )
 VALUES
  ( current_remote_user, error_text );
 COMMIT WORK;
 //Now notify the DBA by email that an error has occurred
 // on the consolidated database. The email should contain the error
 // strings that the Message Agent is passing to the procedure. 
 IF CURRENT PUBLISHER = 'cons' THEN
  CALL sp_notify_DBA( error_text );
 END IF
END;

Create a stored procedure that manages the sending of email.

For example, execute the following statement to create the sp_notifiy_DBA procedure:

CREATE PROCEDURE sp_notify_DBA( in msg long varchar)
BEGIN
 DECLARE rc INTEGER;
 rc=call xp_startmail( mail_user='davidf' );
 //If successful logon to mail
 IF rc=0 THEN
 rc=call xp_sendmail(
   recipient='Doe, John; Smith, Elton',
   subject='SQL Remote Error',
   "message"=msg);
 //If mail sent successfully, stop
  IF rc=0 THEN
   call xp_stopmail()
  END IF
 END IF
END;

Set the replication_error database option to call the procedure that notifies the DBA by email that an error occurs.

For example, execute the following statement to call the sp_LogReplicationError procedure when an error occurs:
SET OPTION PUBLIC.replication_error = 'cons.sp_LogReplicationError';

Create an audit table.

For example, execute the following to create the replication_audit table:

CREATE TABLE replication_audit (
 id   INTEGER DEFAULT AUTOINCREMENT,
 pub  CHAR(30) DEFAULT CURRENT PUBLISHER,
 remoteuser CHAR(30),
 errormsg LONG VARCHAR,
 timestamp  DATETIME DEFAULT CURRENT TIMESTAMP,
 PRIMARY KEY (id,pub)
);

The following table describes the columns of the replication_audit table:

Column	Description
pub	Current publisher of the database (identifies the database in which the publisher was inserted).
remoteuser	Remote user applying the message (identifies the database from which the remote user came from).
errormsg	Error message passed to the replication_error procedure.

Test your procedures.

For example, insert a row on the consolidated database that uses the same primary key as a row on a remote database. When this row from the consolidated database is replicated to the remote database, a primary key conflict error occurs and:

The remote database Message Agent (dbremote) prints the following message to its output log:

Received message from "cons" (0-0000000000-0) 
SQL statement failed: (-193) primary key for table 'reptable' is not unique 
INSERT INTO cons.reptable( id,text,last_contact ) 
VALUES (2,'dave','1997/apr/21 16:02:38.325') 
COMMIT WORK

The following INSERT statement is sent to the consolidated database:

INSERT INTO cons.replication_audit
 ( id,
   pub,
   remoteuser,
   errormsg,
   "timestamp")
VALUES
 ( 1,
  'cons',
  'sales',
  'primary key for table ''reptable'' is not unique (-193)',
  '1997/apr/21 16:03:13.836');
COMMIT WORK;

An email is sent to John Doe and Elton Smith with the following message:

primary key for table 'reptable' is not unique (-193) 
INSERT INTO cons.reptable( id,text,last_contact ) VALUES (2,'dave','1997/apr/21 16:02:52.605')

Receive email notification about remote database errors

To set up SQL Remote to send email notification of errors (SQL)

See also