Trigger Perormance Issues when Journalling Started

110 pts.
Tags:
Journal
performance
TRIGGER
I have an external trigger program (RPGIV) that is added to about 50 files. Its task is to store record images upon insert, delete, update in any of the 50 files. It stores the images in another file (the monitor file) which also contains an identity field (generated always as identity). All files, including the monitor file have to be journalled. To stress test the performance, I am inserting 5 million records in total into the 50 files. If I remove the trigger from the 50 files, this takes 140 seconds. If I change the trigger program so that it issues a return before the write to the log file, then the time goes up to 245 seconds, which seems reasonable. If I let the trigger program write to the monitor file having ended journalling on the monitor file, then it takes 380 seconds. However, when I start journalling the monitor file, the time taken goes up to 1650 seconds (ouch). As I said previously, monitor file stores record images, so the record size is big - 3030 bytes. I poked around with using SQL triggers, which would be easy enough to generate in an RPGIV program, But they also seem slow. I would really like some help on this one, so if you have any idea on what I should do, please let me know. Thanks in advance, Frank

Software/Hardware used:
iSeries

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    When the receiver is full the journal throws a new receiver and connects to the new receiver...this could be a time sink. Aside from that, I would try a seperate journal from the monitor file.  Why exactly would you want to journal the monitor?Phil
    49,540 pointsBadges:
    report
  • FrankWM
    Thanks Phil. I have tried using a separate journal for the monitor file, but it makes no difference to the timings. Why journal the monitor? So it will roll back the inserted monitor file record if the transaction rolls back
    110 pointsBadges:
    report
  • TomLiotta
    ...it will roll back the inserted monitor file record if the transaction rolls back   Does it roll back if the transaction rolls back?   Tom
    125,585 pointsBadges:
    report
  • FrankWM
    Yes it does roll back the transaction if the transaction rolls back. That's part of the elegance of the solution as I only want to know about changes that got committed. My trigger program is RPGIV. We have mixture of processing, some in commitment control and some not. I close and open the monitor file if the flag in the passed parameter indicates I am open in the wrong mode. The Close/Open has no effect in my test as commitment control is always off.
    110 pointsBadges:
    report
  • TomLiotta
    We have mixture of processing, some in commitment control and some not.   Doesn't that wreak havoc on rollbacks?   I close and open the monitor file if the flag in the passed parameter indicates I am open in the wrong mode.   Which modes are "wrong"? I assume the "parameter" is 'Commit lock level'. How does opening/closing the file matter for that?   The Close/Open has no effect in my test as commitment control is always off.   It has no effect on what? And are you saying that the timings you gave above were always outside of commitment control?   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    So the trigger program only writes to the monitor file, which could have been created from the Journals.  Why do this in real time?
    49,540 pointsBadges:
    report
  • FrankWM
    Doesn’t that wreak havoc on rollbacks?No, when STRCMTCTL issued, generally all files are open COMMITWhich modes are “wrong”?Yes - commit lock, if this indicates that the file needs to be open commit, but it isn't, then it is closed and opened commit and vice-versa.It has no effect on what? And are you saying that the timings you gave above were always outside of commitment control?Yes, my tests are outside of commitment control. 50% of real system transactions do not use commitment control and it was only for comparative purposes.The use journals questionThey were considered as an option but they also have their problems that are outside the scope of this.
    110 pointsBadges:
    report
  • FrankWM
    What happened to my line-feeds? My previous message looks a mess
    110 pointsBadges:
    report
  • TomLiotta
    With 50 files, there could be a very large number of jobs all trying to write to the same (monitor) file. Contention could be excessive. Further, the extra journaling adds an additional layer of contention (for 50 files). And because the write to the journal must complete before the write can be allowed to the file, every point of contention has an extra delay added in.   All of it on top of the primary output to the 50 files and the journaling for them.   I've yet to understand why a "monitor" file is good for anything when journaling already exists. I don't quite see the elegance yet.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    And 5,000,000 rows in 25 minutes is a problem because ...
    49,540 pointsBadges:
    report
  • FrankWM
    And 5,000,000 rows in 25 minutes is a problem becauseBecause it only takes a little over 2 minutes without the trigger. Although, in practice you may be right. I need to do some analysis of just how many writes do take place. I'll do that later today.Interestingly, I have done some tests with record updates and they are quite acceptable. Something like 1.5 million updates. With no trigger 7 minutes and with the trigger, 16 minutes. I also have a lightweight version of the monitor file that does not include the before and after images (the thought is that I can write those to a separate, non-journaled file with same key). In that scenario I see blocking happening with the writes to the monitor file and it only takes 9 minutes; I haven't added the extra file for the images, so it will go up when I do
    110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following