ROUTER_EXPRESSION

[Osimar Medeiros]

Hi experts,

I am having some issues in using ROUTER_EXPRESSION for a table I am syncing.

SymmetricDS version : 3.7.0

Please bear with me as I try to articulate my issue.

I am syncing several tables. I took one of them to test this router_expression usage.

So, my intention is : during initial load [reload-table command] and during day-to-day execution, I would expect it not to bring entries from source table that have NULL value in a single column [DTPAG] to target table [exact same structure both target and source tables].

So, here's what I've done :

my table's name is : PCPREST
my router_expression column is : DTPAG
my router_expression is : DTPAG IS NULL for INSERTS only

INSERT INTO SYM_ROUTER (ROUTER_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, ROUTER_TYPE, CREATE_TIME, LAST_UPDATE_TIME, USE_SOURCE_CATALOG_SCHEMA, ROUTER_EXPRESSION, SYNC_ON_UPDATE, SYNC_ON_DELETE) VALUES ('clientext_2_fastsellerxt_titulo_ins', 'clientext', 'fastsellerxt', 'column', current_timestamp, current_timestamp, 0, 'DTPAG=NULL', 0, 0);

INSERT INTO SYM_ROUTER (ROUTER_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, ROUTER_TYPE, CREATE_TIME, LAST_UPDATE_TIME, USE_SOURCE_CATALOG_SCHEMA, ROUTER_EXPRESSION, SYNC_ON_INSERT) VALUES ('clientext_2_fastsellerxt_titulo_updel', 'clientext', 'fastsellerxt', 'column', current_timestamp, current_timestamp, 0, 'DTPAG!=NULL', 0);

INSERT INTO SYM_TRIGGER_ROUTER (TRIGGER_ID, ROUTER_ID, INITIAL_LOAD_ORDER, LAST_UPDATE_TIME, CREATE_TIME, ENABLED) VALUES ('pcprest','clientext_2_fastsellerxt_titulo_ins', 100, current_timestamp, current_timestamp,1);

INSERT INTO SYM_TRIGGER_ROUTER (TRIGGER_ID, ROUTER_ID, INITIAL_LOAD_ORDER, LAST_UPDATE_TIME, CREATE_TIME, ENABLED) VALUES ('pcprest','clientext_2_fastsellerxt_titulo_updel', 100, current_timestamp, current_timestamp,1);

Explanation :

.1 I created two routers -- one for INSERTS and one for UPDATE/DELETE

.2 My INSERT router [clientext_2_fastsellerxt_titulo_ins] has a ROUTER_EXPRESSION which states [at least I believe it so] that it will only INSERT entries whose DTPAG value is NULL. For this router, I have disabled SYNC_ON_UPDATE and SYNC_ON_DELETE.

.3 My other router [clientext_2_fastsellerxt_titulo_updel] has SYNC_ON_INSERT disabled and has no ROUTER_EXPRESSION, meaning it will send any updates/deletes when DTPAG is NOT NULL.

Results : SymmetricDS is bringing the whole table during initial load [several million records, whilst DTPAG=NULL has only a few thousands].

My suspicion : SymmetricDS is bringing my initial load as an UPDATE -- and when it sees the record does not exist, it sends the insert.

Questions :

.1 how can I use ROUTER_EXPRESSION properly ?
.2 is my suspicion accurate ?
.3 help please ?

Thanks for any input !

-- Osimar Medeiros

[Osimar Medeiros]

Anybody ?

[Osimar Medeiros]

Nevermind, I figured it out.

[vilpan]

In that case it's nice to leave the explanation in the thread for the people who might search the archive with a similar problem. Thanks!

[Osimar Medeiros]

This is how I made it work :

INSERT INTO SYM_ROUTER (ROUTER_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, ROUTER_TYPE, CREATE_TIME, LAST_UPDATE_TIME, USE_SOURCE_CATALOG_SCHEMA, ROUTER_EXPRESSION) VALUES ('clientext_2_fastsellerxt_titulo_ins', 'clientext', 'fastsellerxt', 'column', current_timestamp, current_timestamp, 0, 'DTPAG=NULL');

INSERT INTO SYM_ROUTER (ROUTER_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, CREATE_TIME, LAST_UPDATE_TIME, USE_SOURCE_CATALOG_SCHEMA, SYNC_ON_INSERT) VALUES ('clientext_2_fastsellerxt_titulo_updel', 'clientext', 'fastsellerxt', current_timestamp, current_timestamp, 0, 0);

[Osimar Medeiros]

No luck after all.

Filter does not work as expected. It brings all table data instead of the data I was hoping to retrieve based on the router_expression.

Any real examples anyone ?

Thank you.

[Chris Henson]

During the initial load, I don't think the router looks at the on_ event type.

I think what you originally had would work, but you need an initial_load_select of 1=0 that brings back no rows for your update and delete router.

It would be more efficient if you used an initial_load_select for the insert router as well (DTPAG is NULL)

[Osimar Medeiros]

Thank you Chris.

I'll check that out asap.

[Osimar Medeiros]

That worked like a charm, Chris.

Thank you !