How ER2 Scans Databases

How ER2 scans databases is dependent on several factors, including (but not limited to) the database type, and the presence of primary key (PK) / unique index columns.

For certain databases, ER2 defaults to the offset-limit approach to iterate through all table rows, using the table's (sorted) PK or unique index column for pagination.

The Oracle database module fully supports tables with primary or unique keys defined by two or more columns.
For other database Targets, if the offset-limit approach is used on tables with primary keys or unique indexes defined by two or more columns, some rows may be skipped during the scan.

For databases such as IBM DB2, IBM Informix, InterSystems Caché, SAP HANA, Sybase/SAP Adaptive Server Enterprise, Tibero, and Oracle, by default ER2 performs unbounded queries to retrieve data during scans. However, in scenarios where the buffer limit for the Proxy Agent is not sufficient to store the retrieved data for the whole table, and the table has either a PK or unique index column, ER2 uses the offset-limit approach instead.

The scanning approach may differ for databases in certain conditions. For example, unbounded queries are used for Microsoft SQL databases when no PK or unique index columns are defined, and for Teradata databases when the FastExport utility is available. For Oracle databases, ER2 limits the number of rows being queried when the pagination option is enabled.

In instances where both the unbounded query and offset-limit approaches are not possible, ER2 only scans the first N number of rows in a database table.

N may vary across tables as the row size (as determined by column types) impacts the number of rows that can fit in the Proxy Agent's buffer limit.

To add and scan database Targets, see Databases.