1

I have something like this to insert via application into DB2 LUW,

insert into MY_TABLE (CREATION_DATE, MESSAGE, IDENTIFIER) values 
(CURRENT_TIMESTAMP, '0', '1b0d7cfb-66ee-4372-913b-2283c6e3f0b5')
,(CURRENT_TIMESTAMP, '1', '1b0d7cfb-66ee-4372-913b-2283c6e3f0b6') 

I have also some triggers installed which makes some consistency checks on the inserted row and which takes some time for execution.

CREATE OR REPLACE TRIGGER  
BEFORE INSERT ON MY_TABLE
REFERENCING NEW AS NEW
    FOR EACH ROW
        --some checks takes approx. 10s for each row
        END

So how can I achieve that the triggers are executed parallel, do not like to wait for 20s.

1 Answer 1

0

That would only be possible if you executed individual row inserts from separate concurrent sessions, but then those sessions would be subject to the usual concurrency control, so all but one of them will likely block anyway.

Perhaps your best bet would be to optimise your consistency checks to not take 10 seconds to complete.

3
  • Lets say I am solving it via concurrent sessions, but what I am struggling about is, I have to ensure that if one of the session is failed, than the whole transaction should be rolled back, preferably only one commit should be done at the end of the whole transaction.
    – sensen ol
    Commented Nov 30, 2023 at 11:28
  • Well, you'll continue struggling with that, since a typical database transaction cannot span multiple sessions. You'll need an external transaction monitor that implements the two phase commit protocol.
    – mustaccio
    Commented Nov 30, 2023 at 13:03
  • Do you have an api for an external transaction monitor or do you mean a tool?
    – sensen ol
    Commented Nov 30, 2023 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.