Relational Database(rdbms via odbc) Interface

RDBMSPI – Input Recovery Modes

The primary task of the RDBMSPI interface is on-line copying of data from relational databases to the PI archive. For this, users specify SQL queries (mostly SELECTs) and the task of the interface is delivering the newly stored rows to PI tags. On the other hand, history (input) recovery means copying bigger amounts of data (from RBDs or other historians) to PI. This task is usually not periodical; that means, it is one-time action only. The interface must thus address different issues; mainly, divide the time interval, for which the data needs to be copied, into smaller, configurable chunks. There are many reasons for it, above all, avoid higher memory consumption, improve performance and increase the robustness of the recovery process. In the following paragraphs we will describe the settings, which the interface (since version 3.17) supports.

In the simplest possible scenario, the history recovery is actually covered by the most common query customers have:

SELECT Timestamp,Value,0 FROM Table WHERE Timestamp > ? ORDER BY Timestamp ASC; P1=TS

Provided the amount of data in RDB between the snapshot and the current time is of reasonable size, the query above simply fills in the missing events in PI archive during the first query execution. The interface will then continue executing the SELECT (in on-line mode) and the query will return only the newly inserted rows.

As stated at the beginning of this section, in case the amount of data in RDB is big, it is desirable to divide the time interval into chunks in order to avoid potential high resource utilization (CPU, memory, etc.) on the interface node as well as on the RDB side. For this, the interface offers two switches: /RECOVERY_TIME and the new start-up parameter /RECOVERY_STEP. Both parameters accept various input formats.

Their definitions and short description can be found in the following table:

Input History Recovery startup switches and their definitions

	RECOVERY_TIME definitions	Example	Description
1	Absolute start time	/RECOVERY_TIME= 01-Jan-00 /RECOVERY_STEP=30d	Recovery will start at the given time, will process the time interval in 30 days chunks, and, when the current time is reached, the query will continue execution in the standard on-line mode.
2	Absolute start time, absolute end time	/RECOVERY_TIME= 01-Jan-00,01-Jan-09 /RECOVERY_STEP=30d	The same as above, but when the end time is reached the query will NOT continue in on-line mode. In fact, after all input tags will be processed, the interface will exit.
3	Relative start time	/RECOVERY_TIME= *-365d /RECOVERY_STEP=1d	The same as #1, start times is expressed in relative times.
4	Relative start time, relative end time	/RECOVERY_TIME= *-365d, * /RECOVERY_STEP=1d	The same as #3. After processing all tags the interface will exit.
5	Name of the timestamp tag. The snapshot value of this tag will be used as the start time.	/RECOVERY_TIME= RDBMSPI_RecovertTagStart /RECOVERY_STEP=24h	The start time can be passed through a standard PI tag, (of the type Timestamp). The interface will read the snapshot value of this referenced tag and, after each execution, it will store the just processed start time to this tag. This allows for starting the recovery from the last successfully executed bulk.
6	Name of the timestamp tag, Name of the timestamp tag The snapshot values of these tags will be used as the start time.	/RECOVERY_TIME= RDBMSPI_RecoveryTagStart, RDBMSPI_RecoveryTagEnd /RECOVERY_STEP=1d	The same as #5. After processing all tags on the given interval the interface will exit.

Note: Valid start and end time definition syntax used in the /RECOVERY_TIME keyword are strings, which represent:
- absolute times containing some fields of DD-MMM-YY hh:mm:ss
- relative times in +|- n d|h|m|s
- names of the PI tags
In addition, an absolute time can be specified with a word (TODAY, YESTERDAY, SUNDAY, MONDAY,…), an asterisk for the current time, or a combination time using one of the word absolute times and a relative times. See the Data Archive Manual for more information on the time string format.
See also the description of /RECOVERY_TIME and /RECOVERY_STEP in section Command-Line Parameters.

A suitable SQL statement (for the input history recovery) must be of the following pattern:

SELECT Timestamp, Value, 0 FROM Table
WHERE Timestamp > ? AND Timestamp <= ?
ORDER BY Timestamp ASC;
P1=TS P2=TE

That is, a query, which allows binding the start and end times recovery steps, is expected. That does not mean the query must be exactly as stated above. In fact, it can be any query, which delivers suitable result sets, but it must contain at least two timestamp placeholders defined by TS and TE. The query above actually resembles the most often used type of an SQL statement, which delivers ordered time series since the last scan.

Provided the /RECOVERY_TIME and /RECOVERY_STEP are specified, the interface will automatically populate the placeholders with appropriate times and will incrementally process the historical data. When the end time is reached, the interface process will exit. Exiting occurs when the /RECOVERY_TIME contains also end time.

Configuration Example for Input History Recovery

Interface startup file:

RDBMSPI.exe /PS=RDBMSPI /F=10 /DSN=SQLServer /lb ... /RECOVERY_TIME=”01-Jan-05,*” /RECOVERY_STEP=10d /RECOVERY=TS

SQL Query (using the distributor strategy):

SELECT Timestamp, Name, Value, 0 FROM Table WHERE Timestamp > ? AND Timestamp <= ? ORDER BY Timestamp ASC; P1=TS P2=TE

Explanation:

After the interface starts, all input points’ queries will be executed on the interval beginning 01-Jan-2005 till current time. The recovery step will be 10 days. That is, the placeholders will be populated as follows:

1. Step: TS=01-Jan-2005 00:00:00 TE=10—Jan-2005 00:00:00

2. Step: TS=10-Jan-2005 00:00:00 TE=20—Jan-2005 00:00:00

3. …

When the current time is reached, the interface process will exit. The interface specific log will contain the following printout:

[INFO]: Input recovery on the interval

<01-Sep-2009 00:00:00.000 , 22-Oct-2009 10:27:46.000>
with step 864000 sec started.

[DEB-1]: Point – Recovery_Distributor : SQL statement(s) : SELECT DateTime AS PI_TIMESTAMP, 'Recovery_Target_1' AS PI_NAME, value AS PI_VALUE, 0 AS PI_STATUS FROM History WHERE DateTime > ? AND DateTime <= ? ORDER BY DateTime;

[INFO]: Processing the input recovery interval
<01-Sep-2009 00:00:00.000 , 11-Sep-2009 00:00:00.000>.

[INFO]: Processing the input recovery interval

<11-Sep-2009 00:00:00.000 , 21-Sep-2009 00:00:00.000>.

…

[INFO]: Processing the input recovery interval

<21-Oct-2009 00:00:00.000 , 22-Oct-2009 10:27:46.000>.

Thu Oct 22 10:28:02 2009 [INFO]: Input recovery completed.

Thu Oct 22 10:28:02 2009 [INFO]: Interface exiting.

12.