Relational Database(rdbms via odbc) Interface

Yüklə 1,66 Mb.
ölçüsü1,66 Mb.
  1   2   3   4   5   6   7   8   9   ...   50

Relational Database(RDBMS via ODBC) Interface

Version 3.19.1.x-3.19.2.x

OSIsoft, LLC

777 Davis St., Suite 250

San Leandro, CA 94577 USA

Tel: (01) 510-297-5800

Fax: (01) 510-357-8136


OSIsoft Australia • Perth, Australia

OSIsoft Europe GmbH • Frankfurt, Germany

OSIsoft Asia Pte Ltd. • Singapore

OSIsoft Canada ULC • Montreal & Calgary, Canada

OSIsoft, LLC Representative Office • Shanghai, People’s Republic of China

OSIsoft Japan KK • Tokyo, Japan

OSIsoft Mexico S. De R.L. De C.V. • Mexico City, Mexico

OSIsoft do Brasil Sistemas Ltda. • Sao Paulo, Brazil

Relational Database(RDBMS via ODBC) Interface

Copyright: © 2006-2018 OSIsoft, LLC. All rights reserved.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, mechanical, photocopying, recording, or otherwise, without the prior written permission of OSIsoft, LLC.

OSIsoft, the OSIsoft logo and logotype, PI Analytics, PI ProcessBook, PI DataLink, ProcessPoint, PI Asset Framework(PI-AF), IT Monitor, MCN Health Monitor, PI System, PI ActiveView, PI ACE, PI AlarmView, PI BatchView, PI Data Services, PI Manual Logger, PI ProfileView, PI WebParts, ProTRAQ, RLINK, RtAnalytics, RtBaseline, RtPortal, RtPM, RtReports and RtWebParts are all trademarks of OSIsoft, LLC. All other trademarks or trade names used herein are the property of their respective owners.


Use, duplication or disclosure by the U.S. Government is subject to restrictions set forth in the OSIsoft, LLC license agreement and as provided in DFARS 227.7202, DFARS 252.227-7013, FAR 12.212, FAR 52.227, as applicable. OSIsoft, LLC.

Published: 08/2011

Table of Contents

Terminology ix

Chapter 1. Introduction 1

Reference Manuals 2

Supported Features 2

Configuration Diagram 7

Chapter 2. Principles of Operation 9

Concept of Data Input from Relational Database to PI 10

Query for Single Tag – One Value per Scan 10

Query for Single Tag – Multiple Values per Scan 10

Tag Groups 11

Tag Distribution 12

RxC Distribution (combination of Group and Distribution) 12

Concept of Data Output from PI to Relational Database 13

Use of PI SDK 14

Chapter 3. Installation Checklist 15

Data Collection Steps 15

Interface Diagnostics 17

Advanced Interface Features 17

Chapter 4. Interface Installation 19

Naming Conventions and Requirements 19

Interface Directories 20

PIHOME Directory Tree 20

Interface Installation Directory 20

Interface Installation Procedure 20

Installing Interface as a Windows Service 20

Installing Interface Service with PI Interface Configuration Utility 21

Service Configuration 21

Installing Interface Service Manually 24

What is Meant by "Running an ODBC Application as Windows Service"? 25

Chapter 5. Digital States 27

Chapter 6. PointSource 29

Chapter 7. PI Point Configuration 31

Point Attributes 31

Tag 31

PointSource 32

PointType 32

Location1 32

Location2 32

Location3 33

Location4 33

Location5 34

InstrumentTag 35

ExDesc 35

Scan 38

Shutdown 39

Source Tag 39

Unused Attributes 40

Chapter 8. SQL Statements 41

Prepared Execution 42

Direct Execution 42

Language Requirements, ODBC API Conformance 42

SQL Placeholders 43

Timestamp Format 49

Inputs to PI via SELECT Clause – Detailed Description 53

NULL Columns 53

Bulk Data Input 54

Data Acquisition Strategies 54

SQL SELECT Statement for Single PI Tag 54

SQL SELECT Statement for Tag Groups 56

SQL SELECT Statement for Tag Distribution 57

Option 1: Fixed Position of Fields in SELECT Statement 57

