× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. April 2016

Re: Performance hit with trigger program

The other thing to think about assuming your at V6R1 or higher. Make any
callable program a Main program. It eliminates the cycle, runs aobut 20%
faster and cuts size of program.

Going to use free format here. Easy enough to translate back to fixed.

Use a named activation group for best performance and assuming all this
program is going to do is write to data queue. Always recommend mediator.
In that case, this would be a very simple Service Program.

**FREE

ctl-opt Option(*Srcstmt:*Nodebugio:*Nounref);
ctl-opt Main(ProcessTransaction);

dcl-ds TD_DbTriggerBuffer Qualified Template;
PhysicalFileName Char(10);
PhysicalFileLibrary Char(10);
PhysicalFileMember Char(10);
TriggerEvent Char(1);
TriggerTime Char(1);
CommitLockLevel Char(1);
Reserve1 Char(3);
CCSIDOfData Int(10);
RelativeRecordNumber Int(10);
Reserve2 Char(4);
BeforeOffset Int(10);
BeforeLength Int(10);
BeforeNullByteOffset Int(10);
BeforeNullByteLength Int(10);
AfterOffset Int(10);
AfterLength Int(10);
AfterNullByteOffset Int(10);
AfterNullByteLength Int(10);
Reserved3 Char(16);
end-ds;

dcl-pr ProcessTransaction ExtPgm('YOURNAME');
PR_InTriggerBuffer LikeDs(TD_DbTriggerBuffer);
PR_InBufferLength Int(10) Const;
end-pr;

dcl-proc ProcessTransaction;
dcl-pi *N;
InTriggerBuffer LikeDs(TD_DbTriggerBuffer);
InBufferLength Int(10) Const;
end-pi;

// declare all variables as local to procedure. As little static as
possible.

// Your write data queue logic.

Return;

end-proc;

On Thu, Apr 7, 2016 at 11:52 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

100% in agreement with Stuart.

The real killer in AG choices would have been to run the trigger in *New.
That would probably have doubled ( or worse ) the impact of LR On. On the
other had even if you hadn’t set LR the file would still have been closed
<grin>


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Apr 7, 2016, at 2:04 PM, Stuart Rowe <rowestu@xxxxxxxxx> wrote:

THOU SHALT NEVER EVER USE THE *DFTACTGRP WITH AN ILE EXECUTABLE (oh I'm
gonna take it in the backside for that one...)

Considering that a trigger program *may* be executed in a multi-threaded
job, you also do not want to use *NEW because when that activation ends
the
whole job will end.

IMO *CALLER is the best bet for a trigger handler. CRTBNDRPG
DFTACTGRP(*NO)
ACTGRP(*CALLER) will do it.

If you do away with the file and use a queue instead, activation group is
of less concern but still don't ever let it end for a trigger program.

Stu


On Thu, Apr 7, 2016 at 12:57 PM, Dan <dan27649@xxxxxxxxx> wrote:

Jon, can you elaborate on this? I haven't explicitly defined AGs in
several years. The trigger program uses *DFTACTGRP.

Links or pointers to RTFMs are fine.

Thanks!
- Dan

On Thu, Apr 7, 2016 at 12:59 PM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
wrote:

You don’t say what AG you are using but that would also potentially
have
an impact. It is the constant close/open of the file that is killing
you
when setting LR on.

No need to set on LR each time but without it you need to decide how
you
are going to close the file.


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Apr 7, 2016, at 11:25 AM, Dan <dan27649@xxxxxxxxx> wrote:

I'm testing the trigger app I developed with help from some of you
here
these past few days.

I ran an iteration of performance tests. A simple program that reads
(by
key) the OWNER table, updates a field, and updates the OWNER record,
inside
a FOR X = 1 TO 100000 loop. I ran this program 20 times; odd-numbered
runs
without a trigger, even-numbered runs with a trigger. The difference
was
significant. Without a trigger, the 10 runs averaged 3.4 seconds.
With
a
trigger, the 10 runs averaged 198.9 seconds. The 100,000 records is
representative of the batch job volumes they have here. The OWNER
table
is
updated in interactive programs as well.

Reviewing several examples of trigger programs online, I noticed that
all
of them turned on the LR indicator. For testing, I no-op'd the EVAL
*INLR=*ON so the program would compile, but never have LR turned on.
Duplicating the previous test cycle, without a trigger, the 10 runs
averaged 3.6 seconds. With a trigger, the 10 runs averaged 11.8
seconds.
Yeah, about 16 times faster than when turning LR on.

As you can tell by the program source I've included below, this is a
simple
app that basically acts like a journal, so there's no need for
initialization each time it's called. I was concerned that each of
the
100,000 calls would put 100,000 instances of the trigger program in
the
job's call stack, but that was not the case. The output file in the
trigger program remained open after my test run completed. I'm not
sure
whether this a concern, because there are about 200 interactive users
who
will be doing something throughout their day that updates the OWNER
table.
CAN ANYONE ADVISE WHETHER THERE ARE ANY PROBLEMS WITH THIS APPROACH?

Oh, and for those who will inevitably ask, I was told that we cannot
turn
on journaling. This is my second week here, and I don't have enough
clout
to delve into the why.

fTRG001H o e disk
d pi ExtPgm( 'TRG001R' )
d eTrgBuffer LikeDS( TrgBuffer )
d eTrgBufferLen Like( TrgBufferLen )
/copy TRGBUFCOPY
d TriggerPtr s *
d TriggerRecord e ds ExtName( OWNER )
d Based( TriggerPtr )
d Prefix( T_ )
d OutputRecord e ds ExtName( OWNER )

Select;
When eTrgBuffer.tbEvent = '1'; // Insert
TriggerPtr = %addr( eTrgBuffer ) + eTrgBuffer.tbNewOffset ;
TrgType = 'I';
When eTrgBuffer.tbEvent = '2'; // Delete
TriggerPtr = %addr( eTrgBuffer ) + eTrgBuffer.tbOldOffset ;
TrgType = 'D';
When eTrgBuffer.tbEvent = '3'; // Update
TriggerPtr = %addr( eTrgBuffer ) + eTrgBuffer.tbNewOffset ;
TrgType = 'C';
Endsl;

TrgTmStamp = %timestamp;

OutputRecord = TriggerRecord;
Write TRG001Hr;

If *inLR <> *inLR; // Test for performance by not turning LR
on
*inLR = *on;
Endif;
Return;

copy member TRGBUFCOPY:
d TrgBuffer ds
d tbFileName 10
d tbLibraryName 10
d tbMemberName 10
d tbEvent 1
d tbTime 1
d tbCommitLock 1
d tbFill01 3
d tbCCSID 10i 0
d tbRRN 10i 0
d tbFill02 10i 0
d tbOldOffset 10i 0
d tbOldLength 10i 0
d tbOldNullOff 10i 0
d tbOldNullLen 10i 0
d tbNewOffset 10i 0
d tbNewLength 10i 0
d tbNewNullOff 10i 0
d tbNewNullLen 10i 0
d tbBufChar 1 32767
d tbBufArry 1 Overlay( tbBufChar )
d Dim( %size( tbBufChar ))

d TrgBufferLen s 10i 0
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.