Full synchronization of time series from RDB tables to PI Data Archive

In some scenarios it is desirable to keep events in the Data Archive in full sync with rows in RDB tables. To do that, the RDBMSPI interface implements a mode where inserts, updates and deletes from an RDB table are all reflected in the PI System. The synchronization direction is from RDB to the PI System only; that means that modifications to the Data Archive do not trigger any action on RDB tables. The following section describes the settings to enable the full synchronization mode and describes the format of the retrieved result-sets (WHERE clause in SQL queries):

• The interface must be configured with the startup parameter /SYNC_TIME, which defines the period on which the synchronization occurs.

• The laboratory caching mode /LB mode must be enabled for the interface.

• To enable this feature on a per-point basis, Location5 for the point must be set to 4.

• This mode is only implemented for points retrieving events through individual SQL queries; that is, only for points using the single delivery strategy.

• The individual SQL queries must have a WHERE clause, which constrains the retrieved rows using the timestamp column, that exactly matches the interval specified by the /SYNC_TIME startup parameter.

• For example: /SYNC_TIME=*-60m,*+30m

-- SQL query for the Oracle RDBMS:

SELECT timestamp, value, status FROM table1
WHERE timestamp BETWEEN sysdate-60*60./86400. -- 60./86400. represents 1min
AND sysdate+30*60./86400.

-- SQL query for SQL Server RDBMS:

SELECT timestamp, value, status FROM table1
WHERE timestamp BETWEEN getdate()-60*60./86400. -- 60./86400. represents 1min
AND getdate()+30*60./86400.