Operating Modes
The ODBC Connector has several modes of operation.
Non-Incremental Mode
In non-incremental mode, the connector performs a full synchronize every time you run a synchronize task. This is the default mode, used when UseIncrementalStatements=false
.
Every time you run a synchronize task, the connector retrieves information from the database using the query specified by the SQL
parameter. To identify new, updated, and deleted rows, the connector must process every row returned by this query.
The SQL query might return many rows that the connector has processed before, and that have not changed. The connector doesn't need to take any action for these rows but processing them does take time, especially if the query returns millions of rows.
Running a full synchronize on every cycle might be necessary if it is not possible to construct a query to extract only the new, modified, and deleted rows.
Incremental Mode
In incremental mode, the connector performs a full synchronize on the first cycle, and an incremental synchronize on subsequent cycles. This can be more efficient.
The first time the connector runs a synchronize task it sends the query specified by the InitialStatement
parameter to the database and processes all of the rows that are returned. As the connector hasn't run the task before, all of the rows represent new documents and the connector sends an ingest-add command to the ingestion server (usually, a Connector Framework Server) for every row. This is a full synchronize, the same as in non-incremental mode.
TIP: If you do not set the InitialStatement
parameter, the connector uses the query specified by AddedAndUpdatedStatement
for the initial synchronize.
On subsequent synchronize cycles, the connector sends different queries to the database. The query specified by AddedAndUpdatedStatement
is used to identify rows that have been added or updated. The query specified by DeletedStatement
is used to identify rows that have been deleted.
An example AddedAndUpdatedStatement
might be:
SELECT * FROM table_name WHERE last_modified > '@lastruntime'
The connector replaces the placeholder @lastrunime
with the time of the last successful synchronize.
An example DeletedStatement
might be:
SELECT * from table_name WHERE deleted=1
If your database stores items by modified date and identifies deleted rows, using incremental mode can increase performance because the incremental queries are likely to return fewer rows than the initial query. Also, in incremental mode the connector does not need to store information in its datastore about the rows that it has retrieved.
Event Table Mode
In event-table mode, applications add rows to an event table. On each synchronize cycle, the connector runs a query to extract documents from the table, processes the documents that are returned, and then deletes the rows that it has processed.
In event-table mode, use the SQL
configuration parameter to specify a query to use to retrieve information from the event table, and the EventTableType
parameter to specify what type of operation to perform on the returned documents. You must also set the DeleteEventSql
parameter to specify the query to use to delete rows from the event table after the information has been ingested. For example:
[EventTableAdd] ConnectionString=... EventTableType=add SQL=SELECT id, metavalue, content FROM event_adds DeleteEventSql=DELETE FROM event_adds WHERE @Clause PrimaryKeys=id Template=document.tmpl
The connector replaces the placeholder @Clause
with a clause that identifies the rows that have been ingested.
To handle new, modified, and deleted documents, configure three separate fetch tasks (one with EventTableType=add
, one with EventTableType=update
, and one with EventTableType=remove
).