Error: PKLess Table Received UPDATE Without Before-Image

Issue

The connection fails to sync a primary-key-less table and returns the following error:

[HVA] SegmentedInputStream error: PKLess table <table> received UPDATE without before-image.

Environment

Connector: High-Volume Agent Db2 for i

Resolution

To resolve this issue, ensure that the affected table is journaled with both before- and after-images:

1. On the Db2 for i system, run the following query to check the table's journal image configuration:

   SELECT
     OBJECT_LIBRARY,
     OBJECT_NAME,
     JOURNAL_LIBRARY,
     JOURNAL_NAME,
     JOURNAL_IMAGES
   FROM QSYS2.JOURNALED_OBJECTS
   WHERE OBJECT_LIBRARY = '<LIBRARY>'
     AND OBJECT_NAME = '<SYSTEM NAME FOR THE TABLE>';
   

2. Check the JOURNAL_IMAGES value. If the value is AFTER, the table is not configured correctly.
3. Update the table's journaling configuration to capture both before- and after-images. You can update either a single table or all tables in the schema.
   • To update a single table, end journaling and then restart it with IMAGES(*BOTH). For example, for table TAB1_00001 in schema Fivetran:

     ENDJRNPF FILE(Fivetran/TAB1_00001) JRN(Fivetran/QSQJRN)
     STRJRNPF FILE(Fivetran/TAB1_00001) JRN(Fivetran/QSQJRN) IMAGES(*BOTH)
     

   • To update all tables in the schema, use the CHGJRNOBJ command:

     CHGJRNOBJ OBJ((Fivetran/*ALL *FILE)) ATR(*IMAGES) IMAGES(*BOTH)
     

4. After updating the journaling settings, either manually trigger a sync or wait for the next scheduled sync.

Cause

This issue occurs when the affected primary-key-less table is journaled with after-images only. For tables without primary keys, we require before-image data to identify and process UPDATE operations correctly.