SQL Anywhere 17 - A More Worldly sa_validate() procedure

[JasonHinsperger]

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:\backup\ ' + 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 || '\n ' || res_messages );

    CALL xp_stopsmtp();  

    MESSAGE 'Validation failed for database: ' || res_object || '\n ' || 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:\backup\ ' + 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')