Issue

My customer is capturing transactions from a MySQL database to a target data warehouse. They want the source production database to contain only the most recent month or years data while target can store historical data. This means when activity to purge occurs on the source, the old data is deleted from it but how can Local Data Processing filter the delete transactions and not delete them on the downstream data warehouse just on the source?

Usually, we can do it for an Oracle source with Capture using the /IgnoreSessionName, but it does not work on MySQL. Is there any other solution can make it?

Environment

Local Data Processing

Resolution

We can do this for MySQL database defining the action on the Source to ignore deletes.

/CaptureCondition="{hvr_op}!=0".

Deletes are not captured.

Interestingly, if you put it on the Integrate side as a Restrict action with '/IntegrateCondition="{hvr_op}!=0″, you will get an error:

o2o_nodelfil-integ-otgt: F_JD223D: Unknown variable {hvr_op} encountered while parsing SQL expression '{hvr_op}!=0'.

Note: The value for hvr_op values are 0, 1, 2, 3, 4, 5 (which indicate the operators: delete, insert, after row update, before the key update, before the non-key update, truncate respectively). This value cannot be transformed and/or post transformation such as values like D, I, UA, etc.

Oracle Source

Alternatively, if your Source was Oracle you could create a new user, i.e., 'purgeadmin' and connect to the database and execute the purge/delete routine as this user with the below modification to the /Capture /IgnoreSessionName=*purgeadmin* .

I.e,

1. Create a user PurgeAdmin on source that will run this purge script.
2. Added to /Capture action, /IgnoreSessionName=*purgeadmin* &
3. Initialize scripts & jobs for all tables
4. Stop and restart jobs
5. Then a ll actions will be ignored that were committed by the user 'purgeadmin'