Relational Database(rdbms via odbc) Interface

Checklist and Trouble-Shooting

From experience supporting this interface, OSIsoft has assembled a number of check points that should help beginners with getting to the right configuration:

No Data (Input)

• 
  If PI_... column names are not used, then the position of timestamp, value and status columns have to follow certain rules.
  
• 
  The status column is mandatory when not using PI_... column names.
  
• 
  The PI_TIMESTAMP column (or its equivalent if PI_... column names are not used) must be of data type SQL_TIMESTAMP.
  
• 
  If the query is directly specified in the Extended Descriptor, the query string must be preceded by /SQL=
  
• 
  Distribution target tags must be in the same scan class as the Distributor Tag.
  
• 
  /ALIAS comparison is case sensitive
  

Data Loss

• 
  Data can arrive to the RDB table at current time but carry older timestamps. If the query filters data using a "… WHERE time > ?..., P1=TS" condition then the old timestamps may not fulfill the query condition.
  
• 
  LST can be used to filter data read by previous scans. If a scan/query fails, LST is still updated and the next scan will exclude previous scan data. Recommendation for single tags is to use TS as placeholder.
  
• 
  Because LET is not updated, if a query fails (valid for single queries only) LET can be used to include data from a previous scan that failed. Data Loss can occur if data comes into the RDBMS table in real-time, mainly because data coming in during query execution time may be located before LET and not picked up by the next scan. Best use for LET scenarios is picking up data (e.g. LAB data) once a day. Timestamps will be located somewhere during the day but not around execution time.
  
• 
  If the connection between interface node and PI Server fails, output events will get lost during this time. The interface currently does not perform on-line recovery. If this data loss is an issue, run a separate instance of the interface in pure replication mode (recovery only mode). The interface will then not work on events but replicate the archive data.
  
• 
  TS placeholder is used for constraining data in distribution strategy. In this case data loss can happen because TS represents the query execution time (timestamp of distributor tag) and not the various current timestamps of the target tags. For distribution strategy OSIsoft recommends flagging data in the RDBMS that was already read or to delete this data if possible (use a multiple query file with a DELETE statement at the end, Example 3.8 – multi statement query).
  

For Users of Previous Interface Versions

Read Before Update

Version 3.0 of the RDBMSPI Interface is a major revision (as the version 2.0 was for version 1.x) and many enhancements have been made that did not fit into the design of the previous version. Be aware that version 3.x of the RDBMSPI interface:

• 
  Is not available for Windows NT
  
• 
  For some tasks, the interface requires PI SDK.
  
• 
  The /sr parameter to set the Sign-Up-For-Updates scan period has been removed.
  

Note: Since 3.11.0.0, there is the /UPDATEINTERVAL parameter that allows for setting the sign-up-for-update rate.

• 
  The /skip_time switch has been removed. See the /perf start up parameter description in the Startup Command File chapter.
  
• 
  The following minor changes may affect compatibility to a previous configuration:

  Location5=1 for String input tags – behavior has changed.. In previous versions (2.x) this setting caused the interface to only send changes to these tags. Now, the behavior is aligned with all other data types, which means no exception reporting is done for tags with Location5=1.

  
  

Upgrading the Interface from a Previous Version

For an upgrade of the RDBMS to PI Interface:

• 
  Make a backup of all interface files at PIPC/interfaces/RDBMSPI directory.
  For example:
  c:> md /PIPC/interfaces/RDBMSPI/RDBMSPI_old
  c:> copy /PIPC/interfaces/RDBMSPI/*.* /PIPC/interfaces/RDBMSPI/RDBMSPI_old/*.*
  
• 
  If the interface was installed as a Windows service, remove the service using
  c:> rdbmspi.exe remove.
  
• 
  Remove the interface with "Add/Remove Programs" on the Control Panel or just delete the interface files if the interface was not installed with a Setup Kit.
  
• 
  If not already installed, update the PI API to the current release of PI SDK (includes latest PI API as well).
  

CAUTION! Users of PI API 1.3.8 should configure a trust/proxy for the interface. The reason is an issue in the PI API that causes the interface not to regain its user credentials after an automatic reconnection to the PI Server executed by PI API. Without having a trust/proxy configured data may get lost. A -10401 error may occur in the PI Server log.

CAUTION! Since RDBMSPI version 3.14 (and UniInt 4.1.2), the interface does NOT explicitly log in to PI anymore. Users always have to configure the trust entry for this interface (in the trust table on the PI Server). Delete the *.PI_PWD file (if there is one in the directory where the /output= parameter points) and remove the /user_pi= and /pass_pi= from the interface start-up file.

CAUTION! RDBMSPI version 3.15 must explicitly set the start-up parameter /pisdk=1 in case the interface is supposed to read and write to (or read from) PI Annotations or will replicate the PI Batch Database. The default value for the /pisdk parameter is 0.

CAUTION! RDBMSPI version 3.16 re-implemented the crypt algorithm for storing the password for the ODBC database. The new password file (a file which stores the password for the database) is still placed in the same directory where the interface specific log-file resides, but its name is different. The new name is composed of the following: interface_name_ps_id.PWD
Where the interface_name is the name of the executable file, ps is the specified PointSource and id is the # of the interface instance.

CAUTION! RDBMSPI version 3.16 stores events with annotations will be forwarded to PI with pure PI SDK call. This has two important side-effects:
- annotated events will not support exception reporting
- when the interface runs against High Availability PI Servers, the annotated events will only be sent to the primary server.

CAUTION! RDBMSPI version 3.18.1 changed the implementation of the /recovery_time start-up parameter when combined with another start-up - /utc. If the /utc is set, the specified recovery time is NOT transformed to UTC and is interpreted as local time.

Now proceed with running the set-up program as described in section Interface Installation.

Perform all configuration steps and, optionally, use existing configuration files from the backup.