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.
Use the SQL Anywhere 12 plug-in to 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 SQL Remote 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 SQL Remote 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') |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |