SQL数据库中识别BW进程上次执行后新增/修改记录的方案咨询

1. 基准时间持久化方案 #

优先选择同库控制表存储执行时间基准，避免BW服务故障、配置丢失导致的基准失效：

• 在业务数据所在的SQL数据库中创建专用控制表，建表语句参考：

CREATE TABLE ETL_PROCESS_CONTROL (
  PROCESS_NAME VARCHAR(100) PRIMARY KEY COMMENT 'BW进程唯一标识',
  LAST_SUCCESS_RUN_TS DATETIME NOT NULL COMMENT '上次执行成功的时间戳',
  CURRENT_RUN_TS DATETIME NOT NULL COMMENT '本次进程启动的时间戳'
);
-- 首次运行初始化基准数据
INSERT INTO ETL_PROCESS_CONTROL VALUES ('你的BW进程唯一名称', '2020-01-01 00:00:00', '2020-01-01 00:00:00');

• 若为单机测试场景，也可使用Tibco BW内置的持久化共享变量存储时间基准，集群部署场景不推荐该方案，易出现变量同步偏差。

2. 进程执行流程设计 #

严格遵循「先记启动时间→拉取增量→处理完成再更新成功基准」的流程，避免数据漏采或重复采集：

• 进程启动第一步，更新本次启动时间：

UPDATE ETL_PROCESS_CONTROL 
SET CURRENT_RUN_TS = CURRENT_TIMESTAMP 
WHERE PROCESS_NAME = '你的BW进程唯一名称';

• 第二步，筛选增量数据，使用左闭右开的时间区间避免临界时间数据丢失：

SELECT * FROM 业务表 
WHERE last_modified_timestamp >= (SELECT LAST_SUCCESS_RUN_TS FROM ETL_PROCESS_CONTROL WHERE PROCESS_NAME = '你的BW进程唯一名称')
AND last_modified_timestamp < (SELECT CURRENT_RUN_TS FROM ETL_PROCESS_CONTROL WHERE PROCESS_NAME = '你的BW进程唯一名称');

• 第三步，所有增量数据处理完成（写入目标端、校验通过无报错）后，再更新成功执行基准：

UPDATE ETL_PROCESS_CONTROL 
SET LAST_SUCCESS_RUN_TS = CURRENT_RUN_TS 
WHERE PROCESS_NAME = '你的BW进程唯一名称';

若本次进程执行报错，不更新LAST_SUCCESS_RUN_TS字段，下次启动仍会从上次成功的时间点开始拉取数据，完美覆盖进程停运场景。

3. 可选优化项 #

• 若要求数据处理完全幂等，可在业务表新增PROCESS_STATUS字段，拉取到数据后先标记为处理中，处理完成后标记为已处理，避免重复数据产生副作用。
• 若增量数据量较大，可给业务表的last_modified_timestamp字段加索引，提升筛选效率。

内容的提问来源于stack exchange，提问作者clarknj