Scheduling REFRESH or Sync Pull Notify

In some cases, you might want to perform an operation such as a REFRESH or a Sync Pull Notify at regular intervals, for example, every 30 seconds. You can use the SLEEP() function to do this within a stored procedure. The following examples show how to perform tasks at regular intervals by using the SLEEP() function. Note that these examples would be implemented as stored procedures, and the stored procedures would probably be called from the body of a START AFTER COMMIT statement and run in the background. 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 statements 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. Although the duration is set in milliseconds, the duration is approximate. The resolution of the clocks and timers on your platform might 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 does not 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.