The sa_validate() procedure can be used to validate various aspects of your database in order to catch any data corruptions and allow you to address them before you get into a production-down situation.  Performing validation as part of the backup process is generally a good idea, and can be automated by leveraging the SQL Anywhere event system.  Here is an example of a backup event which validates the database before the backup is taken.  If there are any problems, an e-mail is sent to the administrator.

 CREATE EVENT "DBA"."BackupDatabase" DISABLE AT ALL HANDLER BEGIN   DECLARE res_validate VARCHAR(250);   DECLARE res_backup VARCHAR(250);   DECLARE backup_dir VARCHAR(250);   DECLARE crsr_validate dynamic scroll cursor FOR CALL sa_validate();     -- First validate the database to make sure we are not backing up a corrupted database   OPEN crsr_validate;   FETCH NEXT crsr_validate INTO res_validate;   IF res_validate <> 'No error detected' THEN     CALL xp_startsmtp('mailuser','mailserver.xyz.com’);     CALL xp_sendmail('admin@xyz.com','Database Backup Failed!',NULL,NULL,NULL,'Validation failed for database: ' || res_validate);     CALL xp_stopsmtp();       RETURN   END IF;       --Once we are satisfied the database is in good condition, we back up the database and log   -- We use a 7 day set of rolling backups       SET backup_dir = 'c:ackup ' + dayname(today());       BACKUP DATABASE DIRECTORY backup_dir;   EXCEPTION WHEN OTHERS THEN     SELECT errormsg() INTO res_backup;     CALL xp_startsmtp('mailuser','mailserver.xyz.com’);     CALL xp_sendmail('admin@xyz.com','Database Backup Failed!',NULL,NULL,NULL,'Backup failed for database: ' || res_backup);     CALL xp_stopsmtp(); END; --Add a schedule for the backup event to run it every day ALTER EVENT "BackupDatabase" ADD SCHEDULE "BackupSched" START TIME '23:00:00' ON ('Sunday','Saturday','Friday','Thursday','Wednesday','Tuesday','Monday') 

One oddity you might notice about the above example, is that in order to detect a validation failure, we have to look for a specific string:

  IF res_validate <> 'No error detected'

With older versions of SQL Anywhere, the only way to determine if there were no validation errors was to look for the string ‘No error detected’ in the result set. A compounding problem is that this string is also localized to all of the languages that SQL Anywhere supports, so there is no straight forward way to code this check for all languages.

In version 17, we are addressing this by adding two new columns to the sa_validate() result set in addition to the existing “Messages” column, which make it possible to check for success consistently, regardless of deployment language.

The first column is “IsValid”, which is a bit value which is set to 1 if the validation is clean and set to 0 if there are any validation errors.

The second column is “ObjectName”, which is empty if the validation is clean.  If there is a validation error, this column contains the name of the database or the table that failed validation.

The above backup event can now be updated to include the two new columns in the sa_validate() result set and simply check the “IsValid” column to determine success/failure of the validation.

 CREATE EVENT "DBA"."BackupDatabase" DISABLE AT ALL HANDLER BEGIN   DECLARE res_backup VARCHAR(250);   DECLARE backup_dir VARCHAR(250);   DECLARE res_messages VARCHAR(250);   DECLARE res_isvalid integer;   DECLARE res_object VARCHAR(250);   DECLARE crsr_validate dynamic scroll cursor FOR CALL sa_validate();     -- First validate the database to make sure we are not backing up a corrupted database   OPEN crsr_validate;   FETCH NEXT crsr_validate INTO res_messages, res_isvalid, res_object;   IF res_isvalid = 0 THEN --validation failed     CALL xp_startsmtp('mailuser','mailserver.xyz.com');     CALL xp_sendmail('admin@xyz.com','Database Backup Failed!',NULL,NULL,NULL,'Validation failed for database: ' || res_object || '
 ' || res_messages );     CALL xp_stopsmtp();       MESSAGE 'Validation failed for database: ' || res_object || '
 ' || res_messages;     RETURN   END IF;   --Once we are satisfied the database is in good condition, we back up the database and log   -- We use a 7 day set of rolling backups       SET backup_dir = 'c:ackup ' + dayname(today());       BACKUP DATABASE DIRECTORY backup_dir;   EXCEPTION WHEN OTHERS THEN     SELECT errormsg() INTO res_backup;     CALL xp_startsmtp('mailuser','mailserver.xyz.com’);     CALL xp_sendmail('admin@xyz.com','Database Backup Failed!',NULL,NULL,NULL,'Backup failed for database: ' || res_backup);     CALL xp_stopsmtp(); END; --Add a schedule for the backup event to run it every day ALTER EVENT "BackupDatabase" ADD SCHEDULE "BackupSched" START TIME '23:00:00' ON ('Sunday','Saturday','Friday','Thursday','Wednesday','Tuesday','Monday') 

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !