Relational Database
(RDBMS via ODBC)
Interface to the PI System
Version 3.0

1

How to Contact Us

Phone / (510) 297-5800 (main number)
(510) 297-5828 (technical support)
Fax / (510) 357-8136
Internet /
World Wide Web /
Bulletin Board / (510) 895-9423
Telebit WorldBlazer modem (Hayes, MNP, or PEP compatible)
8 data bits, 1 stop bit, no parity, up to 14400 bps download
protocols: Xmodem, Ymodem, Zmodem, Kermit
Mail / OSI Software, Inc.
P.O. Box 727
San Leandro, CA 94577-0427
USA
OSI Software GmbH
Hauptstrae 30
D-63674 Altenstadt 1
Deutschland /
OSI Software, Ltd
P. O. Box 8256
Level One, 6-8 Nugent Street
Auckland 3, New Zealand

Unpublished -- rights reserved under the copyright laws of the United States.
RESTRICTED RIGHTS LEGEND
Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii)
of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013

Trademark statement—PI is a registered trademark of OSI Software, Inc. Microsoft Windows, Microsoft Windows for Workgroups, and Microsoft NT are registered trademarks of Microsoft Corporation. Solaris is a registered trademark of Sun Microsystems. HPUX is a registered trademark of Hewlett Packard Corp.. IBM AIX RS/6000 is a registered trademark of the IBM Corporation. DUX, DEC VAX and DEC Alpha are registered trademarks of the Digital Equipment Corporation.
PI_rdbmspi.doc

 2001 - 2002 OSI Software, Inc. All rights reserved
777 Davis Street, Suite 250, San Leandro, CA 9457

1

Table of Contents

Introduction......

Reference Manuals......

Supported Features......

Configuration Diagram......

Principles of Operation

Concept of Data Input from Relational Database to PI......

Concept of Data Output from PI to Relational Database......

SQL Statements

Prepared Execution......

Direct Execution......

Language Requirements......

SQL Placeholders......

Timestamp Format......

Interface Node Clock......

Time Synchronization with PI Server......

Inputs to PI via SELECT Clause......

Data Acquisition Strategies......

SQL SELECT Statement for Single PI Tag

SQL SELECT Statement for Tag Groups

SQL SELECT Statement for Tag Distribution

Event-based Input......

Multi-statement SQL Clause......

Explicit Transactions......

Stored Procedures......

Mapping of Value and Status – Data Input......

Output from PI......

Mapping of Value and Status – Data Output......

Global Variables......

Recording of PI POINT Database Changes

Short Form Configuration......

Long Form Configuration......

PI Batch Database Output

PI Batch Database Replication without Module Database......

PI Batch Database Replication with Module Database......

PI Batch Database Replication Details......

Automatic Re-connection

ODBC Connection Loss......

PI Connection Loss......

Specific Database Considerations

Oracle 7.0; Oracle 8.0; Oracle RDB......

dBase III, dBase IV......

MS Access......

MS SQL Server 6.5, 7.0, 2000......

CA Ingres II......

IBM DB2 (NT)......

Informix (NT)......

Sybase ASE 12.0 (NT)......

Paradox......

More Examples......

Insert or Update......

PI Point Configuration

Tag......

Extended Descriptor......

Point Source......

Point Type......

Scan......

InstrumentTag......

SourceTag......

Location1......

Location2......

Location3......

Location4......

Location5......

Shutdown......

Time Zone and Daylight Savings......

Startup Command File......

PI-Interface Configuration Utility on NT......

PI-ICU RDBODBC Control on NT......

Summary of Command-line Parameters......

Detailed Description of the Command-line Parameters......

Sample RDBMSPI.bat File......

Security......

Performance Point Configuration......

I/O Rate Tag Configuration......

For Users of Previous Interface Versions

Read Before Update......

Updating the Interface from a Previous Version......

Interface Installation......

Naming Conventions and Requirements......

Microsoft DLLs......

