RE: Journaling, SQL updates/inserts/deletes, and COMMIT(*NONE)

Dan,

You need to look up the APIs for journals/receivers. There's quite a bit
more you can do. Carsten has wonderful examples of how to use many of the
APIs.


--
Jim Oberholtzer
Agile Technology Architects


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Monday, January 16, 2017 11:02 AM
To: Midrange Systems Technical Discussion
Subject: Re: Journaling, SQL updates/inserts/deletes, and COMMIT(*NONE)

We are turning on journaling primarily for HA replication. In addition,
because of this, we are redesigning a project that captures transactions
performed on 29 of our production tables that will be sent to an Oracle
database so that their copy of our 29 tables will match the data in our
tables. So, yes, we are going to do "journal scraping". (Before the
decision to journal, the design was to use database triggers on these 29
tables, data queues, and "history tables" that replicated the original
tables plus three new columns.)

Going a bit OT, but not sure it merits its own thread, is there an SQL
function at v7r1 that can do the "journal scraping"? Or am I limited to
using the RCVJRNE command?

- Dan

On Mon, Jan 16, 2017 at 10:46 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

I agree that everyone should be journalling.

I also believe that commitment control is something to be looked into.
For example, if one transaction (like shipping an item) updates
multiple files and part way through the transaction you'd like to back
out that transaction then this is what commitment control is for. For
example, you ship an item.
This transaction updates item master, item master by warehouse, item
master by location, order line file, order header file, GL details,
and so on and your write hits an error on the item master by location
because a write trigger will not allow a negative balance, then you
may need to back out that whole transaction.

But if you're doing journalling so that you can capture every
transaction and can do journal scraping to do analysis then you might
be better off to do IBM i 7.3's "Temporal Tables". It's pretty good.
Enough so that we will be adopting it like crazy as soon as we get a
HA solution which doesn't just milk their maintenance money and starts

supporting them.

Sure, you may still be doing journalling but you won't retain your
receivers as long and your analysis will be MUCH easier.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-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