Relational Database(rdbms via odbc) Interface

Recovery SHUTDOWN

Shutdown recovery is the same as 'TS', if the output tag's snapshot value is either Shutdown or I/O Timeout. If the output tag snapshot does not contain these digital states, NO recovery takes place.

Note: Shutdown recovery exists for compatibility reasons to earlier interface versions. It is recommended to use TS recovery instead.

Interface in Pure Replication Mode

Input Recovery

When the recovery time-window definition contains both; that is, the start and the end-times (separated by comma); for instance, /RECOVERY_TIME = "01-Jan-08,01-Jan-09" all input points will be processed on the defined time interval and then the interface will end (the interface process will exit).

Output Recovery

During recovery, the interface retrieves and reprocesses the compressed data from the PI Archive (as opposed to executing the output points' events coming from the event queue during the interface's normal operation). When the recovery time-window does contain both; that is, the start and the end-times (separated by comma) for example, /RECOVERY_TIME = "*-1d,*" all output points will be processed for the defined time interval and then the interface stops (exits).

In the Pure Replication Mode one can schedule the interface execution via the Windows scheduling service (AT) and let the PI Archive (compressed) data replicate in a batch manner.

Note: Due to the different nature of both recovery modes, it is not recomended to run input and output recovery with one interface instance!

For exact specification of all recovery related parameters, see section Startup Command File.

13. Automatic Reconnection

ODBC Connection Loss

The interface automatically tries to re-connect the RDB in cases when the relational system is not reachable. Because the ODBC API does not provide any direct function to find out whether the communication line is in a healthy and sound state, the interface uses the following mechanism to determine a connection loss:

Any connection related error (ODBC returns error statuses starting with 08xxx) means closing all prepared SQL statements and entering the re-connection loop. Before regarding the situation as a connection loss, an additional verification execution is made. The result of this verification finally decides about the re-connection action. According to the ODBC specification, ODBC drivers have to stamp the errors consistently and communication related problems have to be marked with a proper error state. As different ODBC drivers can return thousands of error codes, it is unlikely that each error code is properly marked. Since version 3.11, the interface implements a new start-up switch /ERC=n. This optional switch activates a mechanism, which counts consecutive occurred runtime errors, and decides for the re-connection action when the number of such errors reaches the specified number (n). This scenario helps to decide about a re-connection action when the interface communicates to an ODBC driver that does not return proper error codes.

Note: The interface tries to re-create the ODBC link every minute. This time interval is hardcoded and cannot be changed.

Note: In version 3.12 and higher (of the PI RDBMS Interface), for the output tags, the placeholder values are retained and the query, which discovered the broken ODBC link is executed again when the connection to RDB is re-established.

Note: During the re-connection attempts (1 min intervals) the interface does NOT empty the update-event queue (for output tags). Some events can thus be lost due to the queue overflow. Should such a situation happen, there is currently NO automatic recovery action taken. Only a manual solution is possible – set-up the corresponding /OOO_OPTION recovery parameters, and re-processes the period when the interface was disconnected from the RDBMS by restarting the interface. See section RDBMSPI – Output Recovery Modes (Only Applicable to Output Points). See the PI Server Manual for details how the event queue size can be increased.

When the ODBC link is broken, and the PI System remains available, the interface normally writes the I/O Timeout digital state to all input points. This can be avoided by setting the interface start-up parameter /NO_INPUT_ERROR.

PI Connection Loss

During the PI API or PI SDK connection loss, neither the snapshot placeholders (TS, VL, SS_I,…) nor the attribute placeholders (AT.xxx) can be refreshed. Corresponding error messages are sent to the interface log-file and the interface enters a loop where it tries to re-connect to PI in one minute intervals. The PI Server availability check is made before each scan class processing.

Note: In case the interface runs as a console application (and there are the /user_pi= or/and /pass_pi= startup parameters specified), the login dialog pops up waiting for the user to re-enter the authentication information.

14. Result Variables

Send Data to PI

The interface sets the following (ransfo) variables according to the result of the write-to-PI action: @write_success and @write_failure.

A failure sets the @write_success to False, the @write_failure to True and vice-versa. Both variables are accessible to users, as indicates the example below:

SELECT Timestamp, Value,0 FROM Table WHERE Timestamp > ? ORDER BY Timestamp;

IF @write_success DELETE FROM Table WHERE Timestamp <= ?;

That means, the rows in the first table can be safely deleted, because they were already copied to PI.

Note: Only if ALL SELECTed rows are successfully sent to the corresponding PI tags then the @write_success variable is true. Data that have no corresponding PI tag (e.g. in the Tag Distribution strategy there is a row that references a nonexistent tag and this row thus cannot be sent to PI), do not count as a failures. To achieve this, consider the @rows_dropped variable in section SQL SELECT Statement for Tag Distribution.

Note: @write_success and @write_failure are undefined before the first SELECT or {CALL …} command and they are set to the undefined state always before the query execution. That means that they can only be evaluated when placed after a query. It also only makes sense to place them after SELECT or {CALL …}; that is, after queries that return a result-set.

Note: The implemented IF does NOT support the ELSE part and only covers one statement after the variable.