Relational Database(rdbms via odbc) Interface

Recording PI Point Database Changes

The interface can record changes made to the PI Point Database. The concept is similar to the regular output point handling. The difference is that the Managing Tag is not triggered by a snapshot event, but by a point attribute modification.

Note: The Managing tag is recognized by having Location4 = -1 or Location4 = -2.

Short Form Configuration

When Location4 is set to –1, the interface expects a subset of the AT.* placeholders in the INSERT query. This statement (INSERT) thus has to be configured and the Managing Tag executes it always when there is a point attribute change. The following table summarizes the placeholders supported in the short form:

PI Point Database Replication – Short Form

Example of the RDB Table Structure for the PIPoint Changes Recording	Placeholder
TAG_NAME (SQL_CHAR)	AT.TAG
ATTRIBUTE_NAME (SQL_CHAR)	AT.ATTRIBUTE
CHANGE_DATETIME (SQL_TIMESTAMP)	AT.CHANGEDATE
CHANGER (SQL_CHAR)	AT.CHANGER
NEW_VALUE (SQL_CHAR)	AT.NEWVALUE
OLD_VALUE (SQL_CHAR)	AT.OLDVALUE

See this example available in Appendix B: Examples:

Example 4.1 – PI Point Database Changes – Short Form Configuration

Note: The interface stores the number of executed queries into the Managing Tag.In the Short Form, nothing is stored when a point was edited and no real attribute change has been made.

Note: By default the interface checks for attribute changes each 2 minutes. It can therefore happen that when an attribute is changed twice within 2 minutes ending with its original value, the interface will NOT record this change. Since RDBMSPI 3.11, the two minutes interval can be changed by specifying the start-up parameter /UPDATEINTERVAL

Long Form Configuration

Location4 = –2 indicates that all AT.* placeholders can be employed (see section SQL Placeholders for the complete list). In this mode, the interface does not remember what the previous attribute value was and just forwards the current PI point attributes state to RDB. The overall principles are the same as with the short form. That is, any attribute change recognized by the interface is the trigger for the SQL statement (INSERT) execution.

See this example available in Appendix B: Examples

Example 4.2 – PI Point Database Changes – Long Form Configuration (only changedate and tag name recorded)

Note: The interface stores the number of executed queries into the Managing Tag.

10. PI Batch Database Output

The PI Batch Database can be replicated to RDB tables in a timely manner. That is, the interface remembers the timestamp of the last batch that was INSERTed during the previous scan, and via the Managing Tags (tags that hold and execute the INSERT statements) it keeps storing the newly arrived batches/unit-batches/sub-batches into RDB tables. The Managing Tags are recognized by the presence of any of the PI Batch Database placeholders; see section SQL Placeholders for more details. That means they are configured as standard input tags (Location4 defines the scan frequency) and just one occurrence of the 'BA.*' placeholder marks them as the batch replicator(s). The batch replication thus resembles the execution of output statements (e.g. INSERT) that periodically send out snapshot values.

PI Batch Database Replication without Module Database

The interface allows for replication of batch records in a form similar to the structure of the PIBatch table visible via PI ODBC or PI OLEDB. The following list shows placeholders that can be used:

PI Batch Database Replication without MDB (Old Batches)

Property	RDB data type	Placeholder
Batch ID	Character string up to 256 bytes	BA.BAID
Unit	Character string up to 256 bytes	BA.UNIT
Product	Character string up to 256	BA.PRODUCT
Start Time	Timestamp	BA.START
End Time	Timestamp	BA.END

The example referenced below demonstrates how to replicate the whole PI Batch Database using a standard input point carrying a simple INSERT statement. The interface periodically asks for new batches since the previous scan and only the closed batches (batches with non-zero end time) are stored.

Note: The optional /RECOVERY_TIME=*-1d start-up parameter applies here in terms of going back into the PI Batch Database for the specified time period.

Note: The input point carrying the INSERT statement receives the number of inserted batches after each scan. It is therefore advisable to define this point as numeric.

See this example available in Appendix B: Examples

• 
  Example 5.1 – Batch Export (not requiring Module Database)