Relational Database(RDBMS via ODBC) Interface - OSIsoft

gasownerΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 6 μήνες)

626 εμφανίσεις



iii

Relational Database(RDBMS via
ODBC) Interface

Version

3.
20
.
6
.
x

Revision A



OSIsoft, LLC

777 Davis St., Suite 250

San Leandro, CA 94577 USA

Tel: (01) 510
-
297
-
5800

Fax: (01)
510
-
357
-
8136

Web:
http://www.osisoft.com


OSIsoft Australia • Perth, Australia
=
OSIsoft Europe GmbH • Frankfurt
Ⱐde牭any
=
OSIsoft Asia Pte Ltd. • Singapore
=
OSIsoft Canada ULC • Montreal & Calgary, Canada
=
l卉po晴Ⱐii䌠C
epresentative 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
-
2013

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 Da
taLink, 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 her
ein are the property of their respective owners.


U.S. GOVERNMENT RIGHTS

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

FAR 12.212, FAR 52.227, as applicable. OSIsoft, LLC.


Published:
03/2013




Relational Database(RDBMS via ODBC) Interface

iii

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


Time
-
Series per Scan

................................
........

10

Tag Groups

................................
................................
.............................

11

Tag Distribution

................................
................................
.......................

11

RxC Distribution (combination of Group

and Distribution)

......................

12

Concept of Data Output from PI to Relational Database

................................
..

12

Use of PI SDK

................................
................................
................................
...

14

Chapter 3.

Ins
tallation Checklist

................................
................................
................

15

Data Collection Steps

................................
................................
........................

15

Interface Diagnostics

................................
................................
.........................

17

Advanced Interface Features

................................
................................
............

17

Chapter 4.

Interface Installation

................................
................................
.................

19

Interface on PI Interface Nodes

................................
................................
........

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

Ch
apter 6.

PointSource

................................
................................
..............................

29

Chapter 7.

PI Point Configuration

................................
................................
..............

31

Point Attributes

................................
................................
................................
..

31

Tag

................................
................................
................................
..........

31

PointSource

................................
................................
............................

31

PointType

................................
................................
................................

32

Table of Contents

iv


Location1

................................
................................
................................

32

Location2

................................
................................
................................

32

Location3

................................
................................
................................

33

Location4

................................
................................
................................

33

Location5

................................
................................
................................

34

InstrumentTag

................................
................................
.........................

35

ExDesc (ExtendedDescriptor)

................................
................................

35

Scan

................................
................................
................................
........

38

Shutdown

................................
................................
................................

39

Source Tag

................................
................................
................................
........

39

Unused Attributes

................................
................................
..............................

40

Chapter 8.

SQL Statements

................................
................................
........................

41

Prepared Execution

................................
................................
...........................

42

Direct Execution

................................
................................
................................

42

Language Req
uirements, 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


Time
-
Series

................................
................................

54

Data Acquisition Strategies

................................
................................
...............

54

SQL SELECT Statement for Single PI Tag

................................
.......................

54

SQL SELECT Statement for Tag Groups

................................
.........................

55

SQL SELECT Statement for Tag Distribution

................................
...................

56

Option 2: Arbitrary Position of Fields in SELECT Statement


Aliases

..

59

SQL SELECT Statement for RxC Distribution

................................
..................

59

Event based Input

................................
................................
.............................

60

Mapping of Value and Status


Data Input

................................
.......................

62

Mapping of SQL (ODBC) Data Types to PI Point Types


Data Input

...

62

Output from PI

................................
................................
.........................

66

Global Variables

................................
................................
......................

68

Chapter 9.

R
ecording PI Point Database Changes

................................
...................

69

Short Form Configuration

................................
................................
..................

69

Long Form Configuration

................................
................................
..................

70

Chapter 10.

PI Batch Database Output

................................
................................
....

71

PI Batch Database Replication without Module Database (old batches)

..........

71

PI Batch Database Replication with Module Database (new batches)

.............

72

PI Batch Database Replication Details

................................
.............................

73

Chapter 11.

RDBMSPI


Input Recovery Modes

................................
.....................

75

Chapter 12.

RDBMSPI


Output Recovery Modes (Only Applicable to Output
Points)

79

Recovery TS

................................
................................
................................
......

79

Out
-
Of
-
Order Recovery

................................
................................
..........

79

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

...

81

Recovery SHUTDOWN

................................
................................
.....................

83

Interface in Pure Replication Mode

................................
................................
...

83


Relational
Database(RDBMS via ODBC) Interface

v

Input Recovery

................................
................................
........................

83

Output Recovery

................................
................................
.....................

83

Chapter 13.

Automatic Reconnection

................................
................................
.....

85

ODBC Connection Loss

................................
................................
....................

85

PI Connection Loss

................................
................................
...........................

86

Chapter 14.

Result Variables
................................
................................
....................

87

Send Data to PI

................................
................................
................................
.

87

Result of ODBC Query Execution

................................
................................
.....

88

Chapter 15.

RDBMSPI


Redundancy Considerations

................................
...........

89

Chapter 16.

RDBMSPI and Server
-
Level Failover

................................
...................

91

Chapter 17.

Startup Command File

................................
................................
.........

93

Configuring the Interface with PI ICU

................................
................................

93

RDBODBC Interface page

................................
................................
......

96

Command
-
line Parameters

................................
................................
.............

105

Samp
le RDBMSPI.bat File

................................
................................
..............

121

Chapter 18.

UniInt Failover Configuration

