Enabling Pause/Resume on a Database Source
-
In the SQL query, add an ORDER BY on the chronological fields of the SELECT statement:
-
If the source supports Incremental Refresh
-
The same chronological fields must also be used in the ORDER BY.
-
If the source does not support Incremental Refresh
-
-
Field that uniquely identifies each record:
If the primary key contains only one field, select that field. However, if there is a column that contains a sequential number incremented by the DBMS each time a new record is added, select that column instead.
-
No field that uniquely identifies each record:
In this case, any field can be used. However, during a resume, records that have been indexed before pausing could be re-indexed.
Example: You have a table containing the 12 months of the year and you select the month column as the reference. The values for this column are not unique, as many rows can be associated to the same month. While crawling the 6th month, you pause, therefore not indexing all the rows for the 6th month. When you resume, the connector will start crawling at the first row of the 6th month, re-indexing the rows that have already been indexed.
-
-
-
Add XML attributes on the Accessor element in the configuration file:
-
OrderByFieldName
-
Specifies the name of the column on which the ORDER BY is applied. This attribute must be present to enable Pause/Resume.
-
OrderByFieldType
-
Specifies the .NET data type of that column. This attribute is not normally required. The connector automatically tries to determine the data type by preparing the SQL query - without however executing it - and looking at the schema of the results. However, if a specific DBMS does not handle that process correctly, you can manually specify the data type with this attribute. The following lists the allowed types:
-
short (16-bit signed integer)
-
ushort (16-bit unsigned integer)
-
int (32-bit signed integer)
-
uint (32-bit unsigned integer)
-
long (64-bit signed integer)
-
ulong (64-bit unsigned integer)
-
float (single-precision floating point number)
-
double (double-precision floating point number)
-
string (String)
-
- IncrementalRefreshFieldName
-
To support both Incremental Refresh and Pause/Resume, IncrementalRefreshFieldName must contain the same name as OrderByFieldName.
Refer to this example for an excerpt of a configuration file for a source with Pause/Resume and Incremental Refresh enabled:
Note: You must replace [PARAMETER] by @LastRefresh in an SqlClient scenario or by ? otherwise.
<Mapping type="Orders"> <Accessor type="query" OrderByFieldName="OrderDate" OrderByFieldType="DateTime" IncrementalRefreshFieldName="OrderDate"> SELECT Shippers.CompanyName AS ShipperName, Orders.OrderID AS ID, Orders.CustomerID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Customers.CompanyName, Employees.LastName, Employees.FirstName FROM Orders, Shippers, Customers, Employees WHERE Orders.ShipVia = Shippers.ShipperID AND Orders.CustomerID = Customers.CustomerID AND Orders.EmployeeID = Employees.EmployeeID AND Orders.OrderDate >= [PARAMETER] ORDER BY Orders.OrderDate </Accessor> </Mapping>
Note: For backward compatibility, the equivalent LiveIndexingFieldName parameter from previous CES versions is still supported in CES 7.
-