DB triggers of SYM_TRIGGERS table got invalid

[Roberto Giovanardi]

Hello everyone,
we have a test instance of Symmetric DS, version 3.9.8 CE, with source database IBM DB2 9.7. Today we tried to add a table into an already running synchronizazion with about 300 tables, but as soon we inserted the new table into SYM_TRIGGERS and SYM_TRIGGERS_ROUTERS we got error from clients inserting its value into their SYM_TRIGGER_ROUTERS because the FK of SYM_TRIGGERS not exists:

Failed row data was:

"jqrm2db.symmds_services","source_waits_pull_from_client","1","483",,,"0","2018-09-20 22:29:07.654288","admin","2018-09-20 22:29:07.654288",
 StackTraceKey.init [SqlException:4128149281]
org.jumpmind.db.sql.SqlException: INSERT on table 'SYM_TRIGGER_ROUTER' caused a violation of foreign key constraint 'SYM_FK_TR_2_TRG' for key (jqrm2db.symmds_services).  The statement has been rolled back.

After digging a bit on that we figured out that on the source DB2, on SYM_DATA table, was present only the SYM_TRIGGER_ROUTER Insert and not the SYM_TRIGGERS one.
So we checked SYM_TRIGGERS's trigger and we found they are deactivated:

SELECT TRIGNAME, TABNAME, VALID FROM SYSCAT.TRIGGERS WHERE TABNAME = 'SYM_TRIGGER'

TRIGNAME                   |TABNAME     |VALID |
---------------------------|------------|------|
SYM_ON_U_FOR_SYM_TRGGR_SRC |SYM_TRIGGER |X     |
SYM_ON_D_FOR_SYM_TRGGR_SRC |SYM_TRIGGER |X     |
SYM_ON_I_FOR_SYM_TRGGR_SRC |SYM_TRIGGER |X     |

Why do triggers got deactivated? How to avoid it?
How to reactivate them? We already try a sync-triggers but didn't works; only solution was to reactivate via DB2 stored procedures:
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('TRIGGER','SYMUSR','SYM_ON_U_FOR_SYM_TRGGR_SRC');

Currently we have other 2 test instances with the very same configuration but triggers are still valid. Even a main Symmetric DS Pro, with similar configuration, have triggers valid. Only this test instance got invalidated.

Thanks in advance
Roberto

[Mark Michalek]

Roberto, DB2 will invalidate triggers when procedures or table structures change. Normally SymmetircDS should also detect these structural changes and rebuild the triggers as needed. If there is a case where the trigger got invlalidated (or disabled on other platforms), Symmetric would not recognize this. We allow for disabled triggers so users can temporarily pause their data capture.

If you recognize a pattern to this issue, please let us know what you observe and maybe we can help come up with a solution to prevent this.

Mark

[Roberto Giovanardi]

Hi Mark,
thanks for your answer. Unfortunately we have no pattern about this issue.. We played a bit with other not SYM tables which got invalidated too, but we didn't touch Symmetric DS rules.

We will keep it monitored.

Roberto