................................
............................

123

Introduction

................................
................................
................................
......

123

Quick Overview

................................
................................
.....................

124

Synchronization through a Shared File (Phase 2)

................................
..........

125

Configuring Synchronization through a Shared File (Phase 2)

.......................

126

Configuring UniInt Failover through a Shared

File (Phase 2)

.........................

129

Start
-
Up Parameters

................................
................................
.............

129

Failover Control Points

................................
................................
.........

131

PI Tags

................................
................................
................................
..

132

Detailed Explanation of Synchronization through a Shared File (Phase

2)

....

136

Steady State Operation

................................
................................
........

137

Failover Configuration Using PI ICU

................................
...............................

139

Create the Interface Instance with PI ICU

................................
.......................

1
39

Configuring the UniInt Failover Startup Parameters with PI

ICU

....................

140

Creating the Failover State Digital St
ate Set

................................
..................

140

Using the PI ICU Utility to create Digital State Set

...............................

141

Using the PI SMT 3 Utility to create Digital State Set

...........................

141

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

........

144

Chapter 19.

Database Spe
cifics

................................
................................
.............

145

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

................................
...........

145

Open Statements Limitation

................................
................................
.

145

TOP 10

................................
................................
................................
..

146

How to Construct Stored Procedure that Returns Result
-
Set:

.............

146

dBase III, dBase IV

................................
................................
..........................

147

Date and Time Data Type

................................
................................
.....

147

Login

................................
................................
................................
.....

147

Multi
-
User Access

................................
................................
.................

147

Microsoft Access

................................
................................
.............................

147

Table of Contents

vi


Login

................................
................................
................................
.....

147

Slowdown in statement preparation for more than 50 tags

..................

148

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

................................
...........

148

DATETIME Data Type

................................
................................
..........

148

TOP 10

................................
................................
................................
..

148

SET NOCOUNT ON

................................
................................
.............

148

CA Ingres II

................................
................................
................................
.....

149

Software Development Kit

................................
................................
....

149

IBM DB2 (NT)

................................
................................
................................
..

149

Statement Limitation

................................
................................
.............

149

Informix (NT)

................................
................................
................................
...

150

Error while ODBC Re
-
Connection

................................
........................

150

Paradox

................................
................................
................................
...........

150

Error when ALIASES used in WHERE Clause

................................
.....

150

Chapter 20.

Interface Node Clock

................................
................................
..........

151

Time Synchronization with PI Server

................................
..............................

152

Time Zone and Daylight Saving

................................
................................
......

152

Chapter 21.

Security

................................
................................
...............................

153

Windows

................................
................................
................................
..........

153

Chapter 22.

Starting

/ Stopping the Interface

................................
.......................

155

Starting Interface as a Service

................................
................................
........

155

Stopping Interface Running as a Service

................................
........................

155

Chapter 23.

Buffering

................................
................................
.............................

157

Which Buffering Application to Use

................................
................................
.

157

How Buffering Works
................................
................................
.......................

158

Buffering and PI Server Security

................................
................................
.....

159

Enabling Buffering on an Interface Node with the ICU

................................
...

159

Choose Buffer Type

................................
................................
..............

160

Buffering Settings
................................
................................
..................

160

Buffered Servers

................................
................................
...................

163

Inst
alling Buffering as a Service

................................
...........................

166

Chapter 24.

Interface Diagnostics Configuration

................................
.................

169

Scan Class Performance Points

................................
................................
.....

169

Performance Counters Points

................................
................................
.........

172

Performance Counters

................................
................................
..........

173

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

...............

174

Performance Counters for (_Total) only

................................
...............

175

Performance Counters for (Scan Class x) only

................................
....

177

Interface Health Monitoring Points

................................
................................
..

179

I/O Rate Point

................................
................................
................................
..

184

Interface St
atus Point

................................
................................
......................

186

Appendix A.

Error and Informational Messages

................................
...................

189

Interface
-
specific Output File

................................
................................
..........

189

Messages

................................
................................
................................
........

190


Relational
Database(RDBMS via ODBC) Interface

vii

System Errors and PI Errors

................................
................................
...........

190

UniInt Failover Specific Error Messages

................................
.........................

190

Informational

................................
................................
.........................

190

Errors (Phase 1 & 2)

................................
................................
.............

191

Errors (Phase 2)

................................
................................
....................

192

Appendix B.

PI SDK Options

................................
................................
..................

193

Appendix C.

Examples

................................
................................
...........................

195

Example 1.1


single tag query

................................
................................
.......

195

Example 1.2


query data array for a single tag

................................
.............

196

Example 1.3


three PI points forming a GROUP

................................
...........

197

Example 1.4


Tag Distribution

................................
................................
.......

198

Example 1.5


RxC Distribution

................................
................................
......

199

Example 1.6


Single Input with PI Annotations

................................
.............

200

Example 2.1a


insert sinusoid values into table (event based)

.....................

201

Example 2.1b


insert sinusoid values
into table (scan based)

......................

202

Example 2.1c


insert 2 different sinusoid values into table (event based)

....

203

Example 2.1d


insert sinusoid values with (string) annotations into RDB table
(event based)

................................
................................
................................
.................

204

Example 3.1


Field Name Aliases

................................
................................
.

205

Example 3.2


Tag Gr
oup, Fixed Column Positions

................................
.......

206

Example 3.3


Tag Group, Arbitrary Column Position


Aliases

....................

207

Example 3.4a