Interface Directories......

The PIHOME Directory Tree......

Interface Installation Directory......

Interface Installation Procedure......

Installing the Interface as an NT Service......

Buffering......

PILOGIN.INI......

Shutdown

Control Program......

CPPI Text Commands......

CPPI/RDBMSPI Functionality Accessed via MMC......

Appendix A: Error and Informational Messages......

Appendix B: Hints for PI System Manager

Appendix C: Interface Test Environment

Version 1.28......

Version 2.08......

Version 3.0.8......

Revision History......

Relational Database (RDBMS via ODBC) Interface to the PI System1

Introduction

The Interface allows bi-directional transfer of data between the PI System and any relational database management system (RDBMS) that supports Open Database Connectivity (ODBC) drivers. The Interface runs on Microsoft Windows (NT/2000) operating systems, and is able to connect to any PI Server node available in the network. This version only supports one ODBC connection per running copy but multiple Interface instances are possible.

SQL statements are generated by the end user either in the form of ordinary ASCII files, or are defined in the Extended Descriptor of a PI tag. These SQL statements are the source of data for one or more tags – data input, and similarly, PI tags provide values for RDB – data output.

The Interface makes internal use of the PI-API-NT and PI-SDK in order to keep a standard way of interfacing from a client node to the PI Server Node.

Note:Databases and ODBC drivers not yet tested with the Interface may require additional onsite testing, which will translate to additional charges. Please refer to the section entitledAppendix C: Interface TestEnvironmentfor a list of databases and ODBC drivers that the Interface is known to work with. Even if your database and/or ODBC driver is not shown, the Interface still may work. However, if problems are experienced, the Interface will have to be enhanced to support your environment. Please contact your OSI sales representative.

Reference Manuals

OSIsoft
  • UniInt End User Document
  • PI Data Archive Manual
  • PI-API,PI-SDK Installation Instructions
Vendor
  • Vendor specific ODBC Driver Manual
  • Microsoft ODBC Programmer’s Reference

Supported Features

Feature / Support
Part Number / PI-IN-OS-RELDB-NTI
Platforms / Windows NT 4 or higher (Intel)
PI Point Types / Float16 / Float32 / Float64 / Int16 / Int32 / Digital / String
Sub-Second Timestamps / Yes
Sub-Second Scan Classes / Yes
Automatically Incorporates PI Point Attribute Changes / Yes
Exception Reporting / Yes
PI-API Node Support / Yes
Uses PI-SDK / Yes
Inputs to PI / Scan-based / Unsolicited / Event Tags
Outputs from PI / Event-based
Text Transfer / Yes
Configuration Data / Output
Maximum Point Count / Unlimited
* Source of Timestamps / PI Server or RDBMS
* History Recovery / Yes
Failover / No
* UniInt-Based / Yes
* Vendor Software Required / Yes
Vendor Hardware Required / No
* Additional PI Software Included with Interface / Yes
Device Point Types / N/A

* See below for more information.

Source of Timestamps

The Interface can accept timestamps from the RDBMS or it can provide PI server synchronized timestamps.

History Recovery

For output tags the Interface goes back in time (optionally a time interval can be specified via /recovery_timeparameter), and uses values stored in the PI Archive. See the section /recovery later on.
This option is only available at interface startup (or for a single tag after tag edit) but not for RDBMS connection problems. These are covered by the re-connection mechanism (see Automatic Re-connection).
For input tags history recovery depends on the WHERE condition of a SELECT query. See section Inputs to PI via SELECT Clausefor more details.

UniInt-Based

UniInt stands for Universal Interface. UniInt is not a separate product or file; it is an OSIsoft-developed template used by our developers, and is integrated into many interfaces, such as the RDBMSPI Interface. The purpose of UniInt is to keep a consistent feature set and behavior across as many of our interfaces as possible. It also allows for the very rapid development of new interfaces. In any UniInt-based interface, the interface uses some of the UniIntsupplied configuration parameters and some interface-specific parameters. UniInt is constantly being upgraded with new options and features.

