Re: Journaling Constraints

We receive a daily paper log from some hospitals, from which the user keys
in the hospital account number. Sometimes, they key it in wrong, and have to
change it. Sometimes the hospitals mistakenly re-use the account numbers
(they are unique keys in the DB), so we have to go back and add a char to
the end of the number.

We have entertained the idea of creating another, unique identifier, which
links all related files, for this database, but we will probably decide
against it. You see, in the project that we are working on, there are four
applications, running on three servers (2 on PCs, 2 on iSeries), that deal
with this data throughout the course of it's life. The Hospital account
number is the link between all servers. Creating another tracking number for
these accounts, which will just be used internally to this database, and is
only necessary for one function (history inquiry), seems unnecessary.

We haven't made a final decision yet...

On 10/24/05, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:
>
> What business reason would there be to change an account number?
>
> 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:25 PM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> 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"
> --
> 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"