Tag Distribution, Search According to Real Tag Name

........

208

Example 3.4b


Tag Distribution, Search According to Tag's ALIAS Name

...

209

Example 3.4c


Tag Distribution with Auxiliary Column


rowRead

...............

210

Example 3.4d


Tag D
istribution with Auxiliary Table Keeping Latest Snapshot
211

Example 3.4e


Tag Distribution in Combination with /RBO and 'Time
-
W
indow'
212

Example 3.5


Tag Distribution with Aliases in Column Names

.....................

21
3

Example 3.6


RxC Distribution

................................
................................
......

214

Example 3.6b


RxC
Distribution Using PI_TIMESTAMP Keyword

...............

214

Example 3.7


Event Based Input

................................
................................
...

215

Example 3.8


Multi Statement Query

................................
............................

216

Example 3.9


Stored Procedure Call

................................
.............................

217

Example 3.10


Event Based Output

................................
..............................

218

Example 3.11


Output Triggered by 'Sinusoid', Values Taken from 'TagDig'
219

Example 3.12


Global Variables

................................
................................
....

220

Example 4.1


PI Point Database Changes


Short Form
Configuration

.......

221

Example 4.2


PI Point Database Changes


Long Form Configuration (only
changedate and tag name recorded)

................................
................................
.............

222

Example 5.1


Batch Export (not requiring Module Database)

.......................

223

Example 5.2a


Batch Export (Module Database required)
............................

224

Example 5.2b



UnitBatch Export (Module Database required)

.....................

225

Example 5.2c


SubBatch Export (Module Database required)

.....................

226

Example 6.1


Last One Hour of 'Sinusoid'
................................
.....................

227

Appendix D.

Hints and Checklist

................................
................................
...........

229

Hints for the PI System Manager

................................
................................
....

229

ORDER BY TIMESTAMP

................................
................................
.....

229

Table of Contents

viii


Suppress I/O Timeout

................................
................................
...........

229

Checklist and Trouble
-
Shooting

................................
................................
......

229

No Data (Input)

................................
................................
.....................

229

Appendix E.

For Users of Previous Interface Versions

................................
........

231

Read Before Update

................................
................................
.............

231

Upgrading the Interface from a Previous Version

................................
.

231

Appendix F.

Interface Test Environment
................................
...............................

235

Interface Version 1.28

................................
................................
.....................

235

Inte
rface Version 2.x

................................
................................
.......................

235

Interface Version 3.x

................................
................................
.......................

236

Tested RDBMSs

................................
................................
..............................

237

Appendix G.

Technical Support and Resources

................................
..................

239

Before You Call or Write for Help

................................
.........................

239

Help Desk and Telephone Support
................................
.......................

239

Search Support

................................
................................
.....................

240

Email
-
based Technical Support

................................
............................

240

Online Technical Support

................................
................................
.....

240

Remote Access

................................
................................
.....................

241

On
-
site Service

................................
................................
.....................

241

Knowledge Center

................................
................................
................

241

Upgrades

................................
................................
..............................

241

OSIsoft Virtual Campus (vCampus)

................................
......................

241

Appendix H.

Revision History

................................
................................
................

243


Relational
Database(RDBMS via ODBC) Interface

ix

Terminology

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

Buffering

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

suppor
ts 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
Server
s

however it does not guarantee identical a
rchive records since point compressions
attributes

could be different between PI Servers. With this in mind, OSIsoft recommends that
you run
PIBufss

instead.)

ICU

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 comm
and 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 interfac
es, 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.

PI API

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.

Terminology

x


PI Collective

A PI Collective is two or more replicated PI Servers that collect data concurrently.
Collecti
ves 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

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 o
perating
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

[PIHOME]
\
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
.

PIHOME64

PIHOME64

is

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

A typical
PIHOME64

is
C:
\
Program File
s
\
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 abbr
eviation 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.

PI SDK

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 prog
rams are installed. The PI Server
runs on the PI Server Node.


Relational Database(RDBMS via ODBC) Interface

xi

PI SMT

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.

P
ipc.log

The
pipc.log

file is the file to which OSIsoft applications write informa
tional and error
messages. When

a PI interface runs, it writes to the
pipc.log

file. The ICU allows easy
access to the
pipc.log
.

Point

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

poin
t” 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.

Service

A Service is a Windows program that runs without user interaction. A Servi
ce 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 poin
t 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 dev
ice and write these values to an
Input Tag
. Interfaces use an
Output Tag

to write a value to the device.



Relational
Database(RDBMS via ODBC) Interface

1

Chapter 1.

Introduction

The interface allows bi
-
directional transfer of data between the PI System and any Relational
Database Management System (RDBMS) that supports
O
pen
D
ata
B
ase
C
onnectivity
(ODBC) d
rivers. The interface runs on Microsoft Windows operating systems, and is able to
connect to any PI Server node available on 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 directly defined in the
ExtendedDescriptor

of a PI
T
ag. These SQL statements are the
source of data for one or more PI
T
ags


data input
;

and, similarly, PI
t
a
gs can provide values
for RDB


data output
.

The interface makes internal use of the PI API and PI SDK in order to keep a standard way of
interfacing from
a client node to the PI Server n
ode.

Note
:

Databases and ODBC drivers not yet tested with the interf
ace may require
additional onsite testing, which will tr
anslate to additional charges. R
efer to
Appendix
G
: Interface Test Environment

for a list of databases and ODBC drivers that the
interface is known to work with. Even if the customer’s database and/or ODBC driver
is not shown, the interface
will likely
work

