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 RDBMS PI 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; meaning 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.

  Note: The /SYNC_TIME parameter uses the PI System relative time notation and is limited to s, m, h, and d (seconds, minutes, hours, and days respectively).

• 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.