Manipulating data in an SQL Replication environment

You can transform or enhance your source data before it is replicated to the target tables.

You can manipulate data either before or after it is captured. Manipulate your data at registration instead of at subscription if you want to manipulate the data once and replicate transformed data to many target tables. Manipulate your data during subscription instead of registration if you want to capture all of the source data and selectively apply transformed data to individual targets.

In some replication scenarios, you might want to manipulate the content of the source data that is stored in the CD table. A trigger, an expression through the subscription, or a source view can all be used to get the same job done. Each method has its pros and cons. A trigger might be too costly in terms of CPU cycles used. A view lets you set up the function once rather than in multiple subscriptions.

CREATE TRIGGER ENHANCECD
NO CASCADE BEFORE INSERT ON CD_TABLE
REFERENCING NEW AS CD
FOR EACH ROW MODE DB2SQL 
WHEN (CD.COL1 IS NULL)
SET CD.COL1 ='MISSING DATA'
END

CREATE VIEW SAMPLE.SRCVIEW  (columns) AS SELECT 
       ... COALESCE(A.COL1, 'MISSING DATA') ...
       FROM SAMPLE.TABLE A

COALESCE(CD.COL1, 'MISSING DATA')