[z/OS] Timing of trigger/stored procedure/update

Mark Vickers

[z/OS] Timing of trigger/stored procedure/update
We have an after update trigger (BOHT1) on our Billing Order Header table (BOH).
If the status column = 6 it fires a stored procedure (SP1) passing the invoice number.
SP1 checks to see if the entire order has been fulfilled
(all BOH columns with same invoice number have status = 6)
If so, all the BOH and BOL (line items) for that invoice number are copied to two other tables BPH and BPD (header and details) and a job is submitted which prints the invoice.

The timing of all this seems to work fine in production, possibly because each BOH row is updated as the forklift driver completes picking the invoice.

Doing some testing, in SPUFI, we did an update, set status = 6 where BOH.INVNBR = :H
There were 5 rows updated and the invoice printed 5 times.
It should have only printed once, which it does in production.
My logic says trigger is fired 5 times which is expected, cannot understand how all 5 rows were updated before the first trigger/SP1 fired ?
Stumped !, what am I missing here ?

Thanks,
Mark Vickers

Graham Clarke

RE: [z/OS] Timing of trigger/stored procedure/update
(in response to Mark Vickers)

Hi Mark,

My guess would be the timing of the commit processing. SPUFI will commit at the end (unless you code a commit). Does the Production application commit after each row?

Mark Vickers

[z/OS] Timing of trigger/stored procedure/update
(in response to Graham Clarke)
Hi Graham,
That makes sense, thanks.
I found the SP select was using WITH UR and will retest with CS.

Thanks,
Mark.

From: Graham Clarke [mailto:[login to unmask email]
Sent: Wednesday, May 10, 2017 3:36 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [z/OS] Timing of trigger/stored procedure/update


Hi Mark,

My guess would be the timing of the commit processing. SPUFI will commit at the end (unless you code a commit). Does the Production application commit after each row?

-----End Original Message-----