calculated field coming null in beforeRecord

[RajneeshSharma]

We have an application that reads cdc data from IBM DB2 source and writes to kafka target. There is a calculcated field defined for a table in table settings. We noticed that that calculated field value is null in target beforeRecord={} dataset but does the expected calculation in record={} dataset. Is this expected behaviour. Is there anyway to force it to calculate in beforeRecord too.

[Heinvandenheuvel]

Is this a calculated/virtual  field on the source DB, or are you referring to a what Replicate calls a 'transformation' column?

For a source virtual column there is no way for Replicate CDC to get the values. The Replicate CDC input is the DB Journal (Redo, Tlog, whatever it is called for the DB in question). Such journal is maintained by the source DB just for its own needs, notably to roll-back, or re-apply. not for CDC products. As such there is no need for the source DB to store calculated columns and hence that data is not available to Replicate (or any other any) CDC. Makes sense?

The Replicate User Guide typically documents for this under limitations for several endpoints. I don't see it mentioned under DB2 as source, so maybe a formal support case is useful which no doubt will result in a documentation update, not a 'fix'.

Hein

[RajneeshSharma]

I am referring to transformation column

[john_wang]

Besides @Heinvandenheuvel , what's the DB2 type? DB2i, DB2z, or DB2 LUW? Maybe we can confirm for you if you can share the DB type and table sample, also include your calculated column expression.

Regards,

John.

[Heinvandenheuvel]

>> I am referring to transformation column

Ah, i'm not sure. I should try but I will not right now. I'm not entirely surprised if the before image is just about what comes from the DB CDC source - no transformation, but thought I had seen the transformations in 'STORE CHANGE' tables.

Also, if you share the expression used, maybe someone can spot an issue with it.

For the general question you may want to open a support ticket and possibly an enhancement reqest.

For a specific issue, where there are a few critical fields where transformations on before columns images are desired, you could potentially stick them in the target data using with the same expression but based on $BI__<columnname> source.

From the doc: "Using a column's before-image data in a transformation
You can use a column's before-image data in a transformation. This is useful if you need to store the beforeimage data on the target. To do this, simply specify the source column name in Output table's Expression column, in the following format: $BI__MyColumn"

So you might ADD TWO COLLUMNS instead of 1, or just ADD the 1 column for the expression based on the before-image.

COMP_VALUE_NEW: expression...$MyColumn

COMP_VALUE_OLD: expression...$BI__MyColumn

Hein