Scheduling REFRESH or Sync Pull Notify

In some cases, you may want to perform an operation such as a REFRESH or a Sync Pull Notify at regular intervals, for example, every 30 seconds. You may use the SLEEP() command to do this within a stored procedure. Below are 2 examples of performing tasks at regular intervals by using the SLEEP() command. Note that these examples would be implemented as stored procedures, and the stored procedures would probably be run in the background by getting called from the body of a START AFTER COMMIT command. Note that the parameter passed to the SLEEP() function is the desired duration (in milliseconds).

Here is a very simple example of using sleep:

CREATE PROCEDURE SIMPLE_CLOCK
RETURNS (T TIMESTAMP)
BEGIN
  -- Loop "forever".
  WHILE 1 LOOP T := NOW();
    RETURN ROW;
    EXEC SQL COMMIT WORK;
    SLEEP(1000);
  END LOOP
END

Here is an example of scheduling REFRESH commands by using sleep() in procedure code:

CREATE PROCEDURE REFRESH_SCHEDULER
BEGIN
  DECLARE I INTEGER;
  I := 0;
  WHILE I = 0 LOOP
    EXEC SQL COMMIT WORK;
    SLEEP(10000);
    -- here the procedure sleeps 10 seconds (10000ms)
    EXEC SQL EXECDIRECT call refresh_now;
  END LOOP
END

You can extend this approach to apply it to Sync Pull Notify.

The SLEEP function can be called from stored procedures. The duration is measured in milliseconds. Note that the duration is approximate. The resolution of the clocks and timers on your platform may not support millisecond accuracy. Also, the exact timing depends in part upon how busy the computer is. Furthermore, any SQL statement or procedure call that is executed in a START AFTER COMMIT statement is run asynchronously in the background and doesn't have very precise execution timing. Finally, the duration of the non-sleep activities also affects the timing. For example, if your loop contains a SLEEP() that lasts 10 seconds and an SQL statement that takes 2 seconds to run, then your loop will actually run approximately every 12 seconds, not every 10 seconds.