Relational Database(rdbms via odbc) Interface

Concept of Data Output from PI to Relational Database

Transferring data from PI to a relational database works similarly to RDB reading; that is, an appropriate SQL statement (usually INSERT) needs to be specified. Statements are executed either event driven (sign-up for snapshot), or on a periodical basis. For copying data from PI to a relational database, the event based approach is used most often. To achieve this, an output tag (a tag that actually executes a SQL statement) must have a reference to its SourceTag. The SourceTag triggers the actual execution and the output tag itself then gets a copy of the exported data to signal the success or failure of the output operation. For periodical output, again, a DML statement is needed. The supported Data Manipulation Language statements are: INSERT, UPDATE, DELETE commands or the Stored Procedure call, but the statements are specified in tags that look like input points, which are executed in scan classes. More detailed description can be found in section Output from PI.

See examples in these sections:

• 
  Example 2.1a – insert sinusoid values into table (event based).
  
• 
  Example 2.1b – insert sinusoid values into table (scan based).
  
• 
  Example 2.1c – insert 2 different sinusoid values into table (event based).
  
• 
  Example 2.1d – insert sinusoid values with (string) annotations into RDB table (event based).
  

Use of PI SDK

RDBMSPI features implemented through PI SDK are the following:

• 
  Writing to and reading from PI Annotations – Next to the timestamp, value and status, RDBMSPI interface can write/read also to PI annotations (see section Data Acquisition Strategies and examine the PI_ANNOTATION keyword).
  
• 
  Replication of PI Batch Database – PI Batch Database can be replicated to RDB; see chapter PI Batch Database Output.
  
• 
  Recording PI Point Database Changes – See chapter Recording of PI Point Database Changes
  

All the above mentioned features are optional. However, users have to be aware that when these features are configured on nodes with buffering; that is, either PI Buffer Server (bufserv) or the PI Buffer Subsystem (pibufss) are running, buffering will be bypassed.

CAUTION! When RDBMSPI interface runs against High Availability PI Servers, SQL queries containing the annotation column will NOT deliver events to other PI Servers than the primary.

Note: Events with annotations will always bypass exception reporting.

Use of PI SDK requires the PI Known Server’s Table contains the PI Server name the interface connects to.

Note: In order to make use of PI SDK communication, set the start-up parameter PISDK=1 or enable PI SDK through the PI ICU.

UniInt Failover

This interface supports UniInt failover. Refer to the UniInt Failover Configuration section of this document for configuring the interface for failover.

3. Installation Checklist

If you are familiar with running PI data collection interface programs, this checklist helps you get the Interface running. If you are not familiar with PI interfaces, return to this section after reading the rest of the manual in detail.

This checklist summarizes the steps for installing this Interface. You need not perform a given task if you have already done so as part of the installation of another interface. For example, you only have to configure one instance of Buffering for every Interface Node regardless of how many interfaces run on that node.

The Data Collection Steps below are required. Interface Diagnostics and Advanced Interface Features are optional.

Note: The steps below should be followed in the order presented.

Data Collection Steps

1. 
   Confirm that you can use PI SMT to configure the PI Server. You need not run PI SMT on the same computer on which you run this Interface.
   
2. 
   If you are running the Interface on an Interface Node, edit the PI Server’s Trust Table to allow the Interface to write data.
   
3. 
   Run the installation kit for the PI Interface Configuration Utility (ICU) on the interface node if the ICU will be used to configure the interface. This kit runs the PI SDK installation kit, which installs both the PI API and the PI SDK.
   
4. 
   Run the installation kit for this Interface. This kit also runs the PI SDK installation kit which installs both the PI API and the PI SDK if necessary.
   
5. 
   If you are running the Interface on an Interface Node, check the computer’s time zone properties. An improper time zone configuration can cause the PI Server to reject the data that this Interface writes.
   
6. 
   Run the ICU and configure a new instance of this Interface. Essential startup parameters for this Interface are:
   

Point Source (/PS=x)

Interface ID (/ID=#)

PI Server (/Host=host:port)

Scan Class(/F=##:##:##,offset)

Data Source Name(/dsn)

Interface log file(/output)

7. 
   If you will use digital points, define the appropriate digital state sets.
   
8. 
   Build input tags and, if desired, output tags for this Interface. Important point attributes and their purposes are:
   

Location1 specifies the Interface instance ID.

Location2 specifies bulk vs. non-bulk reading.

Location3 defines the reading strategy.

Location4 specifies the scan class.

Location5 specifies how the data is sent to PI (snapshot, archive write,..).

ExDesc stores the various keywords.

InstrumentTag specifies name of the file that stores the SQL file.

SourceTag for output points.

9. 
   Configure the interface using the PI ICU utility or edit startup command file manual. It is recommended to use PI ICU whenever possible.
   
10. 
    Configure performance points.
    
11. 
    Configure the I/O Rate Tag.
    
12. 
    It is recommended to test the connection between the interface node and the RDB using any third-party ODBC based application. For example the ODBC Test app. From Microsoft or any other tool that works with ODBC data sources. Verify that the SQL query(ies) are syntactically correct and they deliver data from/to the above mentioned third-party ODBC based application.
    
13. 
    Start with one simple SQL statement or with the ‘tested’ one and verify the data in PI.
    
14. 
    Set or check the interface node clock.
    
15. 
    Start the Interface interactively and confirm its successful connection to the PI Server without buffering.
    
16. 
    Confirm that the Interface collects data successfully.
    
17. 
    Stop the Interface and configure a buffering application (either Bufserv or PIBufss). When configuring buffering use the ICU menu item Tools  Buffering…  Buffering Settings to make a change to the default value (32678) for the Primary and Secondary Memory Buffer Size (Bytes) to 2000000. This will optimize the throughput for buffering and is recommended by OSIsoft.
    
18. 
    Start the buffering application and the Interface. Confirm that the Interface works together with the buffering application by either physically removing the connection between the Interface Node and the PI Server Node or by stopping the PI Server.
    
19. 
    Configure the Interface to run as a Service. Confirm that the Interface runs properly as a Service.
    
20. 
    Restart the Interface Node and confirm that the Interface and the buffering application restart.