with it.

P
lease contact the
OSIsoft
technical support
, or the local sales re
p. for additional guidance
.


Note
:

Version 3.x of the RDBMSPI Interface is a major
revision

(as the version 2.x
was for version 1.x) and many enhancements have been made that did not fit into
the design of the previous version.
Refer to

Appendix F: For Users of Previous
Interface Versions

prior to upgrading an older version of the interface.

The
i
nterface runs on Intel
computers

with Microsoft Windows operating systems

and

t
he
Interface Node may be either a PI home or PI API n
ode


see section
Configuration Diagram
.


This document contains the following topics:



Brief design overview



Installation and operation details



PI Points configuration details (points that will receive data via this
interface)



Supported command line parameters



Commented examples

Note:


The value of [PIHOME] variable for the 32
-
bit interface will depend on whether
the interface
is being installed on a 32
-
bit operating s
ystem
Introduction

2


(
C:
\
P
rogram

Files
\
PIPC
) or a 64
-
bit o
peratin
g system
(
C:
\
Program

Files

(x86)
\
PIPC
).

The valu
e of [PIHOME64] variable for a

64
-
bit interface will be C:
\
Program

Files
\
PIPC on
the 64
-
bit Operating system.

In this documentation [PIHOME] will be used to represent the value for either [PIHOME]
or [PIHOME
64]. The value of [PIHOME] is the directory which is the common location for
PI client applications.


Note:


Throughout
this manual
,

there are references to where messages are written

by the interface
,

which is the
PIPC.log.
Since the interface version
3.20.6.x the

interface has been built against a UniInt

version 4.5.
5
.
22
,

which now writes all its
messages to the local PI Message log.

Please note that any place in this manual where
it references
PIPC.log should now
refer to the
local PI message log. Pl
ease see the document
UniInt Interface
Message Logging.docx
in the %PIHOME%
\
Interfaces
\
UniInt directory for more
details on how to access these messages.

Reference Manuals

OSIsoft



PI Server
M
anuals



PI API
and PI SDK M
anual



UniInt Interface User Manual



Examples_readme.doc

Vendor



Vendor specific ODBC Driver Manual



Microsoft ODBC Programmer's Reference

Supported Features

Feature

Support

Part Number

PI
-
IN
-
OS
-
RELDB
-
NTI

* Platforms

32
-
bit Interface

64
-
bit Interface

Windows XP


32
-
bit OS

Yes

No


64
-
bit OS

Yes

(Emulation Mode)

No

Windows 2003 Server


32
-
bit OS

Yes

No


64
-
bit OS

Yes

(Emulation Mode)

No

Windows Vista


Relational Database(RDBMS via ODBC) Interface

3

Feature

Support


32
-
bit OS

Yes

No


64
-
bit OS

Yes

(Emulation Mode)

No

Windows 2008


32
-
bit OS

Yes

No

Windows 2008 R2


64
-
bit OS

Yes

(Emulation Mode)

No

Windows 7


32
-
bit OS

Yes/No

No


64
-
bit OS

Yes

(Emulation Mode)

No


Auto Creates PI Points

No

Point Builder Utility

No

ICU Control

Yes

PI Point Types

Float16 / Float32 / Float64 / Int16 / Int32 / Digital
/ String / Timestamp

Sub
-
second Timestamps

Yes

Sub
-
second Scan Classes

Yes

Automatically Incorporates PI

Point
Attribute Changes

Yes

Exception Reporting

Yes

Outputs from PI

Yes (Event
-
based, Scan
-
based)

Inputs to PI:

Scan
-
based /
u
nsolicited

/

Event Tags

Supports Questionable Bit

No

*
Support

for reading/writing to PI
Annotations

Yes

Supports Multi
-
character PointSource

Yes

Maximum Point Count

Unlimited

Required PI API Version

1.6.0+

* Uses PI SDK

Yes

PINet String Support

No

* Source of Timestamps

RDBMS or PI Server

History Recovery

Yes

*
UniInt
-
based


* Disconnected Startup


* SetDeviceStatus

Yes

No

Yes

*
Failover

UniInt Phase 2

Failover

(cold)
; Server
-
level
failover

* Vendor Software Required on PI
Interface Node / PINet Node

Yes

Vendor Software Required on Foreign
Device

Yes

Vendor Hardware Required

No

Additional PI Software Included with
Interface

No

Device Point Types

See note below.

Introduction

4


Feature

Support

Serial
-
Based Interface

No

* See

paragraphs below for further explanation.

Platforms

The Interface is designed to run on the above mentioned Microsoft Windows operating
systems and their associated service packs.

Please contact OSIsoft Technical Support for more information.

Support for reading/writing to PI Annotations

Next to the
times
tamp
,
value

and
status
, the RDBMSPI interface can write/read also to PI
annotations

(see section
Data Acquisition Strategies

and
take a look at

the
PI_ANNOTATION keyword).

Uses PI SDK

The PI SDK and the PI
API are bundled together and must be installed on each PI Interface
node. This Interface specifically makes PI SDK calls to access the PI Batch Database and
read some PI Point Attributes. Since interface version 3.15, PI SDK is used to write and read
to/f
rom PI Annotations.

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 and uses values stored in the PI Archive for
outputting them through a suitable SQL statement (mostly INSERT
s

or UPDATE
s
). See
section
RDBMSPI


Output Recovery Modes
, for more on this topic.

