6.9 ROLLUP Control and State

Job State and Processing Completion

ROLLUP starts automatically at creation. Repeating START immediately or stopping an already stopped job can cause a state error. This exercise separates states in the sequence create → STOP → insert → START → WAKEUP → FORCE.

CREATE TAG TABLE ch6_control (
    name VARCHAR(32) PRIMARY KEY,
    time DATETIME BASETIME,
    value DOUBLE,
    quality INTEGER
);
CREATE ROLLUP ch6_control_ru ON ch6_control(value)
  INTERVAL 1 MIN WAKEUP INTERVAL 10 SEC;
ALTER ROLLUP ch6_control_ru STOP;
INSERT INTO ch6_control VALUES ('TEMP_01', TO_DATE('2026-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 10.0, 1);
INSERT INTO ch6_control VALUES ('TEMP_01', TO_DATE('2026-01-01 00:00:30', 'YYYY-MM-DD HH24:MI:SS'), 20.0, 1);
INSERT INTO ch6_control VALUES ('TEMP_01', TO_DATE('2026-01-01 00:01:00', 'YYYY-MM-DD HH24:MI:SS'), 30.0, 1);
INSERT INTO ch6_control VALUES ('TEMP_02', TO_DATE('2026-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 100.0, 1);
EXEC TABLE_FLUSH(ch6_control);

SELECT DISTINCT ROLLUP_NAME, ENABLED, INTERVAL_TIME, WAKEUP_INTERVAL
  FROM V$ROLLUP WHERE ROLLUP_NAME = 'CH6_CONTROL_RU';

ALTER ROLLUP ch6_control_ru START;
ALTER ROLLUP ch6_control_ru WAKEUP;
ALTER ROLLUP ch6_control_ru FORCE;

SELECT rollup('min', 1, time) AS bucket, AVG(value)
  FROM ch6_control WHERE name = 'TEMP_01'
 GROUP BY bucket ORDER BY bucket;

While stopped, ENABLED is 0, INTERVAL_TIME is 60000 ms, and WAKEUP_INTERVAL is 10000 ms. The final query returns TEMP_01 averages of 15 at 00:00 and 30 at 00:01.

Instead of SQL ALTER, you can use named EXEC ROLLUP_START(name), ROLLUP_STOP(name), and ROLLUP_FORCE(name) calls. Do not execute the same transition consecutively using both forms. Distinguish unnamed bulk control from control of a specific job.

WAKEUP INTERVAL

If omitted, WAKEUP INTERVAL equals the creation INTERVAL. It must be positive, no greater than the aggregation interval, and divide that interval exactly. More frequent wakeups can reduce lag but increase processing load.

ALTER ROLLUP ch6_control_ru SET WAKEUP INTERVAL 5 SEC;
SELECT DISTINCT ROLLUP_NAME, INTERVAL_TIME, WAKEUP_INTERVAL
  FROM V$ROLLUP WHERE ROLLUP_NAME = 'CH6_CONTROL_RU';

WAKEUP_INTERVAL becomes 5000 ms. The next statement intentionally fails because its interval does not divide 60 seconds.

ALTER ROLLUP ch6_control_ru SET WAKEUP INTERVAL 7 SEC;

Reading V$ROLLUP

SELECT ROLLUP_NAME, ROLLUP_TABLE, SOURCE_TABLE, ROOT_TABLE,
       INTERVAL_TIME, WAKEUP_INTERVAL, LAST_WAKEUP_TIME, NEXT_WAKEUP_TIME,
       ENABLED, RUN_STATE, LAST_ELAPSED_MSEC
  FROM V$ROLLUP WHERE ROLLUP_NAME = 'CH6_CONTROL_RU'
 ORDER BY ROLLUP_NAME;
SHOW ROLLUPGAP;

SHOW ROLLUPGAP is a machsql-only client command; do not send it to an SDK’s ordinary SQL API. GAP is the difference between source and ROLLUP processing RIDs, not elapsed time lag. Check it alongside all hierarchy levels and relevant nodes. gap=0 does not mean corrections to already aggregated source data are reflected. During continuous input it changes with observation time, so pause input for reproducible comparisons.

If retention deletes source data while a job is stopped, START cannot restore it. Run FORCE from lower to upper levels. On failure, check the first error, state, and source accessibility.

Clean Up

DROP ROLLUP ch6_control_ru;
DROP TABLE ch6_control;

For detailed command contracts, see the EXEC Reference and ROLLUP Troubleshooting.