Tweet
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