How Does the High-Volume Agent Db2 for I Connector Detect Primary Keys?

Question

How are primary keys detected for High-Volume Agent Db2 for i tables?

Environment

Connector: High-Volume Agent Db2 for i

Answer

Fivetran first checks whether the table has a declared primary key constraint. If the table doesn't have a declared primary key, we use an eligible unique index as the table key. An eligible unique index is one that passes all of the following checks:

• It's a table index — not a clustered or other index kind.
• It's unique — no duplicate key values allowed.
• It's non-partial — it covers all rows, not a filtered subset based on select/omit conditions.

If multiple eligible unique indexes exist, Fivetran sorts eligible unique indexes by index_name, index_qualifier and ordinal_position. We then select the first one as the table key.

If we can't find a declared primary key or eligible unique index, we use the _fivetran_id system column as a synthetic key. The _fivetran_id value is generated from the row's non-Fivetran column values.