Re: RPGLE Trigger programs and Commit Control -- RPG400-L

Hi Mark,

thanks as always for your reply.

To be honest my main surprise was that the trigger got called on the
UPDATE, I really had assumed it wouldn't happen until the COMMIT.

However, having seen that things work differently to the way I had assumed
I have no difficulty in coding it.

I do have a lot of experience of MQ, Prior to this contract I worked for a
couple of years writing interfaces between legacy IBMi applications and
Oracle.

The data queue I'm using is actually just to make the async process
event-driven, I'm not passing data via the queue.

cheers,
Craig

On 7 June 2018 at 16:31, Mark Murphy <jmarkmurphy@xxxxxxxxx> wrote:

Craig, how does the trigger handle shifting from synchronous processing to
asynchronous? If you are using Data Queues, You may want to look into
MQSeries. Those queues are a bit heavier, but have commitment control built
in. I believe that with MQSeries, a message that is put on the queue but
not yet committed is not available for retrieval. Then you can use
commitment control as needed. You asynchronous jobs do not get messages
until they are committed, and a rollback will remove those messages before
they are delivered.

Rollback should not fire the trigger again because anything that is
processed in the same commitment definition whether by trigger, or by
application will be rolled back by the rollback itself (as long as all your
changed resources are covered by the same commitment definition). If you
are doing commitment control, but not covering all of your bases with the
commitment definition, that is really on you, not the database. It has to
assume that anything you left out of the transaction was intentional. Thus
re-firing triggers is the wrong thing to do on a rollback.

On Thu, Jun 7, 2018 at 10:46 AM, Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Hi Birgitta,

I changed the commit scope to *JOB.
I'm seeing the same behaviour as before.

To reiterate...

- Application Program running under commit control ( I tested with

both

*JOB and *ACTGRP ) updates FILEA ( under commit control )
- FILEA has an RPGLE *AFTER *UPDATE trigger, not using commit control.
This program runs in the caller's activation group
( For completeness, this trigger program looks at the journal images

and

if certain fields have changed, builds a JSON message from the data

and

writes to FILEB, then uses QSNDDTAQ to wake an async server which

picks

up
the message from FILEB and distributes it to other systems )
- After the UPDATE executes in the Application program, stepping

through

in debug shows it goes straight into the trigger program, which

performs

the operations described above. Stepping back out of the trigger

program

and though the main application program and past the eventual COMMIT
obviously doesn't invoke the trigger again, it's work is done.
- Repeating the process, but forcing a fail and ROLBK this time, it
works exactly the same. The Trigger Program gets called immediately
following the UPDATE, but is not called again after the ROLBK when the
database transaction is rolled back.

Now that I know this is how it works, I can code around it. I'm just
surprised that the trigger is informed about the initial update but not

the

fact that it was rolled back.

I understand how I can include the trigger database activity in the same
commit block if I want, but actually that doesn't solve my problem

because

the write and async process has already happened and can't be reversed

but

it also does not explain why the trigger gets the update but not the
rollback. How can the trigger keep things in order if it doesn't have all
of the transactions?

Anyway, I'll just have to take care of more of this at the Application

Code

level, I was just surprised that things do not work as I had assumed.

Best regards,
Craig

On 7 June 2018 at 12:15, Marco Facchinetti <marco.facchinetti@xxxxxxxxx>
wrote:

Yes using RCVJRNE. And you can complete the circle telling the trigger

to

