I have fileA which has 250k records. When batch job runs it processes this file instantly (all 250k records in less than a minute). Now when I add the trigger for insert/update/delete, it becomes 2-3 hours.
The trigger has the following. What it does is it checks if a record exist on fileB via `Select 1 From FileB Where Field1=N.Field1`, it then it runs an if else sqlcode=100 then do either an update or an insert. All on the trigger. I tried to have it explain when I do each individual queries. I thought it was just an index problem. I created an index but when I explain the query, the index is not even used. Now I'm not sure what's causing the extreme slowdown.
EDIT: I'm considering to convert this trigger a pure RPGLE program instead. And chain+update+unlock when needed. I imagine the program is executing this trigger 250k times and that is causing the slowdown.
EDIT2: Added if else conditions on the trigger update statement to filter out update without value change from a chain->update rpgle sequence. CPU usage seconds went down by 90%. The remaining time I would guess is just DB2 trigger tax.
The trigger has the following. What it does is it checks if a record exist on fileB via `Select 1 From FileB Where Field1=N.Field1`, it then it runs an if else sqlcode=100 then do either an update or an insert. All on the trigger. I tried to have it explain when I do each individual queries. I thought it was just an index problem. I created an index but when I explain the query, the index is not even used. Now I'm not sure what's causing the extreme slowdown.
Code:
CREATE TRIGGER library1.CrtFILE1 AFTER INSERT OR DELETE OR UPDATE ON library1.FILE1 REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2ROW SET OPTION ALWBLK = *ALLREAD, ALWCPYDTA = *Optimize, Commit = *None, DBGVIEW = *Source, DECRESULT = (31, 31, 00), DFTRDBCOL = library2, DYNDFTCOL = *No, DYNUSRPRF = *User, SRTSEQ = *HEX BEGIN ATOMIC /* Define Variable */ DECLARE Sqlcode INTEGER DEFAULT 0; /* Inserting a new row */ IF INSERTING THEN SELECT 1 FROM library2.FILE2 WHERE library2.FILE2.Field1 = N.Field1; IF Sqlcode = 100 THEN /* No other process has added a row */ INSERT INTO library2.FILE2 ( Field1, Field2, Field3, Field4, Field5, Field6 ) VALUES ( N.Field1, 'FILE1', 'INS', Current_Timestamp, ' ', Current_Timestamp ); ELSE /* Row exists, need to update with the latest information */ UPDATE library2.FILE2 SET ( Field1, Field2, Field3, Field4, Field5, Field6 ) = (N.Field1, 'FILE1', 'INS', Current_Timestamp, ' ', Current_Timestamp) WHERE library2.FILE2.Field1 = N.Field1; END IF; END IF; /*Updating a record in the Email Table */ IF UPDATING THEN SELECT 1 FROM library2.FILE2 WHERE library2.FILE2.Field1 = N.Field1; IF Sqlcode = 100 THEN /* No other process has added a row */ INSERT INTO library2.FILE2 ( Field1, Field2, Field3, Field4, Field5, Field6 ) VALUES ( N.Field1, 'FILE1', 'UPD', Current_Timestamp, ' ', Current_Timestamp ); ELSE /* Row exists, need to update with the latest information */ UPDATE library2.FILE2 SET ( Field1, Field2, Field3, Field4, Field5, Field6 ) = (N.Field1, 'FILE1', 'UPD', Current_Timestamp, ' ', Current_Timestamp) WHERE library2.FILE2.Field1 = N.Field1; END IF; END IF; /* Deleting a record in the Email Table */ IF DELETING THEN SELECT 1 FROM library2.FILE2 WHERE library2.FILE2.Field1 = O.Field1; IF Sqlcode = 100 THEN /* No other process has added a row */ INSERT INTO library2.FILE2 ( Field1, Field2, Field3, Field4, Field5, Field6 ) VALUES ( O.Field1, 'FILE1', 'DEL', Current_Timestamp, ' ', Current_Timestamp ); ELSE /* Row exists, need to update with the latest information */ UPDATE library2.FILE2 SET ( Field1, Field2, Field3, Field4, Field5, Field6 ) = (O.Field1, 'FILE1', 'DEL', Current_Timestamp, ' ', Current_Timestamp) WHERE library2.FILE2.Field1 = O.Field1; END IF; END IF; END; ;
EDIT2: Added if else conditions on the trigger update statement to filter out update without value change from a chain->update rpgle sequence. CPU usage seconds went down by 90%. The remaining time I would guess is just DB2 trigger tax.
Comment