Campaign Classic - Incremental Query picks up all records instead of only new ones

Description

The customer has several incremental queries which are not working as expected. Instead of only picking up new records since their the last run, they are picking up all records each time - like a normal query activity.

Resolution

The culprit is the cleanup workflow.

The incremental query workflow works this way:

  1. Maintain a history table with results from previous iterations.
  2. Fetch ALL the rows from the target query.
  3. Filter out all the rows present in the history table
  4. Add the remaining results into the history table for the next iteration filtering.

So, this history worktable name is of the following notation:
wkfhistoworkflowidactivityName_

Now, for workflowIDs 0 (for customers where the xtknewid allows negative sequences), we see that it is actually:

wkfhisto(uint)workflowidactivityName_

Although this is okay for workflow execution.

So, for example, the incremental activity incremental1 of workflow ID=-1 will create a table wkfhisto4294967295_incremental1.

The thing which is missed is the CleanUp workflow.

Here, we have a code that tries to delete worktables of deleted workflows.

A dedicated code here lists all the wkfhisto* tables, extract out the workflowId from their names (from the above convention), and deletes them all except the ones whose worklowIDs are found in the xtkworkflow table.

However, it misses the uint part.

So, it tries to look up a workflow with ID 4294967295 instead of casting this back to int. Since this workflow is not found, this table is deleted. Next time, when this workflow runs, the incremental query activity does not find an existing history table and creates it thinking of this as the first run ever.

Fix:

The fix for this issue is available in Adobe Campaign Classic 20.1.1 Release (build 9122 and onwards).

Workarounds that customers can use:

Workaround 1: Stop the cleanup workflow and run it intermittently to cleanup the Database and HDD until the fix is taken and available. Not recommended if you don’t have a planned upgrade.

Workaround 2: Assume that the incremental Query activity is impacted and workaround it by doing the same thing that the incremental Query does by creating a persistent schema to hold the history table contents. Use a combination of query and update data activities to mimic the behavior. This will need to be done for all the workflows requiring the incremental query.

Workaround 3:  Assume that the incremental Query activity is impacted and work around it by adding an audit field (tsCreated/tsLastModified) to the schema in question. Your incremental query will then be converted to a normal query activity with a where clause like tscreated GetDate().

Workaround 4:

  • Create a new sequence xtknewworkflowid and initialize it to something that is far away from the current workflowId ranges.
  • Change the xtkworkflow schema to use this pkSequence
  • Ask the customer to clone all the affected workflows and delete the original ones.
  • Once the customer is ready for an upgrade, remove this fix by reverting to xtknewId for the workflow creation (to avoid unwanted surprises).

On this page