For input tags, history recovery often depends on the WHERE condition o
f a SELECT query.
Moreover
, since version 3.17, the interface implemented enhanced support for the input
history recovery; for more detailed description, see section
RDBMSPI


Input Recovery
Modes

UniInt
-
based

UniInt st
ands for Universal Interface. UniInt is not a separate product or file; it is an
OSI
soft
-
developed template
used by developers

and is integrated into many interfaces,
including this interface. The purpose of UniInt is to keep a consistent feature set and b
ehavior
across as many
of OSIsoft’s

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
UniInt
-
supplied configuration parameters and some interface
-
specific
parameters. UniInt is
constantly being upgraded with new options and features.

The
UniInt Interface User Manual

is a supplement to this manual.


Relational Database(RDBMS via ODBC) Interface

5

SetDeviceStatus

The RDBMSPI Interface
3.15+

is built with UniInt 4.
3
+, where the new functionality has
been adde
d to support health tags


the health tag with the point attribute

Exdesc =
[UI_DEVSTAT]

is used to represent the status of the source device.

The following events will be written into the tag:



"0 | Good | " the interface is properly communicating and
gets data from/to the
RDBMS system via the given ODBC driver



"3 | 1 device(s) in error | " ODBC data source communication failure



"4 | Intf Shutdown | "the interface was shut down

R
efer to the
UniInt Interface User Manual.doc

file for more information on h
ow to
configure health points.


Failover



Server
-
Level

Failover

The interface supports the FAILOVER_PARTNER keyword in the connection string
when used with the Microsoft SQL Server 2005
(and above)
and the Native Client
ODBC driver. In other words, in case

the interface connects to the mirrored
Microsoft SQL Servers and the connection gets broken, the interface will attempt to
reconnect the second SQL Server.



UniInt Failover Support

UniInt Phase 2 Failover

provides support for
cold, warm, or hot

failover
co
nfigurations. The Phase 2 hot failover results in a
no data loss

solution for bi
-
directional data transfer between the PI Server and the Data Source given a single
point of failure in the system architecture similar to Phase 1. However, in warm and
cold fa
ilover configurations, you can expect a small period of data loss during a
single point of failure transition.


This failover solution requires that two copies of the
interface be installed on different interface nodes collecting data simultaneously from
a

single data source.


Phase 2 Failover requires each interface have access to a shared
data file.

Failover operation is automatic and operates with no user interaction. Each
interface participating in failover has the ability to monitor and determine livel
iness
and failover status. To assist in administering system operations, the ability to
manually trigger failover to a desired interface is also supported by the failover
scheme.

The failover scheme is described in detail in the
UniInt Interface User Manua
l
,
which is a supplement to this manual. Details for configuring this Interface to use
failover are described in the
UniInt Failover Configuration

section of this manual.

This interface supports UniInt Phase 2, cold failover.

Vendor Software Required

The ODBC D
river Manager comes with Microsoft Data Access Components (MDAC). It is
recommended to use the latest MDAC available at:
http://msdn.microsoft.com

(and search
for the MDAC keyword).In addition, the given (RDBMS sp
ecific) ODBC driver must be
installed and configured on the interface node.


Introduction

6


Device Point Types

For full description of the ODBC supported data types see the ODBC Programmer's
Reference available on
http://msdn.microsoft.com/en
-
us/library/ms714177.aspx
.

The interface does some internal consideration in terms of mapping the RDBMS data types to
PI data types and vice versa. For more in
formation on this topic see sections:

Mapping of SQL (ODBC) Data Types to PI Point Types


Data Input


and
Mapping of Value and Status


Data Input
.



Relational Database(RDBMS via ODBC) Interface

7

Configuration Diagram

In the foll
owing
figures

there is the basic configuration of the hardware and software
components in a typical scenario
used with the RDBMSPI Interface.

Configuration Diagram


PI Home Node with PI Interface Node and RDBMS Node


Introduction

8


Configuration Diagram


All PI Softwa
re and RDBMS Installed on one Node


Note
:

For both of the configuration options depicted above
-

t
he communication
between
the
RDBMPI interface and
a
PI Server is established via PI API
and,
optionally
, through
PI SDK.
PI API is used for the actual transfer of time series,
PI
SDK is used for replication of the PI Batch Database and for reading from and writing
to PI Annotations.
To activate the PI SDK link, the
/PISDK=1

start
-
up parameter
must be
defined
.

The communicati
on between the RDBMSPI interface and
a

relational database
happens

through the ODBC library. The interface can thus connect a relational
database, which runs either on an interface node
,

or can be remote. This remote
database
node does
not have to be
the
W
indows platform.



Relational
Database(RDBMS via ODBC) Interface

9

Chapter 2.

Principles of Operation

The PI Relational Database Interface runs on Windows operating systems as a console
application or as a

Windows

NT service. As already stated, it uses the extended PI API and
/or

PI SDK to connect to the PI Server node and the specified ODBC driver for connection to the
R
elational
D
ata
B
ase (RDB). For the ODBC connection, the
D
ata
S
ource
N
ame (DSN) must
be created vi
a the ODBC Administrator (the Data Sources ODBC icon in Windows Control
Panel). This DSN name is then passed within the start
-
up parameters of the interface;
example: /DSN=
Oracle
X
.

SQL queries are provided by the user in the form of either ASCII files, or

via direct definition
in the PI point's
ExtendedDescriptor
. Queries are executed according to the scan class
type

(
cyclic or event driven
)

of a PI point holding the query definition.

In the direction from a relational database to PI

-

input
, the
appropriate SELECT
,

or

a

Stored
Procedure returning a result
-
set
,

must be specified
.

T
he interface
then
converts the result
-
set
into the PI

time
-
series

concept of:
timestamp
,
value
,
status
,
annotation
.


The opposite direction

-

writing

data out of PI to RD
B

-

uses

the concept
of run
-
time
placeholders

and a different set of SQL statements

(INSERT, UPDATE, Stored Procedure
call,..)
.

General Features Supported by the Current Version

if the RDBMSPI interface:



Query
t
imestamp
,
v
alue
,
s
tatus

and
/or

a
nnotation

in

RDB
t
ables



Scan or Event based (input)

o

SELECT queries or Stored Procedures calls

o

Query data (input) for:
Single tags
,

Multiple tags

(Tag Group),
Multiple tags
via TagName Key

(Tag

Distribution and RxC Strategy).



Event or Scan based (output): INSERT, UPDATE and DELETE
statements and
Stored Procedure calls



Support of multiple
SQL
statements


mul
tiple statements per PI tag (multiple
s
tatements can be one single

transaction
-

/TRANSACT

keyword

or auto
-
committed)



Mill
isecond and sub
-
millisecond timestamp resolution



Support of runtime placeholders:
t
imestamp

(
s
can
t
ime,
s
napshot
t
ime,

),
v
alue
,
s
tatus

and
/or

a
nnotation
,
including the
f
oreign
t
ags
(
tags outside the interface
point
-
source
)



Access to

all PI point attribut
e
s

(classic point class)
through
placeholders
AT.x



History recovery for input and output points



Old PI Batch Database replication to RDB through BA.x placeholders

Principles of Operation

10




New PI Batch Database replication (batches, unit
-
batches, sub
-
batches)



Recording of the PI point attribute changes into RDB



Support for different Timezone/DST settings than PI Server



RDB timestamps as well as timestamps taken from PI (through placeholders) can
optionally be in UTC (
/UTC

start
-
up parameter)



And many others.


T
he two sections
, which follow

-

Concept of Data Input from Relational Database to PI

and
Concept of Data Output from PI to Relational Database
,

briefly explain
the
basics

how
the
ti
me
-
series
data is transferred from RDB to
PI
and
vice versa (
from PI

to RDB
)
.
The m
ore
detailed description

of SQL

statements,
placeholders,
retrieval strategies, hints to individual
RDBs
, etc.

follows

in
section

SQL Stat
ements

later on in this manual.

Concept of Data Input from Relational Database to PI

The SELECT query is generally expected to provide a result
-
set consisting of the following
columns:
timestamp
,
value
,
status

[
,
annotation]
. The interface then internally t
ransforms
such
a
result
-
set according to the specified distribution strategy. The following paragraphs
outline

the principles of the individual data collection
approaches
; from
a

si
mple

approach
-

per
-
tag
-
query
-
definitions to more complex ones, where the
result
-
set contains time
-
series for multiple
tags.

Note
:

The sole purpose for supporting several distribution strategies is to minimize
the number of queries the interface execute
s
. As stated in the beginning of this
manual



an
execution of fewer querie
s
, which

retrieve

data for more PI tags
,

is less
expensive
and faster
than per
-
tag

query execution
.

Query for Single Tag


One Value per Scan

There are
D
istributed
C
ontrol
S
ystems (DCS) that keep only current values in relational
database tables.
Through

t
he scan
-
based,
per
-
tag defined
SELECT
,
the interface
can
read
such

data in
a

timely
(periodical)
manner
.

In fact,

RDBMSPI then simply
emulate
s

the
behavior of a standard DCS interface

-

t
he
corresponding
SELECT is expected to return
exactly

one row
, which
represents a snapshot of the given value.

The
obvious
disadvantage of
this kind of data retrieval is low performance and accuracy limited to the scan frequency.
Needless to say that relational databases

are not designed for this kind querying

on a massive
scale
, like

hundreds or thousands of queries executed in a loop.


A typical low throughput query
can be
similar to
:


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

Query for Single Tag


Time
-
Series

per Scan

A
better

strategy
(c
ompared to the one described in the previous paragraph)
is to
define

low
er

scan rates (e.g. 1+ minute) instead of
executi
ng the “one
-
row
-
delivering
” SELECT every
second
. In other words, getting the same amount of data in one call is faster

and less
expensi
ve

than getting it in many calls
.

T
his approach
,

however,
assumes that RDB tables get

Relational Database(RDBMS via ODBC) Interface

11

populated by INSERT
s

(
not UPDATE
s
);

t
he interface
then retrieves

“true time
-
series”
, even
if still for one tag
.

T
he
mandatory
t
imestamp column

serves as a

book
-
mark
, beca
use

t
he
ultimate
goal is to read only the newly arrived rows since the last scan
.


An example of a query delivering a
per
-
tag
-
time
-
series can be

like
:


SELECT Timestamp,Value,Status FROM Table WHERE
Name=? AND
Timestamp>? ORDER BY Timestamp

ASC
;


As a
result, t
he interface
obtains

a succession of
time
-
ordered
rows
representing only those,

which have been INSERTed since the last scan. This is achieved by asking for rows bigger
than the
timestamp

question
-
mark.
Moreover,

the interface can also do the PI e
xception
reporting
, because the result
-
set is ordered.

For more detailed description of the per
-
tag reads, see section
SQL SELECT Statement for
Single PI Tag
. Concrete examples are in:

Appendix
C
: Examples Example 1.1


single tag query
.

Appendix C: Examples Example 1.2


query data array for a single tag
.

Tag Groups

Another way of improving performance
(
compared to reading
time
-
series
for a single t
ag
)

is
grouping tags together.
For Tag Groups
-

an

RDB table should be structured in a way that
multiple
events

are stored in the same r
ow

in more columns.
Hence, t
he result
-
set for Tag
Groups
must be of

the following form:

Timestamp,
Value1,Status1,Value2,Status2,…

Timestamp,
Value1,Status1,Value2,Status2,…



or, in case there is a timestamp column for every value/status pair:

Timestamp1
,
Value1
,
Status1
,
Timestamp2
,
Value2
,
Status2
,


Timestamp1
,
Value1
,
Status1
,
Timestamp2
,
Value2
,
Status2
,




A
n example of the
“Tag Group”
SELECT statement can be

like
:

SELECT Timestamp,Value
1
,Status
1,Value2,Status2,…

FROM Table WHERE
Timestamp>? ORDER BY Timestamp

ASC
;


Obviously, querying such a table is more efficient than having a SELECT statement defined
per
a single tag.
For detailed description of this distribution strategy, see section

SQL
SELECT Statement for Tag Groups
. The concrete example:

Appendix
C
: Examples

Example
1.3


three PI points forming a GROUP
.

Tag Distribution

Compared to Tag Groups, where grouping happens in the form of multiple
V
alue
N
,
S
tatus
N

pairs

in the SELECT list
,

the
Tag Distribution
approach
requires

“named
r
ows”.
T
hat means
,
an additional field must be
provided

in the result
-
set; the
Name

column

then
tell
s

the interface
to which target point a particular row
will

be distributed.
The result set for Tag Distribution
should have the following form:

Timestamp,Name,Value,Status

Timestamp,Name,Value,Status



Principles of Operat
ion

12


An example of a “Tag Distribution” SELECT can be

like
:

SELECT Timestamp,
Name,
Value,Status

FROM Table WHERE

Timestamp > ?
;

Again,
a

SELECT query deliv
ering

time
-
series for multiple tags allow
s

for
more
efficient
data retrieval.
For a detailed description of

this distribution strategy, see section
SQL
SELECT Statement for Tag Distribution
. A
concrete example can be found in

Appendix C:
Examples Example 1.4


Tag Distribution
.

RxC Dis
tribution (combination of Group and Distribution)

Some laboratory data in RDB tables have a c
ommon structure that looks like
:


SAMPLETIME,TANK_NAME,TANK_LEVEL,TANK_LEVEL_STATUS,TEMPERATURE_NAME,T
EMPERATURE_VALUE,TEMPERATURE_STATUS,DENSITY_NAME, DENSITY_VAL
UE,
DENSITY_STATUS,





To
efficiently
transform
and forward
this kind of result
-
set

to PI
,

the interface implements a
strategy

that

accepts data being structured as follows:

Timestamp
,
Name1
,
Value1
,
Status1
,
Name2
,
Value2
,
Status2
,



Timestamp
,
Name1
,
Value1
,
Status1
,
Name2
,
Value2
,
Status2
,




o
r, in

case there is a timestamp column for every name/value/status

set
:

Timestamp1
,
Name1
,
Value1
,
Status1
,
Timestamp2
,
Name2
,
Value2
,
Status2
,


Timestamp1
,
Name1
,
Value1
,
Status1
,
Timestamp2
,
Name2
,
Value2
,
Status2
,




The RxC approach

is actually a combination of the Tag Group and Tag Distribution
retrieval
s.

For a more detailed description of this distribution strategy, see section
SQL
SELECT Statement for RxC Distribution

and a concrete example can be found in
Appendix
C
: Examples
Example 1.5


RxC Distribution
.

Concept of Data Output from PI to Relational Database

Transferring data from PI to a relational database works similarly to RDB r
eading; that is, an
appropriate SQL statement (usually INSERT) needs to be
defined
. 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
n

SQL statement) must have a reference to its
SourceTag
. The
SourceTag

triggers the execution and the output tag itself then gets a copy
of the exported data to signal the succ
ess or failure of the output operation.

For periodical output, the corresponding
output
PI point configuration

should
look like
those
for
input points
; the difference is that the SQL statements used are those which modify the
relational database; INSERT,
UPDATE, DELETE, or the Stored Procedure call
. 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 v
alues into table (event based)
.


Relational Database(RDBMS via ODBC) Interface

13



Example 2.1d


insert sinusoid values with (string) annotations into RDB table
(event based)
.

Principles of Operation

14


Use of PI SDK

RDBMSPI features implemented through PI SDK are

the following
:



Writing to and reading from PI
a
nnotations

N
ext to t
he
timestamp
,
value

and
status
, RDBMSPI interface can write/read also
to PI
a
nnotations (see section
Data Acquisition Strategies

and
see

the
PI_ANNOTATION keyword).




Replication of PI Batch Database

S
ee chapter
PI Batch Database Output
.

All the above mentioned features are optional. However, users have to be aware that when
these features are configured on nodes with buffering
,

the
PI SDK buffering

must be enabled
in order
the PI SDK calls are buffered properly;

t
he
PI SDK
buffering ships with PI SDK
version 1.4 and above
.

Note:


In order to
activate the PI SDK link
, enable PI SDK through PI ICU
,

or manually specify the
/PISDK=1