The UniInt End User Document is a supplement to this manual.

Vendor Software Required

The ODBC Driver Manager comes with Microsoft Data Access Components (MDAC). It is recommended to use the latest MDAC available at

The particular, RDBMS specific ODBC driver must be installed, and configured on the Interface node.

Additional PI Software Included with Interface

The Control Program (CPPI) is a tool that assists in troubleshooting the Interface. For more details see the section Control Program.

Device Point Types

For full description of the ODBC supported data types see the ODBC Programmer’s Reference available on . The Interface does some internal consideration in terms of mapping the RDBMS data types to PI data types and vice versa. For more info on this topic see section Mapping of SQL (ODBC) Data Types to PI Point Types – Data Inputand Mapping of Value and Status – Data Output.

Configuration Diagram

In the following picture there is the basic configuration of the hardware and software components in a typical scenario used with the RDBMSPI Interface installation:

Relational Database (RDBMS via ODBC) Interface to the PI System1

Principles of Operation

  • Interface runs on Windows NT operation system as a console application or as a NT Service. It uses the extended PI-API-NT and PI-SDK to connect to the PI Server node, and the relational database connection is made via the corresponding ODBC driver. The Data Source Name (DSN) is created by the ODBC Administrator (Data Sources ODBC icon in Control Panel), and this DSN name is passed in the start-up arguments of the Interface (e.g. /DSN=Oracle8).
  • SQL queries are provided by the user in form of either ASCII files, or via a direct definition in the Extended Descriptor. Queries are executed according to the scan class type (cyclic or event driven) set by a PI point holding the query definition.
  • When data is read from the relational database, the Interface tries to convert the result- set of a SELECT query into the PI concept of [timestamp], value, status and sends these values to the PI Snapshot. The opposite direction - writing data out of the PI system – storing data from PI to RDBMS makes use of runtime placeholders (described later in this Manual).
Current Version of the Interface Supports Following General Features

Query Timestamp, Value, Status in RDBMS Tables (including Strings)

Support of String tags and millisecond timestamps

Query data (read) for single tag

