Enabling Pause/Resume on a Database Source

The Database connector can support the Pause/Resume parameter if the following is added to your configuration file:

1. 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.

2. 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.