write the before image and a timestamp (if program's name match) into a
dedicated table. Later you can match journal entries (using id and
timestamp), checking for rollbacks, and do your sync job.

HTH

--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

2018-06-07 13:00 GMT+02:00 Craig Richards <craig@xxxxxxxxxxxxxxxx>:

Hi Marco,

Thanks for your comment.

By journal analysis do mean - rather than firing a trigger, having an

aysnc

process using something like RCVJRNE to read the entries and do

something

with them based on the program listed there?

If so, it's a good reminder of that technique which I have used in

the

past. Also I've seen some places set up so that trigger programs do

nothing

themselves other than shunt the record image(s) to a secondary

generic

table where an async process decides what, if anything to do with the
change.

But we are only journalling After Images and I need both for my

processing

and I've already got a standard process in place which essentially

uses

QMHSNDPM and QMHRCVPM to bounce up the stack until it either hits the

top

or finds a program whose name doesn't begin with Q which it then

checks

against an exclusion file. It works fast, doesn't leave messages in

the

joblog and for the moment at least meets our requirements.

thanks kindly,
Craig

On 7 June 2018 at 11:27, Marco Facchinetti <

marco.facchinetti@xxxxxxxxx>

wrote:

Hi Craig, if your decision about update or not doesn't relay on the

full

call stack but only on write/update program 's name consider using

journal

analysis.

I found it very usefull especially if updates are really async or

on

a

remote DB.

HTH

--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

2018-06-07 10:26 GMT+02:00 Craig Richards <craig@xxxxxxxxxxxxxxxx

:

Hi Dieter (I hope this is the correct name - it seems better than

calling

you D*B )

Thanks again for taking time to post. I do appreciate it.

I will consider your words, if nothing more than for my own

education

as

I

haven't looked at commit exit program.
However I will most likely take my previously suggested approach

and

exclude certain programs from the trigger processing, putting

that

code

in

the application programs.

Here are my reasons for this:

1) I prefer to keep trigger programs as light as possibly. This

is

because

they have to perform as part of the database update and therefore

be

efficient, and the other reason is that, to me at least, it's

kind

of

"under the covers" processing. So any heavy transactions or

business

rules

I tend to port into something like an asynchronous server where

the

code

is

more visible and it's not holding up critical I/O. This is just

my

opinion

and preference, I don't expect everyone to agree.

2) When there is a possibility that the database can be updated

via

DDRA/DDM (QRWTSRVR Jobs) or Database Host Server (QZDASOINIT

Jobs)

which

hang around for a while and get re-used via different

connections,

you

cannot leave the trigger program resident or you are just asking

for

trouble. Therefore every database access would need to start up

the

trigger

program each time, then the trigger program would have to find

and

dynamically bind in the service program each time, then this is

registering

the exit program each time. I'm sure many, maybe most people

would

argue

that this is all find and dandy and just a walk in the park for

an

IBMi,

but I'd personally just rather not have all of that going on in

my

triggers

plus the overhead and logic of storing up all of the data ready

for

when

a

COMMIT is issued. I can see how this approach could work though.

But I'm grateful you took the time to send me your thoughts and

some

information I didn't know about before.

best regards,
Craig

On 7 June 2018 at 08:55, D*B <dieter.bender@xxxxxxxxxxxx> wrote:

<Craig>
1) If the database manager held back on the trigger processing

until

the

commit. Life would be simple. There would be nothing to undo.

I'm

using

commit control, that's a declaration that I don't want to

declare

the

transaction over until I commit. Why let processing bleed over

into

the

trigger's domain until I've confirmed the transaction with a

commit?

</Craig>

... this wouldn't be too hard to implement:
- create a SRVPGM, providing exported procedures:
-- triggerFired, taking the complete Trigger Buffer, just

putting

the

contents to a global variable to store it (if you would have

multiple

records in one transaction, dim would help.
-- commitIssued, taking the info of the commit exit program

CCEXIT,

and

does all needed work of your trigger, if commit was issued and
reinitializes the global buffer variable. In case of rollback,

only

initialisation is done.
-- at activation time (first call of one of the exported

procedures)

register the commit exit by call of the API.
- your trigger programm, only calls triggerFired, doing

nothing

- create a commit exit programm (you'll find QRPGLESRC.CCEXIT

and

the

needed headerfiles on sourceforge too, as an example). If

CCEXIT

is

called,

simply call commitIssued of your SRVPGM.
Let it all run in *caller.

that's it!

D*B
--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD