The value 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 [PIHOME64]. 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. Please 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 Manuals PI API and PI SDK Manual 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 32-bit OS Yes No 64-bit OS Yes (Emulation Mode) No
Installing Interface Service Manually Help for installing the interface as a service is available at any time with the command: RDBMSPI.exe –help Open a Windows command prompt window and change to the directory where the rdbmspi1.exe executable is located. Then, consult the following table to determine the appropriate service installation command. Windows Service Installation Commands on a PI Interface Node or a PI Server Node with Bufserv implemented Manual service RDBMSPI.exe –install –depend "tcpip bufserv" Automatic service RDBMSPI.exe –install –auto –depend "tcpip bufserv" *Automatic service with service id RDBMSPI.exe –serviceid X –install –auto –depend "tcpip bufserv" Windows Service Installation Commands on a PI Interface Node or a PI Server Node without Bufserv implemented Manual service RDBMSPI.exe –install –depend tcpip Automatic service RDBMSPI.exe –install –auto –depend tcpip *Automatic service with service id RDBMSPI.exe –serviceid X –install –auto –depend tcpip *When specifying service id, the user must include an id number. It is suggested that this number correspond to the interface id (/id) parameter found in the interface .bat file. Check the Microsoft Windows Services control panel to verify that the service was added successfully. The services control panel can be used at any time to change the interface from an automatic service to a manual service or vice versa.
PointSource The PointSource is a unique, single or multi-character string that is used to identify the PI point as a point that belongs to a particular interface. For example, the string Boiler1 may be used to identify points that belong to the MyInt Interface. To implement this, the PointSource attribute would be set to Boiler1 for every PI point that is configured for the MyInt Interface. Then, if /ps=Boiler1 is used on the startup command-line of the MyInt Interface, the Interface will search the PI Point Database upon startup for every PI point that is configured with a PointSource of Boiler1. Before an interface loads a point, the interface usually performs further checks by examining additional PI point attributes to determine whether a particular point is valid for the interface. For additional information, see the /ps parameter. If the PI API version being used is prior to 1.6.x or the PI Server version is prior to 3.4.370.x, the PointSource is limited to a single character unless the SDK is being used. Case-sensitivity for PointSource Attribute The PointSource character that is supplied with the /ps command-line parameter is not case sensitive. That is, /ps=P and /ps=p are equivalent. Reserved Point Sources Several subsystems and applications that ship with PI are associated with default PointSource characters. The Totalizer Subsystem uses the PointSource character T, the Alarm Subsystem uses G and @, Random uses R, RampSoak uses 9, and the Performance Equations Subsystem uses C. Do not use these Point Source characters or change the default point source characters for these applications. Also, if a PointSource character is not explicitly defined when creating a PI point; the point is assigned a default PointSource character of Lab (PI 3). Therefore, it would be confusing to use Lab as the PointSource character for an interface. Note: Do not use a PointSource character that is already associated with another interface program. However it is acceptable to use the same point source for multiple instances of an interface.
As of RDBMSPI 3.11, conditions can be placed on trigger events. Event conditions are specified in the ExtendedDescriptor as follows: /EVENT= 'tagname' condition The trigger tag name must be in single quotes. For example: /EVENT= 'Sinusoid' Anychange will trigger on any event coming from tag 'Sinusoid' as long as the next event is different than the last event. The initial event is read from the snapshot. For a complete list of available keywords see the definition.
Mapping of Value and Status – Data Input A single PI tag can only historize value or status, but never both together. Therefore, a consistent method of mapping a given value/status pair (SELECTed from an RDB table) into the PI concept is provided. PI System interfaces mostly apply the following rule: If the status of a value is ‘good’, store the value. If the status of a value is other than ‘good’, store the status instead. Note: Any requirement that goes beyond that needs more than one tag. Previous sections of this manual demonstrate that the interface requires both value and status in the SELECT field list. The following paragraphs will explain how these two fields make it into various PI point types. Mapping of SQL (ODBC) Data Types to PI Point Types – Data Input In general, the following columns can appear in the SELECT list: TIMESTAMPn TAGNAMEn (see section ) VALUEn STATUSn ANNOTATIONn To be able to process the aforementioned fields, the interface makes some considerations for their data types. The following table shows what combinations of PI point types and SQL column data types (used in SELECT queries) are valid. Tags that do not match those criteria are rejected by the interface. This does not mean that those tags cannot be serviced at all. It only means that additional explicit conversion might be required. The following tables list the allowed RDB data types in combination with PI tag types: RDB Data Types to PI Point Types Mapping – Value Input Field SQL Data Type PI Point Type Timestamp SQL_TIMESTAMP All PI point types Tag name SQL_CHAR, SQL_VARCHAR, SQL_LONGVAR CHAR All PI point types Real(R) Integer(I) Digital(D) String(S) Value Approximate (floating points) data types SQL_NUMERIC, SQL_DECIMAL, SQL_REAL , SQL_FLOAT, SQL_DOUBLE Cast to the particular type. Cast to long integer Cast to integer and interpreted as pointer to Digital State Set Converted from to string.
See these examples available in Appendix B: Note: The output point itself is populated with a copy of the SourceTag data whether the output operation was successful. Otherwise, the output tag will receive a digital state of Bad Output. Mapping of Value and Status – Data Output For output of data in the direction from PI to RDB, no fixed table structure is required. Corresponding placeholders are used for the intended data output. Although mapping of the placeholders (VL, SS_I, SS_C, etc.) to RDB data types works similarly as for the data input (see section ), some variations do exist. Following paragraphs list the differences. DIGITAL Tags Digital output tag values are mapped only to RDB string types. This means that the corresponding field data type in the table must be string, otherwise explicit conversion is required CAST(value_exp AS data_type). The following table shows the assignment of value placeholders (VL, SS_I, SS_C) for a Digital tag: Digital Output Tags Can only be Output to RDB Strings PI Value VL Field Type String SS_I Field Type Integer or Float SS_C Field Type String Digital state is NOT in the error range defined by /SUCC1 /SUCC2 start-up parameters <Digital State <String> 0 "O.K." Digital state IS in the error range defined by /BAD1 /BAD2 start-up parameters <Digital State String> 1 "Bad Value" See this example available in Appendix B: Examples Float, Integer and String Output Tags – Value and Status Mapping PI Value VL Field Type Numeric or String SS_I Field Type Numeric SS_C Field Type String Value NOT in error <Value> 0 "O.K." Digital State < Previous Value> <Digital State> <Digital State String>
tag/output tag event comparison matches /ooo_option 1 Yes The option that was matched is set to True e.g. /ooo_option= "replace" source archive event <> output archive event Source tag event is out of order and Source tag/output tag event comparison matches none of the /ooo_options -1 No n/a out-of-order events ignored 0 Yes @source_appended=True @source_replaced=False @source_removed=False Backward compatibility 1 No n/a e.g. /ooo_option= "append" source archive event <> output archive event no query execution for replaced data New source tag event (in-order) -1 Yes @source_appended=True @source_replaced=False @source_removed=False in-order events trigger query execution 0 Yes @source_appended=True @source_replaced=False @source_removed=False in-order events trigger query execution 1 Yes @source_appended=True @source_replaced=False @source_removed=False in-order events trigger query execution
output operation failed. Points can also get a status of I/O Timeout if the interface detects connection problems. Messages System Errors and PI Errors System errors are associated with positive error numbers. Errors related to PI are associated with negative error numbers. Error Descriptions on Windows On Windows, descriptions of system and PI errors can be obtained with the pidiag utility: Windows: \PI\adm\pidiag – error_number UniInt Failover Specific Error Messages Informational Message 16-May-06 10:38:00 RDBMSPI 1> UniInt failover: Interface in the “Backup” state. Meaning Upon system startup, the initial transition is made to this state. While in this state the interface monitors the status of the other interface participating in failover. When configured for Hot failover, data received from the data source is queued and not sent to the PI Server while in this state. The amount of data queued while in this state is determined by the failover update interval. In any case, there will be typically no more than two update intervals of data in the queue at any given time. Some transition chains may cause the queue to hold up to five failover update intervals worth of data. Message 16-May-06 10:38:05 RDBMSPI 1> UniInt failover: Interface in the “Primary” state and actively sending data to PI. Backup interface not available. Meaning While in this state, the interface is in its primary role and sends data to the PI Server as it is received. This message also states that there is not a backup interface participating in failover. Message 16-May-06 16:37:21 RDBMSPI 1> UniInt failover: Interface in the “Primary” state and actively sending data to PI. Backup interface available. Meaning While in this state, the interface sends data to the PI Server as it is received. This message also states that the other copy of the interface appears to be ready to take over the role of primary.
Example 1.4 – Tag Distribution SQL Statement (defined in file PI_REAL_DISTR1.SQL) SELECT PI_TIMESTAMP, PI_TAGNAME, PI_VALUE, PI_STATUS FROM T1_4 WHERE PI_TAGNAME LIKE 'Tag%' ORDER BY PI_TMESTAMP, PI_TAGNAME; Relevant PI Point Attributes Extended Descriptor (Distributor) Location1 (All points) Location2 (All points) Location3 Location4 All points Location5 All points 1 0 'Distributor' -1 'Target_Point(n)' 0 1 0 Instrumenttag (Distributor) Point Type (Distributor) Point Source (All Points) Float32 S RDBMS Table Design Table T1_4 PI_TIMESTAMP PI_VALUE PI_STATUS PI_TAGNAME Datetime (MS SQL Server) Date/Time (MS Access) Real MS SQL Server) Number (Single) Prec.(MS Access) Varchar(12) (MS SQL Server) Text(12) (MS Access) Varchar(80) (MS SQL Server) Text(80) MS Access) Example of an appropriate result-set: PI_TIMESTAMP PI_TAGNAME PI_VALUE PI_STATUS 20-Oct-2000 08:10:00 Target_Point1 10 NULL 20-Oct-2000 08:10:00 Target_Point2 20 NULL 20-Oct-2000 08:10:00 Target_Point3 30 10 goes to Target_Point1; 20 to Target_Point1; 30 to Target_Point3 … Note: See also section:.
Example 1.5 – RxC Distribution SQL Statement (defined in file PI_REAL_DISTR1.SQL) SELECT sampletime AS PI_TIMESTAMP1, 'Tag1' AS PI_TAGNAME1, [level] AS PI_VALUE1, sampletime AS PI_TIMESTAMP2, 'Tag2' AS PI_TAGNAME2, temperature AS PI_VALUE2, temperature_status AS PI_STATUS2, sampletime AS PI_TIMESTAMP3,'Tag3' AS PI_TAGNAME3, density AS PI_VALUE3, density_status AS PI_STATUS3 FROM T1_5 WHERE sampletime > ? AND tank = 'Tank1' Relevant PI Point Attributes Extended Descriptor (RxC Distributor) Location1 (All points) Location2 (All points) Location3 Location4 (All points) Location5 (All points) P1=TS 1 0 'RxC Distributor' -2 'Target points' 0 1 0 Instrumenttag (Distributor) Point Type (All points) Point Source (All Points) Float32 S RDBMS Table Design Table T1_5 SAMPLETIME LEVEL, TEMPERATURE, DENSITY LEVEL_STATUS, TEMPERAURE_ STATUS, DENSITY_STATUS TANK Datetime (MS SQL Server) Date/Time (MS Access) Real MS SQL Server) Number (Single) Prec.(MS Access) Varchar(12) (MS SQL Server) Text(12) (MS Access) Varchar(80) (MS SQL Server) Text(80) (MS Access) Example of an appropriate result-set: PI_TIMESTAMP1 PI_TAGNAME1 PI_VALUE1 20-Jul-2002 08:10:00 Target_Point1 1 PI_TIMESTAMP2 PI_TAGNAME2 PI_VALUE2 PI_STATUS2 20-Jul-2002 08:10:00 Target_Point2 10 NULL PI_TIMESTAMP3 PI_TAGNAME3 PI_VALUE3 PI_STATUS3 20-Jul-2002 08:10:00 Target_Point3 100 NULL 1 goes to Target_Point1; 10 to Target_Point2; 100 to Target_Point3 Note: See also section:.
Example 2.1a – insert sinusoid values into table (event based) SQL Statement (defined in file PI_SINUSOID_OUT.SQL) INSERT INTO T2_1a (PI_TIMESTAMP1, PI_VALUE, PI_STATUS) VALUES (?,?,?); Relevant PI Point Attributes Extended Descriptor Location1 Location2 Location3 Location4 Location5 P1=TS P2=VL P3=SS_I 1 0 0 0 0 Instrumenttag Point Type Source Tag Point Source PI_SINUSOID_OUT.SQL Float32 SINUSOID S RDBMS Table Design Table T2_1a PI_TIMESTAMPn PI_VALUE PI_STATUS Datetime (MS SQL Server) Date/Time (MS Access) Real (MS SQL Server) Single Precision (MS Access) Smallint (MS SQL Server) Whole Number (MS Access)
Example 3.4b – Tag Distribution, Search According to Tag's ALIAS Name SQL Statement (file PI_DIST2.SQL) SELECT TIME, PI_ALIAS, VALUE,0 FROM T3_4b WHERE TIME > ?; Relevant PI Point Attributes Tag Instrument tag Extended Descriptor Location1 Location3 Location4 Tag1 PI_DIST2.SQL P1=TS 1 -1 1 Tag2 /ALIAS=Valve1 1 1 Tag3 /ALIAS=Valve2 1 1 Tag4 /ALIAS=Valve3 1 1 RDBMS Table Data Table T3_4b Time PI_Alias Value 20-Oct-2000 08:10:00 Valve1 "Open" 20-Oct-2000 08:10:00 Valve2 "Closed" 20-Oct-2000 08:10:00 Valve3 "N/A"
Example 3.5 – Tag Distribution with Aliases in Column Names SQL Statement (file PI_DIST3.SQL) SELECT NAME AS PI_TAGNAME, VALUE AS PI_VALUE , STATUS AS PI_STATUS, DATE_TIME AS PI_TIMESTAMP FROM T3_5 WHERE NAME LIKE ?; Relevant PI Point Attributes Extended Descriptor Location1 All points Location2 All points Location3 Location4 All points Location5 All points Distributor – P1="Key_123%" Target points - /ALIAS='value retrieved from NAME column' 1 Not evaluated -1 Not evaluated 1 0 Instrumenttag Point Type (Distributor) Point Source S PI_DIST3.SQL Float32 RDBMS Table Design Table T3_5 DATE_TIME NAME VALUE STATUS Datetime (MS SQL Server) Date/Time (MS Access) Char(80) (MS SQL Server) Text(80) (MS Access) Real (MS SQL Server) Text(255) (MS Access) Real (MS SQL Server) Text(12) (MS Access)
Example 3.8 – Multi Statement Query SQL Statement (file PI_MULTI.SQL) INSERT INTO T3_8 (PI_TIMESTAMP, PI_VALUE, PI_STATUS) VALUES (?, ?, ?); DELETE FROM T3_8 WHERE PI_TIMESTAMP < ?; Relevant PI Point Attributes Extended Descriptor Location1 Location2 Location3 Location4 Location5 P1=TS P2=VL P3=SS_I P4=TS 1 0 0 0 0 InstrumentTag Point Type Source Tag Point Source PI_MULTI.SQL Float32 SINUSOID S RDBMS Table Design Table T3_8 PI_TIMESTAMP PI_VALUE PI_STATUS Datetime (MS SQL Server) Date/Time (MS Access) SmallInt (MS SQL Server) Number-Whole Number (MS Access) Smallint (MS SQL Server) Number Single Precision (MS Access)
Example 3.9 – Stored Procedure Call SQL Statement {CALL SP_T3_9(?,?)}; Stored procedure definition CREATE PROCEDURE SP3_9 @Start_Time DateTime, @End_Time DateTime AS SELECT PI_TIMESTAMP,PI_VALUE,PI_STATUS FROM T3_9 WHERE PI_TIMESTAMP BETWEEN @Start_Time AND @End_Time Relevant PI Point Attributes Extended Descriptor Location1 Location2 Location3 Location4 Location5 /SQL= "{CALL SP3_9(?,?)};" P1=LST P2=ST 1 1 0 1 0 InstrumentTag Point Type Point Source Float16 S RDBMS Table Design Table T3_9 PI_TIMESTAMP PI_VALUE PI_STATUS Datetime (MS SQL Server) Real (MS SQL Server) Smallint (MS SQL Server)
Tested RDBMSs RDBMS ODBC Driver Oracle (NT) Oracle ODBC Driver () 8.0.5.0.0.0 8.01.73.00 9.00.11.00 9.00.15.00 11.01.00.06 Microsoft ODBC Driver for Oracle ( see the latest MDAC) 2.573.6526.00 2.573.9030.00 2.575.1117.00 DataDirect () 4.10.00.4 8.0.5 (Oracle 8) 9.0.1 (Oracle 9i) 10.1 (Oracle 10g) 11.1 (Oracle 11g) Microsoft SQL Server ( see the latest MDAC) 03.70.0820 2000.80.194.00 2000.81.9031.14 2005.90.1399.00 6.01.7601 2009.100.2500.00 2011.110.2100.60 7.00 (SQL Server 7.0) 8.00 (SQL Server 2000) 9.00 (SQL Server 2005) 10.00 (SQL Server 2008, 2008R2) 11.00 (SQL Server 2012) DB2 (NT platform) 06.01.0000 07.01.0000 Informix (NT platform) 02.80.0008 2.20 TC1 07.31.0000 TC5 Ingres II (NT platform) 3.50.00.11 (Some tests FAILED!) Advantage Ingres Version 2.6 Sybase (NT platform) 3.50.00.10 12 ASE Microsoft Access 4.00.5303.01 4.00.6200.00 6.01.7601.17632 2000 2002 2003 2007 2010