Learn about principles of operation for RDBMS

To read data from an RDBMS into the PI Data Archive, you define input tags, which are associated with SELECT queries that return columns containing values for the following tag attributes:

• Timestamp

• Value

• Status

• Questionable (optional)

• Annotation (optional)

To write data from the PI Data Archive to the RDBMS, you define output tags, which are associated with INSERT, UPDATE, or DELETE queries. Queries can contain placeholders for replaceable parameters. Placeholders are evaluated when the query is executed. For efficiency in reading data from the RDBMS and writing it to the PI Data Archive, the interface supports several distribution strategies, depending on the structure of the result rows returned by the SELECT query:

• One PI point value per row: Each row returned in the result set returned by the SELECT query contains a single timestamp, value, status and (optionally) questionable and/or annotation, but no PI tag name. For details, see Updating a single tag in this document.

• Multiple PI point values per row (the "tag group" approach): Each row in the result set contains values for more than one PI point. To associate a specific column with a PI point, you specify the column position in the Location3 point attribute. For details, see Update a predetermined set of tags (tag groups) in this document.

• Read the PI point name from the row: To handle result sets that include rows for multiple PI points, the interface provides the "tag distribution" and "RxC" (row by column) distribution approaches, depending on whether the row includes one value for one tag or values for multiple tags. For details, see Update from multi-value rows containing name of target points (RxC) in this document.

Tags can be scanned or event-driven. Scanned tags are read or written at a regular interval that is defined by the scan class assigned to the tag. Event-driven tags are read or written when a specified trigger tag is updated. The interface provides several options for handling data that does not arrive in timestamp sequence (out-of-order data).

Besides collecting data in real time, the interface can recover historical data from Data Archive to an RDBMS or from the RDBMS to Data Archive. You can configure the period to be restored, and after recovery is complete, the interface can either exit or begin collecting real- time data.

The interface runs on Windows either as a console application or Windows service. It uses ODBC to connect to the RDBMS. For the ODBC connection, you define the data source name (DSN) using the Data Sources (ODBC) Windows control panel and configure the interface instance to connect to the database using the DSN.