Verify Rows Filtering Expression return error "cannot parse expression", "cannot return as a Date"

Question

I've got the Infosphere CDC environment from DB2LUW to Microsoft SQL Server.

Because we had the disk space issue, we needed to filter replication; selected data will be replicated By Date column.

The source table insertion mode is APPEND, which rarely updates. During the creation of the subscription Filtering tab, when I run the verify button it returns an error like the below.

BUSINESS_DATE column data type is TIMESTAMP(0).
Error message details :
cannot parse expression : BUSINESS_DATE > '2017-12-18 00:00:00.0"
Cannot return as a date

I've read the documentation, but I cannot understand it, and there is no sample expression that fulfills my scenario. I am not sure why CDC cannot perform filtering using the timestamp data type column.

When I try to convert my timestamp column into char format, then CDC finds it is valid:

%TOCHARFORMAT(BUSINESS_DATE,'yyyy.MM.dd HH:mm:ss') > '2017.12.18 00:00:00'
%TOCHARFORMAT(BUSINESS_DATE,'yyyyMMdd') > '20171218'

As I don't have any development environment, I'm not convinced enough to run it.

I expect some performance issues, sinces every row of (business_date) will need to be converted in the first place, before comparing to the filtering condition.

At the end, my table mapping is filtered by another column (bigint data type). I'm just curious why the refresh process does not seem to be using any index for filtering rows (the query that CDC runs in the source database is not using any filter), although it helps to accelerate the refreshing, because not every data will need to be inserted to the target table.

Can CDC perform filtering using the timestamp data type column? Any suggestions?

Answer 1

I think the problem with your original filter expression was that the literal was evaluated as a character data type. I suppose you cannot perform comparisons between different data types by doing some casting.

Regarding the refresh, CDC always performs a full table scan. I imagine the reason is that the row filtering could include tests that cannot be supported by any sort of SQL where clause, including user exits and journal control columns and because when it is scraping the transaction log, it is not using any filtering supplied by the database, so this avoids any issues where there is a mismatch between filtering logic in the refresh and in mirroring, which could occur if refresh used a database filter and mirroring used a native filter.