Foxboro Batch
Interface to the PI System
Version 1.0.2.2
Revision B
i
How to Contact Us
OSIsoft, Inc.777 Davis St., Suite 250
San Leandro, CA 94577 USA
Telephone
(01) 510-297-5800 (main phone)
(01) 510-357-8136 (fax)
(01) 510-297-5828 (support phone)
Houston, TX
Johnson City, TN
Mayfield Heights, OH
Phoenix, AZ
Savannah, GA
Seattle, WA
Yardley, PA / Worldwide Offices
OSIsoft Australia
Perth, Australia
Auckland, New Zealand
OSI Software GmbH
Altenstadt,Germany
OSI Software Asia Pte Ltd.
Singapore
OSIsoft Canada ULC
Montreal, Canada
OSIsoft, Inc. 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
Sales Outlets and Distributors
§ Brazil
§ Middle East/North Africa
§ Republic of South Africa
§ Russia/Central Asia / § South America/Caribbean
§ Southeast Asia
§ South Korea
§ Taiwan
WWW.OSISOFT.COM
OSIsoft, Inc. is the owner of the following trademarks and registered trademarks: PI System, PI ProcessBook, Sequencia, Sigmafine, gRecipe, sRecipe, and RLINK. All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Any trademark that appears in this book that is not owned by OSIsoft, Inc. is the property of its owner and use herein in no way indicates an endorsement, recommendation, or warranty of such party’s products or any affiliation with such party of any kind.
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
Unpublished – rights reserved under the copyright laws of the United States.
© 2006-2007 OSIsoft, Inc. PI_FoxBatch.doc
85
Foxboro Batch Interface to the PI System 85
Table of Contents
Introduction 1
Reference Manuals 2
Supported Features 2
Diagram of Hardware Connection 4
Principles of Operation 5
Overview 5
Startup 6
Data Collection Modes 7
Maintaining Connections to SQL Server and PI Server 7
Gathering Performance Information 8
Controlling Functionality without Stopping the Interface 8
Installation Checklist 9
Interface Installation 11
Naming Conventions and Requirements 11
Interface Directories 11
PIHOME Directory Tree 11
Interface Installation Directory 11
Interface Installation Procedure 12
Installing Interface as a Windows Service 12
Installing Interface Service with PI ICU 12
Installing Interface Service Manually 15
SQL Server Configuration 17
Batch Data Recovery 25
Initialization File 27
Sample PIFoxBatch.ini File 32
Digital States 33
PointSource 35
PI Point Configuration 37
Interface Performance Tags 39
I/O Rate Tag Configuration 41
Startup Command File 43
Configuring the Interface with PI ICU 43
foxbatch Interface Tab 45
Command-line Parameters 61
Sample PIFoxBatch.bat File 67
Control File 69
Sample PIFoxBatch<serviceid>_CONTROL.txt File 71
Interface Node Clock 73
Security 75
Starting / Stopping the Interface 77
Starting Interface as a Service 77
Stopping Interface Running as a Service 77
Starting Interface Interactively 77
Buffering 79
Appendix A: Error and Informational Messages 81
Message Logs 81
Messages 81
Initialization or Startup Errors 81
General Errors 84
System Errors and PI Errors 85
Revision History 87
85
Foxboro Batch Interface to the PI System 85
Introduction
The OSIsoft’s PI Foxboro Batch (PI FoxBatch) Interface to the PI Data Archive collects batch data from the SQL Server-based data log on Foxboro IA Series DCS Batch Execution System (BES).
The flow of data is uni-directional; the data can only be read from a Foxboro IA BES and written to the PI Server. By design, the Interface does not edit or delete the source data. However, the Interface does create its own tables, copies source data into those tables and deletes data from them.
The PI FoxBatch Interface is a scan-based Interface and is designed to populate the PI Batch Database and the PI Module Database. The Interface requires two performance tags and, unlike most other PI Interfaces, there are no other tags associated with the Interface. The Interface does not use the PI API buffering service because the batch data is already buffered by the Foxboro IA BES historian database.
The Interface can run in three different modes:
1. Setup
- RealTime (default)
3. Recovery
The Setup mode is used to setup the processing environment for the Interface and it must be used when the Interface is run for the first time or the settings in initialization file has been changed. If the Interface has not been run in the Setup mode, then the other two modes may not collect data.
The RealTime mode is the default mode of operation and the Recovery mode is designed to recover batch data provided the data is still in the SQL Server. The principal difference between RealTime and Recovery mode is that in the RealTime mode the Interface will process each data record to the PI Server as soon as it is retrieved from the SQL Server, whereas in the Recovery mode, the start and the end times of the entire batch must be known before comparison with the data in PI and sending any missing data to PI.
In Recovery mode, all open batches are processed only when there are no complete batches left to be processed. If the Interface is started in Recovery mode, it will change to RealTime mode as soon as it completes the recovery process. The Interface can also perform data integrity checks without updating the PI Server by running the Interface in Recovery mode along with the /noupdate switch.
The Interface is designed to handle connection losses either to the PI Server or to the SQL Server database. The Interface does not require either the SQL Server or the PI Server databases to be available at the time of the Interface startup. If the connection to the PI or SQL Server fails due to network interruption or server unavailability, the Interface will periodically try to reestablish the connection to the server. Once the Interface reconnects and has good communication to both databases, it will process all the data since the connection was lost.
Note: The PI FoxBatch Interface requires PI Server version 3.3 SR 2 or higher, PI SDK version 1.3.4.333 or higher, and MDAC version 2.6 or higher.
Reference Manuals
OSIsoft
· PI Server manuals
· PI SDK User manual
Foxboro
· Foxboro IA Series DCS manual
Supported Features
Feature / Support /Part Number / PI-IN-FX-BA-NTI
* Platforms / Windows (2000, XP)
APS Connector / No
Point Builder Utility / No
ICU Control / Yes
PI Point Types / Integer
Sub-second Timestamps / Yes
Sub-second Scan Classes / No
Automatically Incorporates PIPoint Attribute Changes / No
Exception Reporting / No
Outputs from PI / No
Inputs to PI: Scan-based / Unsolicited / Event Tags / Scan-based
Supports Questionable Bit / No
Supports Multi-character PointSource / Yes
Maximum Point Count / No
* Uses PI SDK / Yes
PINet String Support / N/A
* Source of Timestamps / Device, Interface
* History Recovery / Yes
UniInt-based / No
* Failover: UniInt-based / Other / No
* Vendor Software Required on PI Interface / PINet Node / Yes
Vendor Software Required on Foreign Device / No
Vendor Hardware Required / No
Additional PI Software Included with Interface / No
Device Point Types / N/A
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. Because it is dependent on vendor software, newer platforms may not yet be supported. Please contact OSIsoft Technical Support for more information.
Uses PI SDK
The PI SDK and the PI API are bundled and must be installed on each PI Interface node. The PI FoxBatch Interface specifically makes PI SDK calls to access the PI Module Database and PI Batch Database. The Interface requires PI SDK version 1.3.4.333 or higher to be installed. The Interface uses PI API to log messages in the local pipc.log file. It does not require a PI API connection to the PI Server.
Source of Timestamps
Since each batch data record in the Foxboro IA BES history data log contains a timestamp, the PI FoxBatch Interface uses that timestamp for the batch records in PI. For the performance tags, the Interface uses the local system time at the time the value is being recorded.
History Recovery
The operation of the PI FoxBatch Interface may be interrupted without loss of data. While the Interface is offline, the data is being buffered on the SQL Server.
The Interface can recover data provided it is still available in the Foxboro IA BES history database. In order to perform history data recovery, the Interface must be run in Recovery mode. In this mode, the Interface can recover data for any time period specified by the user. The recovery time period can be set by the optional command line parameters /rst and /ret. If none of these parameters are specified, the Interface will recover all the data from the SQL Server. Note, the data recovery is also limited by other factors on the PI Server, like the date of creation of each PIUnit, the size of archives into which data is backfilled, etc. For more detailed information on the recovery process see section “Batch Data Recovery.”
Vendor Software Required
The Microsoft Data Access Components (MDAC) version 2.6 or higher must be installed on the PI Interface node. This Interface specifically uses the ActiveX Data Objects (ADO) driver from the MDAC collection to make data queries to the Foxboro IA BES SQL-based history database.
Diagram of Hardware Connection
Figure 1. Schematic of Hardware Configuration for PI Foxboro Batch Interface
The Foxboro IA Series DCS Batch Execution System (BES) is installed with an embedded SQL Server as the native data historian. By default, the SQL Server setup installs Microsoft Data Access Components (MDAC) on the same node as the SQL server. For example, SQL Server 2000 SP1 comes with MDAC version 2.6. MDAC suite contains ODBC, OLEDB and ADO drivers. The PI Foxboro Batch Interface uses the ADO driver to connect to SQL Server for data access. MDAC 2.6 or higher is also required on the Interface node. Typically, MDAC is installed with the operating system. If MDAC is not installed, it can be downloaded from Microsoft website. The Interface connects to the PI Server through PI SDK. The Interface requires PI SDK 1.3.4.333 or higher to be installed on the same node as the Interface. The Interface may be installed either on the same node as the Sequel Server (SQL), PI Server or on a completely separate PI Interface node. Due to load balancing considerations, OSIsoft does not recommend that the Interface be installed on the same node as the PI Server. Contact the vendor of your Foxboro BES for recommendations as to installing third-party software, such as the PI Foxboro Batch Interface, on the same node as the SQL server. In the hardware configuration above where the Interface is installed on a separate PI Interface node, PI SDK and Microsoft MDAC are required to be installed on the Interface node.
85
Foxboro Batch Interface to the PI System 85
Principles of Operation
Overview
The PI Foxboro Batch Interface is designed to transfer batch data from the Foxboro IA Series DCS SQL Server-based data log to the PI Server Batch and Module databases. The Foxboro IA Series DCS Batch Execution System (BES) consists of various components and the SQL Server is one of them. The SQL Server is used to store the actions performed by the Foxboro IA Series DCS. The SQL Server contains multiple databases. The Foxboro BES is using only one specific database. In version 6.4 of the Foxboro BES, this database is named “BatchHistory.” The Interface is designed to connect only to the BatchHistory database (and not any other components on the BES or other databases on the SQL Server) and hence can collect data only from this database. Microsoft ADO driver for the SQL Server is used to communicate with the BatchHistory database.
The SQL Server has two tables that are of interest to this Interface. Those tables are named “BatchDetail,” “BatchIDLog.” However, the Interface does not always query data from these tables directly. The Interface creates three additional tables named “Backfill,” “Recovery” and “ActionCDOrder” and one trigger named “BatchDetailCopyToBackFill.” These names are configurable in the initialization file named PIFoxBatch.ini located in PIHOME\Interfaces\FoxBatch\. The purpose of the trigger is to copy data from the “BatchDetail” table to the “Backfill” table when the data is first written to the “BatchDetail” table. The Interface will then query the “BackFill” table at every scan. On successful data retrieval, it stores the data in the local data file named PIFoxBatch.bin and deletes the data from the “Backfill” table. Deleting the data from the “Backfill” table will guarantee that on the next scan the table will contain only new data. This method of data retrieval improves performance because we do not need to perform any complex queries on the SQL Server to identify new data. The local data file will be updated as the data is sent to PI. The Interface checks this file on every restart to see if there is any unprocessed data. The “Backfill” table is locked by the Interface while reading and deleting the data so that the trigger only caches the new data from the “BatchDetail” table without updating the “Backfill” table. Once the data is deleted from the “Backfill” table, the Interface will unlock the table and disconnect from the SQL server until the next scan.
The “Recovery” table is used during the batch history data recovery process. The Interface uses this table to filter and extract data from the “BatchDetail” table for the specified time period. This method of data extraction minimizes the data querying time to the SQL server and reduces the amount of data to be transferred over the network.
The purpose of the “ActionCDOrder” table is to filter and order the data during data query by the Interface. The reference to this table is used in all data queries performed by the Interface. Based on the retrieved data, the Interface creates the necessary PIModules and it adds PIBatches, PIUnitBatches and PISubBatches to PI Server. The PI-SDK provides read and write access to the PI Server.