More WSG

Messages are piling up with status 2, what is going on?  Easy

Remember the classic statement that will tell you everything about everything:

select count(1), ETEVNTST from testdta.f90710 group by ETEVNTST

Tell you the records and what status they are at.

If you have records at 2, it means that the trigger is not working, you need to create the trigger and the sequence.  Note that this is going to happen after every data refresh unless you manually create the trigger and the sequence in your prod environment (PRODDTA) so refreshes will pick it up.  Note that it will not get used in the prod environment, because it actually uses the SY812.F90710 (if you are being good and following best practice).

Note that this is oracle syntax:

Log on as JDEDBA

CREATE OR REPLACE TRIGGER TESTDTA.F90710_EVT_RIB
before insert on TESTDTA.F90710
for each row
declare
  nextseq number;
begin
  select TESTDTA.F90710_EVT_SEQ.nextval into nextseq from dual;
  :new.ETEVNTTIME := sysdate;
  :new.ETEVNTST := 3;
  :new.ETEVNTSEQ := nextseq;
end;
/

DROP SEQUENCE TESTDTA.F90710_EVT_SEQ;

CREATE SEQUENCE TESTDTA.F90710_EVT_SEQ
  START WITH 16011243
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  ORDER;

GRANT SELECT ON  TESTDTA.F90710_EVT_SEQ TO RO_TESTDTA;

GRANT SELECT ON  TESTDTA.F90710_EVT_SEQ TO RW_TESTDTA;

If you’ve got a bunch of data in there at 2, create a copy of the table.  Install your triggers and the insert the data back, then all of the correct things will happen and the events will be marked as 3.  Then when they start getting grabbed by the txn server they will be marked as 4.  Once the delivery has been confirmed, they will be deleted!

When things are broke:

  COUNT(1)   ETEVNTST
---------- ----------
      5521          2

When things are working:

  COUNT(1)   ETEVNTST
---------- ----------
      3384          3
       685          4