Relational Database(rdbms via odbc) Interface

Example 6.1 – Last One Hour of 'Sinusoid'

SQL Statement (file PI_IU1.SQL)
UPDATE PI_INSERT_UPDATE_1ROW SET PI_TSTAMP=?, PI_VALUE=?, PI_STATUS=?; UPDATE PI_INSERT_UPDATE RIGHT JOIN PI_INSERT_UPDATE_1ROW ON {Fn MINUTE(PI_INSERT_UPDATE_1ROW.PI_TSTAMP)}={Fn MINUTE(PI_INSERT_UPDATE.PI_TSTAMP)} SET PI_INSERT_UPDATE.PI_TSTAMP = PI_INSERT_UPDATE_1ROW.PI_TSTAMP, PI_INSERT_UPDATE.PI_VALUE = PI_INSERT_UPDATE_1ROW.PI_VALUE, PI_INSERT_UPDATE.PI_STATUS = PI_INSERT_UPDATE_1ROW.PI_STATUS;
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_IU1.SQL	Float16		SINUSOID	S
RDBMS Table Design
Table PI_INSERT_UPDATE_1ROW and PI_INSERT_UPDATE
PI_TSTAMP (PK)		PI_VALUE			PI_STATUS
Date/Time (MS Access)		Number Single Precision (MS Access)			Number Whole Number (MS Access)

Hints and Checklist

Hints for the PI System Manager

ORDER BY TIMESTAMP

When using the option to query a complete time series for a tag, the query must solve the problem that the value/timestamp pairs arrive ordered by timestamp. Otherwise the interface cannot perform exception reporting and the PI Server cannot do compression.

Reconnect to RDBMS

Reconnect attempts are modified to be more general. Only a few ODBC drivers report detailed error codes for networking problems. This was required for RDBMSPI Version 1.28 to reconnect (codes 08xxx (network problems) and xxTxx (timeout) were required). As a result, the interface reported an error (typically S1000) but did not reconnect (because S1000 is a general error).

Now, on any serious error the connection with the RDBMS is tested and the interface reconnects if necessary.

Suppress I/O Timeout

A common problem was the Relational Database was shutdown periodically due to backups. Since the interface then reports a connection problem (I/O Timeout gets written to all interface tags), queries with reference to previous timestamps only query back in time to the shutdown event. As a result, data was missing. In such a situation the startup parameter /NO_INPUT_ERROR can help.

Field Size (1)

If the field size is less than required for the current value to be passed, the interface prints an error message into the log file but continues to try on the next event with the value valid at that time.

For example, if the field length of a character field is 2 and the interface tries to store 'ON' and 'OFF' values, 'ON' will work, 'OFF' will generate an error.

Uppercase for Constant String

If the query contains a constant in the SELECT column list, and the constant is a string, some ODBC drivers transform this string to capital letters.

For example, SELECT timestamp,0,'No Sample' WHERE …

the 'NO SAMPLE' arrives in the PI part of the interface. Searches in the Bad and Good area are now case insensitive to address this problem.

Repeated Error Messages

Some error messages in the pipc log file are only displayed on first occurrence. To avoid log files to be filled with many duplicate messages, the interface only reports when the error is resolved. In the interface specific log file (/output=if_logfile) this feature is not implemented => e.g. ODBC runtime errors coming up in every scan may cause the log file growing infinitely.

Field Size (2)

The minimum field size for digital state output is 12 characters. Some ODBC drivers also require one additional character for the string termination byte (NULL). In this case the interface needs a minimum field size of 13 characters.

No Data

SELECT statements using LST or LET may not get any data if the clocks of PI System computer and RDBMS System are not synchronized. That is because LST and LET are filled from the interface but compared to RDBMS timestamps.

Login to PI

To avoid login problems (changed password, API 1.3.8 bug,…) OSIsoft recommends the setup of a trust/proxy for the interface. The interface was changed so it does not require an explicit login anymore (/user_pi now optional).