start
-
up parameter.


CAUTION!


When RDBMSPI interface runs against High Availability PI Servers, SQL queries
containing the annotation column
on nodes
NOT

supporting the PI SDK buffering
,

will NOT deliver

events

to other
collective members, but

the primary.

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.


UniInt Failover

This interface supports UniInt failover. Refer to the
UniInt Failover Configuration

section of
this document for configuring the interface for failover.




Relational
Database(RDBMS via ODBC) Interface

15

Chapter 3.

Installation Checklist

If you are familiar with running PI data co
llection 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 Inte
rface. 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
b
uffering 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
i
nterface.

2.

If you are running the
i
nterface on an
i
nterface
n
ode, edit the PI Server’s
t
rust
t
able to
allow the Interface to writ
e 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 i
nstallation 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
i
nterface on an
i
nterface
n
ode, check the computer’s time zone
properties. An improper tim
e zone configuration can cause the PI Serve
r to reject the
data that this i
nterface writes.

6.

Run the ICU and configure a new instance of this
i
nterface. Essential startup
parameters for this
i
nterface are
:

P
oint 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.

Installation Checklist

16


8.

Build input tags and, if

desired, output tags for this i
nterface. Important
point
attributes and their

purposes

are:

Location1

spec
ifies the
i
nterface 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 P
I (snapshot, archive write,..).

Ex
tendedDescriptor

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
t
ag.

12.

It is recommended to test the connection between the interface node and the RDB
using any third
-
party ODBC based application. For example t
he ODBC Test
er

app.
f
rom 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
s
.

13.

Take
one
,

(ideally)
simp
le SQL statement
, which has been tested in the third party
tool

and configure an interface tag, which will use it
.

14.

Set or check the interface node clock.

15.

Start the
i
nterface interactively and confirm its successful connection to the PI Server
without buffe
ring.

16.

Confirm that the
i
nterface collects data successfully

for one tag
.

If OK, add more.

17.

Stop the
i
nterface 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
i
nterface. C
onfirm that the
i
nterface 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
the Windows

Service. Conf
irm that the
i
nterface
runs properly as a Service.

20.

Restart the
i
nterface
n
ode and confirm that the
i
nterface and the buffering application
restart.


Relational Database(RDBMS via ODBC) Interface

17

Interface Diagnostics


1.

Configure Scan Class Performance points.

2.

Install the PI Performance Monitor Interface

(Full Version only) on the Interface
Node.

3.

Configure Performance Counter points.

4.

Configure UniInt Health Monitoring points

5.

Configure the I/O Rate point.

6.

Install and configure the Interface Status Utility on the PI Server Node.

7.

Configure the Interface Stat
us point.

Advanced Interface Features

Configure UniInt
Failover;

see that section in this document for details related to configuring
the interface for failover.



Relational
Database(RDBMS via ODBC) Interface

19

Chapter 4.

Interface
Installation

Interface on PI Interface Nodes

OSIsoft recommends th
at interfaces be installed on
PI
i
nterface
n
odes instead of directly on
the PI

Server node
.
A PI
i
nterface
n
ode is any node other than the PI Server node where the
PI

Application Program
ming

Interface (PI API) is

installed (see the PI
API

manual)
.
With
this approach, the PI Server need not compete with interfaces for the machine’s resources
.
The primary function of the PI

Server is to archive data and to service clients that request
data.

On

the PI API nodes, OSIsoft’s interfaces are usually installed along with the buffering
service
.
For more information about Buffering see the
Buffering

section of this manual.

In most cases, interfaces on PI
i
nterface
n
odes
should be installed as automatic services
.
Services keep running after the user logs off
.
Automatic services automatically restart when
the computer is restarted, which is useful in the event of a power failure.

The guidelines are different if an interface

is installed on the PI Server node
.
In this case, the
typical procedure is to install the PI Server as an automatic service and install the interface as
an automatic service that depends on the PI Update Manager and PI Network Manager
services. This typic
al scenario assumes that Buffering is not enabled on the PI Server node.
Bufserv can

be enabled on the PI Server node so that interfaces on the PI Server node do not
need to be started and stopped in conjunction with PI, but it is not standard practice to
enable
buffering on the PI Server node
. The PI Buffer Subsystem
can also be installed

on the PI
Server.

See the
UniInt Interface User Manual

for special procedural information.

More considerations about NT Services and ODBC applications are
described
in

se
ction

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

Naming Conventions and Requirements

In the installation procedure below, it is assumed that the name of the interface executable is
rdbmspi
.
exe

and t
hat the startup command file is called
rdbmspi
.bat
.

When Configuring the Interface Manually

It is customary for the user to rename the executable and the startup command file when
multiple copies of the interface are run
.
For example,
rdbmspi
1.exe

and
rdb
mspi
1.bat
would typically be used for interface number 1,
rdbmspi
2.exe

and
rdbmspi
2.bat

for
interface number 2, and so on
.
When an interface is run as a service, the executable and the
command file must have the same root name because the service looks for

its command
-
line
parameters

in a file that has the same root name.

Interface Installation

20


Note
:

The interface is installed along with the
.pdb

file (file containing the debug
information). This file can be found in the same directory as the executable

file

or in
%windir%
\
Symbols
\
exe
. If you rename the
rdbmspi.exe

to
rdbmspi1.exe
,
you also have to create/rename the corresponding
.pdb

file. That is,
rdbmspi.pdb

to
rdbmspi1.pdb
.

Interface Directories