Daily Backup via DB Scheduler

Applications that embed the database as part of their solution have challenging requirements regarding the data server:

• a customizable, small footprint
• a hardware gentle architecture
• rock-solid reliability
• flexible high availability options
• extremely low administration overhead
• smoothly backward application compatibility
• excellent performance

If you look at each of those requirements it becomes clear why IBM favors IDS as the data server of choice for integrated solutions. The new database scheduler introduced in IDS V11 offers extensive possibilities to automate routine tasks. Probably one of the most important DBA routine tasks is the regular backup of the data server.

The following example shows how you might automate this task by exploiting the IDS V11 database scheduler. The example is based on Linux and uses the Informix ontape utility.

onconfig Parameters

As prerequisite you should set the following parameters in your $ONCONFIG file:

• TAPEDEV
  Should be set to a filesystem directory that is readable/writable by informix
  Example: TAPEDEV /home/informix/backups
• BACKUP_FILTER
  Should be set to a compression utility to reduce the backup file size
  Example: BACKUP_FILTER /usr/bin/gzip
• RESTORE_FILTER
  Should be set to a utility that is able to de-compress the backup data for restore
  Example: RESTORE_FILTER '/usr/bin/gzip -d -c'

Backup Procedure

We will create a stored procedure that - depending on the current weekday - either performs a Level-0 or a Level-1 Backup. In addition we will check the returncode of the ontape backup command and insert an appropriate row into table sysadmin:ph_alert. This allows us to view the events generated by the p_ontape procedure thru the Open Admin Tool.

database sysadmin;

create procedure p_ontape
(
   v_task_id      integer,
   v_task_seq     integer
)
   define v_backup_level   integer;
   define v_sql_rc         integer;
   define v_system_rc      integer;
   define v_weekday        smallint;
   define v_backup_cmd     char(256);
   define v_color          like ph_alert.alert_color;
   define v_type           like ph_alert.alert_type;
   define v_msg            like ph_alert.alert_message;

   -- determine the current weekday (0=sunday, 1=monday...)
   select weekday(today)
      into v_weekday
      from sysmaster:sysdual;

   if v_weekday = 0 then
      -- Level-0-Backup on Sunday
      let v_backup_level = 0;
   else
      -- Level-1-Backup on all other days
      let v_backup_level = 1;
   end if

   -- initialize variables
   let v_sql_rc = 0;
   let v_system_rc = 0;

   begin
      -- catch exception
      on exception in (-668)
         set v_sql_rc, v_system_rc
      end exception
      -- create and execute backup command
      let v_backup_cmd = "ontape -s -L " || v_backup_level;
      system v_backup_cmd;
   end


   -- check returncode
   if (v_system_rc = 0)
   then
      let v_type  = "INFO";
      let v_color = "GREEN";
   else
      let v_type  = "ERROR";
      let v_color = "RED";
   end if

   -- create message text
   let v_msg   = "Command [" || trim(v_backup_cmd) || "], Returncode [" || v_system_rc || "]";

   -- insert row into table sysadmin:ph_alert
   insert into ph_alert
   (
      id,
      alert_task_id,
      alert_task_seq,
      alert_type,
      alert_color,
      alert_state,
      alert_object_type,
      alert_object_name,
      alert_message,
      alert_action
   )
   values
   (
      0,
      v_task_id,
      v_task_seq,
      v_type,
      v_color,
      "NEW",
      "USER",
      "ontape",
      v_msg,
      null
   );

end procedure

document "This procecure performs an ontape backup";

Database Scheduler Task

Now we need to define a new task for the database scheduler by inserting a new row into table sysadmin:ph_task.

insert into ph_task
(
   tk_name,
   tk_type,
   tk_group,
   tk_description,
   tk_execute,
   tk_start_time,
   tk_stop_time,
   tk_frequency,
   tk_next_execution
)
values
(
   "ontape_backup",
   "TASK",
   "BACKUP",
   "execute ontape backup",
   "p_ontape",
   null,
   null,
   interval(1) day to day,
   datetime(2007-12-15 01:00:00) year to second
);

The individual columns and their meaning:

• tk_name
  The individual name of this task. Must be unique.
• tk_type
  The type of this task. Possible valid values are:
  • SENSOR
  • TASK
  • STARTUP SENSOR
  • STARTUP TASK
• tk_group
  The logical group this task/sensor belongs to. The columns references the ph_group.group_name column. The default group is MISC.
• tk_description
  A description of this task/sensor.
• tk_execute
  The SQL statement, Stored Procedure, Function or C-/Java-UDR that should be executed.
• tk_start_time
  tk_start_time and tk_stop_time specify the time-frame in which the task/sensor should be executed in the given frequency (tk_frequency). The default is from 08:00:00h thru 19:00:00h.
• tk_stop_time
  see tk_start_time
• tk_frequency
  Specifies the frequency for the task/sensor to be executed.
• tk_next_execution
  The next execution time of the task/sensor. The default is current. That means if you don't specify a next execution time, the task/sensor will be triggered immediately.

The backup task is now ready to execute automatically each day at 01:00:00h. Make sure that new backups are appearing in the directory specified by the TAPEDEV parameter and use the Open Admin Tool to view the generated alerts created by procedure p_ontape.

Administrating the Scheduler

Here are a few helpful commands for administrating and monitoring the database scheduler. Keep in mind that it is not necessary to re-start the database scheduler in order to get our new backup job executed. The scheduler works complete dynamic and automatically detects new jobs.

• Relocating the sysadmin database to another dbspace

  execute function sysadmin:task("reset sysadmin", "new_dbspace");

• Stopping the scheduler

  execute function sysadmin:task("scheduler shutdown");

• Starting the scheduler

  execute function sysadmin:task("scheduler start");

• Monitoring the scheduler

  • onstat -g dbc

  • onstat -m

  • Direct selects on the appropriate sysadmin tables
  • Open Admin Tool (Health Center)