Query data (read) for multiple tags (Tag Group

Query data (read) via TagName Key (Tag Distribution

Scan or Event based SELECT queries

Event based UPDATE, DELETE and INSERT queries

Support of multiple statements per query

Statements in SQL file can be one single transaction

Support of stored procedures

Support of ‘runtime placeholders’ Timestamp (Scan Time, Snapshot Time,...), Value, Status

Support of all classic ‘point attribute’ placeholders

Support of placeholders for Value, Status, Timestamp of a ‘Foreign Tag’ - a tag outside the Interface point source

Support of ‘batch’ placeholders for Batch replication

Support for new batch system (batches and unit batches)

Storage of point attribute changes (all point types) in RDBMS

Recovery option for output points

Interface can run in a different Timezone/DST setting than PI Server

RDBMS timestamps can optionally be in UTC independent on interface Timezone/DST setting

Concept of Data Input from Relational Database to PI

The SELECT query provides the [timestamp], value, status for a PI tag. There can be multiple statements per one tag, but only one individual SELECT is allowed in such a batch. The Interface internally transforms the result-set according to the selected distribution strategy. See SQL SELECT Statement for Tag GroupsandSQL SELECT Statement for Tag Distribution. These configurations reduce the number of ODBC calls and thereby increase performance.

Query for Single Tag – One Value per Scan

There are DCS systems that keep current values in relational database tables. Via the scan-based SELECT queries the Interface can read the data in the timely manner, and emulate the behavior of a standard DCS interface. An example is getting data from an ABB IMS station.

More detailed description - see section SQL SELECT Statement for Single PI Tag

The disadvantage of this kind of data retrieval is low performance and accuracy that is limited to scan frequency.

Example 1.1 – Single Tag Query
SQL Statement
(file PI_REAL1.SQL)
SELECT PI_TIMESTAMP, PI_VALUE, PI_STATUS FROM PI_REAL1 WHERE PI_KEY_VALUE = ?;
Relevant PI Point Attributes
Extended Descriptor / Location1 / Location2 / Location3 / Location4 / Location5
P1=”Key_1234” / 1 / 0 / 0 / 1 / 0
Instrumenttag / Pointtype / Pointsource
PI_REAL1.SQL / Float32 / S
RDBMS Table Design
PI_TIMESTAMP / PI_VALUE / PI_STATUS / PI_KEY_VALUE
Datetime
(MS SQL Server)
Date/Time
(MS Access) / Real
(MS SQL Server)
Number-Single Precision
(MS Access) / Smallint
(MS SQL Server)
Number-Whole Number
(MS Access) / Varchar(50)
(MS SQL Server)
Text(50)
(MS Access)
Query for Single Tag – Multiple Values per Scan

A good strategy for the high data throughput is to have low scanning rates (e.g. 1 minute) instead of doing one query every second. In other words getting the same amount of data in one call is faster than getting it in many calls. This assumes that we are not scanning updated records (UPDATE statement overwrites existing rows), but we scan a table that is populated by an INSERT. A typical high throughput query is given below. In this example we get all data since ‘Snapshot’ time.

Note: Supported SQL syntax and parameter description (Pn) is given later in the manual.

Example 1.2 – Query Data Array for a Single Tag
SQL Statement
(file PI_STRING1.SQL)
SELECT PI_TIMESTAMP, PI_VALUE, 0 FROM PI_STRING1 WHERE PI_TIMESTAMP > ?
ORDER BY PI_TIMESTAMP ASC;
Relevant PI Point Attributes
Extended Descriptor / Location1 / Location2 / Location3 / Location4 / Location5
P1=TS / 1 / 1 / 0 / 1 / 0
Instrumenttag / Pointtype / Pintsource
PI_STRING1.SQL / String / S
RDBMS Table Design
PI_TIMESTAMP / PI_VALUE
Datetime
(MS SQL Server)
Date/Time
(MS Access) / Varchar(1000) (MS SQL Server)
Text(255) (MS Access)

Note: A typical low throughput query is:

SELECT Timestamp, Value, Status FROM Table WHERE Name= ?;

Extended Descriptor: P1=AT.TAG, Location2:0

Andthe interface only gets one row (first row in the returned result-set).
The Interface works similarly to an online DCS Interface.

Note: The STATUS column, which is mandatory, is represented by the constant expression ‘0’. See SQL SELECT Statement for Single PI Tag.

Tag Groups

Another way of improving performance (compared to reading value(s) for a single tag) is grouping tags together. The RDBMS table should be structured in a way that multiple values are stored in the same record (in more columns), e.g. transferring LAB data, where one data sample is stored in the same row. Querying Tag Groups can also be combined with getting complete time series per scan (Location2 >0). Only onetimestampis allowed in a resultset, and is used for timestamping of all tags in a group.

Note: The group is created out of points that have the same InstrumentTag attribute => Group member Tags share the same ASCII SQL file.

More detailed description -see section SQL SELECT Statement for Tag Groups.

Example 1.3 – Three PI Points Forming a GROUP
SQL Statement
(file PI_INT_GROUP1.SQL)
SELECT PI_TIMESTAMP, PI_VALUE1, 0 ,PI_VALUE2, 0, PI_VALUE3, 0 FROM PI_INT_GROUP1 WHERE PI_TIMESTAMP > ? ORDER BY PI_TIMESTAMP ASC;
Relevant PI Point Attributes
Extended Descriptor / Location1
All points / Location2
All points / Location3 / Location4
All points / Location5
All points
P1=TS / 1 / 1 / Target_Point1 2
Target_Point2 4
Target_Point3 6 / 1 / 0
Instrumenttag
All Points / Pointtype / Pointsource
All Points
PI_INT_
GROUP1.SQL / Int32 / S
RDB Table Design
PI_TIMESTAMP / PI_VALUEn
Datetime
(MS SQL Server)
Date/Time
(MS Access) / Smallint (MS SQL Server)
Number (Whole Number) (MS Access)
Example of Appropriate Result Set

PI_TIMESTAMPPI_VALUE1PI_VALUE2PI_VALUE3

20-Oct-200008:10:00102030
20-Oct-200008:20:00112131
20-Oct-200008:30:00122232
…

Target_Point1 gets 10, 11, 12, …
Target_Point2 gets 20, 21, 22, …
Target_Point3 gets 30, 31, 32, …

Tag Distribution

Compared to Tag Groups where ‘grouping’ happens in form of multiple value, status columns in a result-set, Tag Distribution means multiple records per query. Each record may contain data for a different tag. To achieve this, an additional field must be provided - containing the tag name, or the alias.

More detailed description - see section SQL SELECT Statement for Tag Distribution.

This option is very efficient for getting exception-based data where it is unknown how many data rows will arrive per single tag. It is only known that there will typically be an average number of data records per scan.

The ‘Distributor’ point defines the SQL statement. This point does not receive any actual (selected) data from the result set. Instead, it gets the number of all rows successfully delivered to ‘target’ points (points mapped by the additional column in a result-set). Such information is useful for administration purposes. Target points are selected either according to Tagname (value retrieved in PI_NAME column should match the Tagname of the point) or according to /ALIAS=alias_key definition found in the Extended Descriptor of the particular point.

Note: It is required the ‘Distributor Point’ to be numeric.

Example 1.4 – Distributor Strategy for PI Points
SQL Statement
(file PI_REAL_DISTR1.SQL)
SELECT PI_TIMESTAMP, PI_NAME, PI_VALUE, PI_STATUS FROM T1_4 WHERE PI_NAME LIKE ‘Tag_%’AND PI_TIMESTAMP > ? ;
Relevant PI Point Attributes
Extended Descriptor
Distributor / Location1
All points / Location2
All points / Location3 / Location4
All points / Location5
All points
P1=TS / 1 / 0 / ‘Distributor’ -1
‘Target points’ 0 / 1 / 0
Instrumenttag
Distributor / Pointtype
Distributor / Pointsource
All Points
PI_REAL_DISTR1.SQL / Float32 / S
RDB Table Design
PI_TIMESTAMP / PI_VALUE / PI_STATUS / PI_NAME
Datetime
(MS SQL Server)
Date/Time
(MS Access) / Real (MS SQL Server)
Number (Single) Prec.(MS Access) / Varchar(12) (MS SQL Server)
Text(12) (MS Access) / Varchar(80) (MS SQL Server)
Text(80) (MS Access)
Example of Appropriate Result Set

PI_TIMESTAMPPI_NAMEPI_VALUEPI_STATUS

20-Oct-200008:10:00Tag_110NULL 20-Oct-200008:10:00 Tag_2 20 NULL 20-Oct-200008:10:00 Tag_3 30 NULL …

10 goes to Tag_1; 20 to Tag_2; 30 to Tag_3 …

Concept of Data Output from PI to Relational Database

Transferring data from PI to a relational database works similarly to the RDBMS reading. Relational database can receive Snapshot values of any PI point as well as any value of a PI point attribute addressable by placeholders; see sectionSQL Placeholders.
For copying new data to a relational database, standard event based output points are used. In this case the Sourcetag should be provided, and the output point itself gets the copy of the exported data to verify the output operation. If the output operation reports a failure (ODBC SQLExecute() function fails), the output point gets the status “Bad Output”.

Note: Writing data to RDBMS is normally configured via the PI output tags - event based output. Nevertheless input points can also be used to write data to RDBMS on a periodical basis: i.e. they execute e.g. INSERT statement instead of an ‘ordinary’ SELECT.