Option 2: Arbitrary Position of Fields in SELECT Statement – Aliases 59

SQL SELECT Statement for RxC Distribution 60

Detailed Description of Information the Distributor Tags Store 61

Event based Input 61

Mapping of Value and Status – Data Input 63

Mapping of SQL (ODBC) Data Types to PI Point Types – Data Input 63

Output from PI 67

Global Variables 69

Chapter 9. Recording PI Point Database Changes 71

Short Form Configuration 71

Long Form Configuration 72

Chapter 10. PI Batch Database Output 73

PI Batch Database Replication without Module Database 73

PI Batch Database Replication with Module Database 74

PI Batch Database Replication Details 75

Chapter 11. RDBMSPI – Input Recovery Modes 77

Chapter 12. RDBMSPI – Output Recovery Modes (Only Applicable to Output Points) 81

Recovery TS 81

Out-Of-Order Recovery 81

Out-Of-Order Handling in On-Line Mode (RDBMSPI Interface Runs) 83

Recovery SHUTDOWN 85

Interface in Pure Replication Mode 85

Input Recovery 85

Output Recovery 85

Chapter 13. Automatic Reconnection 87

ODBC Connection Loss 87

PI Connection Loss 88

Chapter 14. Result Variables 89

Send Data to PI 89

Result of ODBC Query Execution 90

Chapter 15. RDBMSPI – Redundancy Considerations 91

Chapter 16. RDBMSPI and Server-Level Failover 93

Chapter 17. Startup Command File 95

Configuring the Interface with PI ICU 95

RDBODBC Interface page 98

Command-line Parameters 108

Sample RDBMSPI.bat File 124

Chapter 18. UniInt Failover Configuration 125

Introduction 125

Synchronization through a Shared File (Phase 2) 127

Configuring Synchronization through a Shared File (Phase 2) 128

Configuring UniInt Failover through a Shared File (Phase 2) 131

Start-Up Parameters 131

Failover Control Points 133

PI Tags 134

Detailed Explanation of Synchronization through a Shared File (Phase 2) 138

Steady State Operation 139

Failover Configuration Using PI ICU 141

Create the Interface Instance with PI ICU 141

Configuring the UniInt Failover Startup Parameters with PI ICU 142

Creating the Failover State Digital State Set 142

Using the PI ICU Utility to create Digital State Set 143

Using the PI SMT 3 Utility to create Digital State Set 143

Creating the UniInt Failover Control and Failover State Tags (Phase 2) 146

Chapter 19. Database Specifics 147

Oracle 7.0; Oracle 8.x, 9i, 10g, 11g; Oracle RDB 147

Open Statements Limitation 147

TOP 10 148

How to Construct Stored Procedure that Returns Result-Set: 148

dBase III, dBase IV 149

Date and Time Data Type 149

Login 149

Multi-User Access 149

Microsoft Access 149

Login 149

Slowdown in statement preparation for more than 50 tags 150

Microsoft SQL Server 6.5, 7.0, 2000, 2005, 2008 150

DATETIME Data Type 150

TOP 10 150


CA Ingres II 151

Software Development Kit 151

IBM DB2 (NT) 151

Statement Limitation 151

Informix (NT) 152

Error while ODBC Re-Connection 152

Paradox 152

Error when ALIASES used in WHERE Clause 152

Chapter 20. Interface Node Clock 153

Time Synchronization with PI Server 154

Time Zone and Daylight Saving 154

Chapter 21. Security 155

Windows 155

Chapter 22. Starting / Stopping the Interface 157

Starting Interface as a Service 157

Stopping Interface Running as a Service 157

Chapter 23. Buffering 159

Which Buffering Application to Use 159

How Buffering Works 160

Buffering and PI Server Security 161

Enabling Buffering on an Interface Node with the ICU 161

Choose Buffer Type 162

Buffering Settings 162

Buffered Servers 165

Installing Buffering as a Service 168

Chapter 24. Interface Diagnostics Configuration 171

Scan Class Performance Points 171

Performance Counters Points 174

Performance Counters 176

Performance Counters for both (_Total) and (Scan Class x) 176

Performance Counters for (_Total) only 177

