Re: Journaling Constraints

Thanks Rob. I am not so worried about somebody turning the journaling off,
just worried about the program that runs nightly, stops journaling, stores
the changes from the previous date, clears the receivers, and re-starts
journaling. We are backing these files up in their entirety each night, so
the journals aren't used for backup/recovery purposes.

The constraints on the log is a much more complex issue... See, if an
account number changes, both the before and after images are stored from the
journals into the log each night. Then, if a person is looking up the
history of that account, they can look at the history of changes for the
final account number, but nothing would show for the changes that happened
before the account number change. So I have modeled a program that reads
through the history of changes in timestamp order, and looks for the
customer account number in the 'after' account number field, and stores
these changes. If it finds one where the 'before' number is different, then
it changes it's search criteria from that point in the file to the 'before'
account number. It continues this until it finds the ADD record, then
displays them to the user.

So you can see that, if an account number changes and journaling isn't on,
for whatever reason, the history for that account is LOST, and not
inquir-able.

On 10/24/05, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:
>
> Anyone clever enough to turn journalling off/on can turn such a constraint
> off/on. However if you're trying for the casual restore didn't do it
> quite right, then use a SQLRPGLE program, with commitment control. If the
> file is not journaled it will say nasty things about your mother. I
> suppose an traditional update with a COMMIT may do something similar.
>
> Why not use pf constraints to avoid the orphans?
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> Tony Carolla <carolla@xxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 10/24/2005 04:04 PM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>,
> RPGIV@xxxxxxxxxxxxxxx
> cc
>
> Fax to
>
> Subject
> Journaling Constraints
>
>
>
>
>
>
> Can journaling be enforced at the DB level?
>
> I am building an application that is using journaling to track history of
> changes, and allow user inquiry (so they will stop saying it was my pgm,
> and
> they didn't change anything <grin>).
>
> Because the primary key of the file (account number) can change, the
> inquiry
> program _NEEDS_ journaling to be on, at all times. If journaling is off,
> and
> an account number changes, bad things will happen, and their history will
> be
> 'orphaned'. The easy answer is to write all update/add applications to
> check
> for journaling to be on, before updating or adding anything. But who knows
> who will be writing these update programs.
>
> What I am hoping is that there is a way to set up something similar to a
> PF
> constraint, that will not allow WRITEs/UPDATEs/DELETEs to a particular
> file
> unless journaling is on.
>
> --
> "Enter any 11-digit prime number to continue..."
> "In Hebrew SQL, how do you use right() and left()?..." - Random Thought
> "If all you have is a hammer, all your problems begin to look like nails"
> --
> This is the RPG programming on the AS400 / 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.
>
>
> --
> This is the RPG programming on the AS400 / 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.
>
>


--
"Enter any 11-digit prime number to continue..."
"In Hebrew SQL, how do you use right() and left()?..." - Random Thought
"If all you have is a hammer, all your problems begin to look like nails"