Configuring partial resynchronization

The probe can perform a partial resynchronization by selecting all active alarms that have not yet been retrieved. This resynchronization is based on a timestamp associated with the alarms.

To enable the probe to perform a partial resynchronization, you must specify an appropriate column from the source database to act as a marker using the MarkerColumn property.

Before deciding which marker column to select, bear in mind the following guidelines:

  • The column that you specify must be either an integer or a unix timestamp.
  • The column should be an incremental row indicator or an incremental timestamp in the table.
  • If the source database is not ordered by the column that you specify, you must order the records retrieved by this column.
  • You must either include the column explicitly in the SELECT statement specified by the SelectSqlFile property, or it must be selected by a wildcard within the SELECT statement, for example:

    SELECT * from TABLE table_name

    For details about specifying the SELECT statement, see Handling open-form SQL statement queries to retrieve data from the event source.

  • Ensure that the marker column is unique and do not duplicate the name in query SQL.
  • Do not use comments /* */ inside the SELECT query.
  • Define only one SQL query in the SQL file.
  • Define only one marker indicator within the SQL query.
  • Define only one marker column.
  • If you need to configure a marker column as a conversion (using CONVERT, CAST, DATEDIFF, or DATEADD), you must use it with the AS keyword; for example:

    CONVERT(int, marker) AS marker_column

The probe can be configured to perform a partial resynchronization based on the last resynchronization marker stored in a data backup file. To do so, the probe uses the DataBackupFile property and the MarkerColumn property together. The probe records the last read alarm (as defined by the marker column specified by the MarkerColumn property) in the file specified by the DataBackupFile property. Before performing a resynchronization, the probe reads the data backup file and retrieves only those alarms that have been created since the previous resynchronization. If the data backup file is empty (as it will be during the initial run of probe), the probe will do a full resynchronization. If a marker column has not been specified, the probe ignores the DataBackupFile property.

Example configuration 1: Using a simple SELECT statement

By default, the probe will add a where clause for the marker column to the end of the mandatory SELECT statement.

For example, suppose the file specified by the SelectSqlFile property contains the following SQL command:

SELECT * from SensorData1

and the MarkerColumn property is set to SensorDataRowID

The probe will execute the following SQL command:

SELECT * from SensorData1 WHERE SensorDataRowID > ?

Where ? is a dynamic value retrieved from the last resynchronization cycle. If there have been no resynchronization cycles yet, the probe sets ? to 0.

Example configuration 2: Using a SELECT statement that contains a WHERE clause

If you are using a query that already contains a where clause, the probe will add a where clause for the marker column to the end of the mandatory SELECT statement.

Suppose the file specified by the SelectSqlFile property contains the following SQL command:

SELECT * from SensorData1 WHERE AlertPriority > 2

and the MarkerColumn property is set to SensorDataRowID

The probe will execute the following SQL command:

SELECT * from SensorData1 WHERE AlertPriority > 2 AND SensorDataRowID > ?

This will work in the same way as Example 1, but will only include alerts whose priority is greater than 2.

Example configuration 3: Using a marker indicator with the SELECT statement

If you are using a more complex query in which a where clause for the marker column cannot be added to the end of the query, you must include the indicator ::marker_column in the SELECT query. This indicates to the probe where the where clause should be expanded.

For example, suppose the file specified by the SelectSqlFile property contains the following SQL command:

SELECT sp.sup_name, sp.street, sp.city, sp.zip, sp.sup_id FROM suppliers sp ::marker_column order by sp.sup_id

and the MarkerColumn property is set to sp.sup_id

The probe will execute the following SQL command:

SELECT sp.sup_name, sp.street, sp.city, sp.zip, sp.sup_id FROM suppliers sp WHERE sp.sup_id > ? order by sp.sup_id

Example configuration 4: Using a more complex SELECT statement

Suppose the file specified by the SelectSqlFile property contains the following SQL command:

SELECT a.name, a.id, b.salary, b.increment FROM employee a, emp_salary b WHERE a.id = b.id ::marker_column AND n.name is NOT NULL order by a.id

and the MarkerColumn property is set to b.salary

The probe will execute the following SQL command:

SELECT a.name, a.id, b.salary, b.increment FROM employee a, emp_salary b WHERE a.id = b.id AND b.salary > ? AND a.name is NOT NULL order by a.id

Note: You must place the marker indicator within the SQL statement in a location that will produce valid SQL when the WHERE clause is expanded. That location will be either directly after the WHERE keyword or after a completed WHERE clause. For example:

SELECT * from SensorData1 WHERE ::marker_column ObservanceID > 0 order by SensorDataRowID

or

SELECT * from SensorData1 WHERE ObservanceID > 0 ::marker_column order by SensorDataRowID

Example configuration 5: Converting a column to unix timestamp format

If you want to select a DateTime field in the source database as the marker column, you must convert it into unix timestamp format using the AS keyword within the SELECT statement.

For example, suppose you are using MySQL and the file specified by the SelectSqlFile property contains the following SQL command:

SELECT unix_timestamp(ts) AS timex, no_id from T1

Where the ts column is of type DateTime in the source table, and the unix_timestamp() function is converting this column to unix timestamp format.

Note: If you are using a database other than MySQL, you may need to use a different SQL conversion function. See the documentation supplied with your database for details.

The MarkerColumn property should be set to unix_timestamp(ts) AS timex.

The probe will execute the following SQL command:

SELECT unix_timestamp(ts), no_id from T1 WHERE unix_timestamp(ts) > ?

Where ? is a dynamic value retrieved from the last resynchronization cycle or from the recovery file specified by the DataBackupFile property.

Example configuration 6: Converting a column using DATEDIFF

You can convert a marker column using the DATEDIFF function.

For example, suppose the file specified by the SelectSqlFile property contains the following SQL command:

SELECT *, DATEDIFF(s, '19700101', AlertDateTime) AS AlertDateTime from SensorData1

The MarkerColumn property should be set to “DATEDIFF(s, '19700101', AlertDateTime) AS AlertDateTime”.

The probe will execute the following SQL command:

SELECT *, DATEDIFF(s, '19700101', AlertDateTime) AS AlertDateTime from SensorData1 WHERE DATEDIFF(s, '19700101', AlertDateTime) > ?

Where ? is a dynamic value retrieved from the last resynchronization cycle or from the recovery file specified by the DataBackupFile property.

Other examples

Suppose the MarkerColumn is set to: “a_emp_id”

You could use the following SQL command in the file specified by the SelectSqlFile property:

“SELECT a.emp_id as a_emp_id, b.emp_id as b_emp_id FROM table a, table b WHERE a.class_id = b.emp_id”

But you could not use following SQL command in the file specified by the SelectSqlFile property:

“SELECT a.emp_id, b.emp_id FROM table a, table b WHERE a.class_id = b.emp_id”

Suppose the MarkerColumn is set to: “DATEDIFF(s, '19700101', sd.AlertDateTime) AS AlertDateUnixTime”

You could use following SQL command in the file specified by the SelectSqlFile property:

“Select DATEDIFF(s, '19700101', sd.AlertDateTime) AS AlertDateUnixTime, sd.AlertDateTime from SensorData1 sd”

Suppose the MarkerColumn is set to: “DATEDIFF(s, '19700101', sd.AlertDateTime) AS AlertDateTime”

You could not use following SQL command in the file specified by the SelectSqlFile property:

“Select DATEDIFF(s, '19700101', sd.AlertDateTime) AS AlertDateTime, sd.AlertDateTime from SensorData1 sd”