Performance Counters for (Scan Class x) only 180

Interface Health Monitoring Points 181

I/O Rate Point 186

Interface Status Point 188

Error and Informational Messages 191

Interface-specific Output File 192

Messages 192

System Errors and PI Errors 192

UniInt Failover Specific Error Messages 192

Informational 192

Errors (Phase 1 & 2) 194

Errors (Phase 2) 195

PI SDK Options 197

Examples 199

Example 1.1 – single tag query 199

Example 1.2 – query data array for a single tag 200

Example 1.3 – three PI points forming a GROUP 201

Example 1.4 – Tag Distribution 202

Example 1.5 – RxC Distribution 203

Example 1.6 – Single Input with PI Annotations 204

Example 2.1a – insert sinusoid values into table (event based) 205

Example 2.1b – insert sinusoid values into table (scan based) 206

Example 2.1c – insert 2 different sinusoid values into table (event based) 207

Example 2.1d – insert sinusoid values with (string) annotations into RDB table (event based) 208

Example 3.1 – Field Name Aliases 209

Example 3.2 – Tag Group, Fixed Column Positions 210

Example 3.3 – Tag Group, Arbitrary Column Position – Aliases 211

Example 3.4a – Tag Distribution, Search According to Real Tag Name 212

Example 3.4b – Tag Distribution, Search According to Tag's ALIAS Name 213

Example 3.4c – Tag Distribution with Auxiliary Column – rowRead 214

Example 3.4d – Tag Distribution with Auxiliary Table Keeping Latest Snapshot 215

Example 3.4e – Tag Distribution in Combination with /RBO and 'Time-Window' 216

Example 3.5 – Tag Distribution with Aliases in Column Names 217

Example 3.6 – RxC Distribution 218

Example 3.6b – RxC Distribution Using PI_TIMESTAMP Keyword 218

Example 3.7 – Event Based Input 219

Example 3.8 – Multi Statement Query 220

Example 3.9 – Stored Procedure Call 221

Example 3.10 – Event Based Output 222

Example 3.11 – Output Triggered by 'Sinusoid', Values Taken from 'TagDig' 223

Example 3.12 – Global Variables 224

Example 4.1 – PI Point Database Changes – Short Form Configuration 225

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

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

Example 5.2a – Batch Export (Module Database required) 228

Example 5.2b – UnitBatch Export (Module Database required) 229

Example 5.2c – SubBatch Export (Module Database required) 230

Example 6.1 – Last One Hour of 'Sinusoid' 231

Hints and Checklist 233

Hints for the PI System Manager 233


Reconnect to RDBMS 233

Suppress I/O Timeout 233

Field Size (1) 233

Uppercase for Constant String 234

Repeated Error Messages 234

Field Size (2) 234

No Data 234

Login to PI 234

Checklist and Trouble-Shooting 234

No Data (Input) 234

Data Loss 235

For Users of Previous Interface Versions 237

Read Before Update 237

Upgrading the Interface from a Previous Version 237

Interface Test Environment 241

Interface Version 1.28 241

Interface Version 2.x 241

Interface Version 3.x 242

Tested RDBMSs 243

Technical Support and Resources 245

Before You Call or Write for Help 245

Help Desk and Telephone Support 245

Search Support 246

Email-based Technical Support 246

Online Technical Support 246

Remote Access 247

On-site Service 247

Knowledge Center 247

Upgrades 247

OSIsoft Virtual Campus (vCampus) 247

Revision History 249


To understand this interface manual, you should be familiar with the terminology used in this document.


Buffering refers to an Interface Node’s ability to store temporarily the data that interfaces collect and to forward these data to the appropriate PI Servers.
N-Way Buffering

If you have PI Servers that are part of a PI Collective, PIBufss supports n-way buffering. N way buffering refers to the ability of a buffering application to send the same data to each of the PI Servers in a PI Collective. (Bufserv also supports n-way buffering to multiple PI Servers however it does not guarantee identical archive records since point compressions attributes could be different between PI Servers. With this in mind, OSIsoft recommends that you run PIBufss instead.)

ICU refers to the PI Interface Configuration Utility. The ICU is the primary application that you use to configure PI interface programs. You must install the ICU on the same computer on which an interface runs. A single copy of the ICU manages all of the interfaces on a particular computer.

You can configure an interface by editing a startup command file. However, OSIsoft discourages this approach. Instead, OSIsoft strongly recommends that you use the ICU for interface management tasks.

ICU Control

An ICU Control is a plug-in to the ICU. Whereas the ICU handles functionality common to all interfaces, an ICU Control implements interface-specific behavior. Most PI interfaces have an associated ICU Control.
Interface Node

An Interface Node is a computer on which

  • the PI API and/or PI SDK are installed, and

  • PI Server programs are not installed.

The PI API is a library of functions that allow applications to communicate and exchange data with the PI Server. All PI interfaces use the PI API.
PI Collective

A PI Collective is two or more replicated PI Servers that collect data concurrently. Collectives are part of the High Availability environment. When the primary PI Server in a collective becomes unavailable, a secondary collective member node seamlessly continues to collect and provide data access to your PI clients.

PIHOME refers to the directory that is the common location for PI 32-bit client applications.

A typical PIHOME on a 32-bit operating system is C:\Program Files\PIPC.

A typical PIHOME on a 64-bit operating system is C:\Program Files (x86)\PIPC.

PI 32-bit interfaces reside in a subdirectory of the Interfaces directory under PIHOME.

For example, files for the 32-bit Modbus Ethernet Interface are in


This document uses [PIHOME] as an abbreviation for the complete PIHOME or PIHOME64 directory path. For example, ICU files in [PIHOME]\ICU.


PIHOME64 is found only on a 64-bit operating system and refers to the directory that is the common location for PI 64-bit client applications.

A typical PIHOME64 is C:\Program Files\PIPC.

PI 64-bit interfaces reside in a subdirectory of the Interfaces directory under PIHOME64.

For example, files for a 64-bit Modbus Ethernet Interface would be found in

C:\Program Files\PIPC\Interfaces\ModbusE.

This document uses [PIHOME] as an abbreviation for the complete PIHOME or PIHOME64 directory path. For example, ICU files in [PIHOME]\ICU.

PI Message Log

The PI message Log is the file to which OSIsoft interfaces based on UniInt 4.5.0.x and later writes informational, debug and error message. When a PI interface runs, it writes to the local PI message log. This message file can only be viewed using the PIGetMsg utility. See the UniInt Interface Message Logging.docx file for more information on how to access these messages.

The PI SDK is a library of functions that allow applications to communicate and exchange data with the PI Server. Some PI interfaces, in addition to using the PI API, require the use of the PI SDK.
PI Server Node

A PI Server Node is a computer on which PI Server programs are installed. The PI Server runs on the PI Server Node.

PI SMT refers to PI System Management Tools. PI SMT is the program that you use for configuring PI Servers. A single copy of PI SMT manages multiple PI Servers. PI SMT runs on either a PI Server Node or a PI Interface Node.

The pipc.log file is the file to which OSIsoft applications write informational and error messages. When a PI interface runs, it writes to the pipc.log file. The ICU allows easy access to the pipc.log.

The PI point is the basic building block for controlling data flow to and from the PI Server. For a given timestamp, a PI point holds a single value.

A PI point does not necessarily correspond to a “point” on the foreign device. For example, a single “point” on the foreign device can consist of a set point, a process value, an alarm limit, and a discrete value. These four pieces of information require four separate PI points.


A Service is a Windows program that runs without user interaction. A Service continues to run after you have logged off from Windows. It has the ability to start up when the computer itself starts up.

The ICU allows you to configure a PI interface to run as a Service.

Tag (Input Tag and Output Tag)

The tag attribute of a PI point is the name of the PI point. There is a one-to-one correspondence between the name of a point and the point itself. Because of this relationship, PI System documentation uses the terms “tag” and “point” interchangeably.

Interfaces read values from a device and write these values to an Input Tag. Interfaces use an Output Tag to write a value to the device.

  1. Yüklə 1,66 Mb.

    Dostları ilə paylaş:
  1   2   3   4   5   6   7   8   9   ...   50

Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur © 2022
rəhbərliyinə müraciət

    Ana səhifə