Re: estimating RGZPFM runtime -- MIDRANGE-L

First off, the definition of primary key doesn't include that it be
immutable...

CREATE TABLE WILTC/TEST2(FLD1 CHAR (10 ) NOT NULL WITH DEFAULT,
PRIMARY KEY (FLD1))
Table TEST2 in WILTC created but was not journaled.
insert into test2 values 'CHARLES'
1 rows inserted in TEST2 in WILTC.
update test2 set fld1 = 'WILT'
1 rows updated in TEST2 in WILTC.

Life's arguably easier if your PK doesn't change. The MS SQL / Oracle guys
who preach the use of identity or GUID's for PK's generally use
the mutability of natural keys as one of the reasons. But that's an
ongoing religious war. :)

Finally, if you're only reporting 1 day's activity. Then you could get
away with reuse deleted or RGZPGM. My point that RGZPFM would be a problem
only applies if you're saving/reporting across days. If you simply
generate the report at the end of the day, you don't even really need RRN#
except to groups the change to a given item together. The before and after
images for a given change are always written in sequence.

Charles

On Tue, Nov 19, 2013 at 10:56 AM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

Thanks for your response.

The assumptions you are making are not accurate - at least in our org. I
guess that I wasn't such a good communicator on this one.

1) When someone updates the price on Cheerios the next day, YES it WILL
pick up the price change. Not sure why you are focusing on a problem that
doesn't exist in this scenario.
(When Sally changes the price at noon, and the journals are dumped at
EOD, the BEFORE price will not equal the AFTER price for [the new] RRN 456,
so the price change WILL be reported).

2) Our files were designed 40 years prior to my arriving here, so I cant
really speak to the "why's" of how the DB was set up. But as was probably
typical for back then, there IS a UNIQUE key on these files. But, it is
not a "Primary" key. My understanding is that a PRIMARY key is immutable;
ie cannot be changed. [Unfortunately] years ago someone created methods
whereby users could CHANGE the unique key values of many files here. When
Cheerios changes from item# 111111 to item# 222222, they STILL require that
the price field is audited and reported on. (And the item# change is
reported also).

Hence the reliance on RRN. BUT, rrn is NOT used as a key to link records.
It is ONLY used when farming the journals - is to ensure that one entity
is compared only to the same entity.

Once the journals are dumped and reported on, a RGZPFM will shuffle the
RRN but that shouldn't have a negative effect on future journal reporting
(as long as users are not making changes during RGZPFM and also the RGZPFM
time window must be skipped over for the next DSPJRN dump).

(I chose PRICE and ITEM as simple understandable fields. We don't really
have PRICE in the product file - its just an example for my question about
RGZPFM).

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, November 19, 2013 7:57 AM
To: Midrange Systems Technical Discussion
Subject: Re: estimating RGZPFM runtime

So what happens the next day when somebody updates the price on Cheerios
again?

You won't pick it up as a price change as Cheerios is now RRN 456 not 789

Assuming you're really dependent on RRN, I don't see how you could use
RGZPFM. You mention before & after images, which gives you a real easy way
to report what changed. You also mention an item#. Is the data in that
field not unique (regardless of rather or not UNIQUE is actually
specified)?

Whereas with the reuse of deleted records, your journal farming would need
to be smart enough to pick up that a delete followed by an add for a given
RRN means a new item. But at least the existing RRN of a item would stay
the same for it's life.

Given that we seem to be talking about some sort of item master, I find it
very hard to believe there isn't an existing primary key, such as the
aforementioned item# field. Even if UNIQUE isn't specified, I'd bet that
apps assume and try to ensure that item# (or some other filed) is unique.
And you probably have all sorts of issues when something screws up and
breaks the assumption.

That being the case, I'd highly recommend putting a primary key on the
file. Assuming the PF is currently unkeyed (or keyed without UNIQUE), you
can add a DDS UNIQUE or a PK constraint without changing the record format
level identifier which means you don't need to recompile and programs.

For that matter, if there is truly no "assumed" primary key. It's really
not hard to add one via an identity column if nothing else without needing
to recompile anything.
Simply:
- move the data to a new table with the new PK identity column
- point existing logicals to the new table
- turn the existing PF into a logical over the new table

The key is to explicitly list the columns in the LFs. This keeps the
record format level identifier the same as before and you won't need to
recompile anything. You can even initially create the new objects in a
different library so you can double check that your'll doing it right and
that the record format level identifier is unchanged.

I use this technique all the time, not to add a PK but to add columns to a
PF without recompiling or changing any existing programs; as I usually have
lots of programs that reference the original keyed PF.

Charles

On Mon, Nov 18, 2013 at 5:38 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx>
wrote:

No I don't agree. I don't think re-using deleted records has any chance
of working for us.

If it is a controlled process, it should work OK as is using RRN.

Example: Cheerios cereal

Monday 9 pm: (Cheerios is item #123 in RRN 789)
lock users off app
dump journals and process them
allow users back into app

Tuesday morning: Cheerios price is changed by user from $3 to $3.05

Tuesday 9 pm: lock users off app
Dump journals and process them
Farming journals detects user changed Cheerios - sees RRN
789 has "Before" vs "After" journal image discrepancy in user data area
Cheerios price change is reported

10 pm: RGZPFM on file containing Cheerios record
Cheerios retains item# 123, but moves up to RRN 456 due

to

some records being deleted from above during the day

11 pm: journal reporting window "start time" is moved forward to
11 pm so tomorrow night's journal farming activity skips over RGZPFM

changes

11:01 PM allow users back into app

Now all should be back to normal and ready to start a new cycle.

If we were to allow re-use deleted records, I think that we would need a
real "primary" key (immutable key) which we don't have on these files

which

were designed in the late 1970s.

Anyone agree or disagree or have any better ideas???

Thanks!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, November 18, 2013 4:23 PM
To: Midrange Systems Technical Discussion
Subject: Re: estimating RGZPFM runtime

If you use RQZPFM at all you'll lose RRN integrity...

You'd be best served by reusing deleted records.

Charles

On Mon, Nov 18, 2013 at 5:16 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx>
wrote:

Another caveat I should have mentioned is that we do "farm the

journals"

looking for user data changes. The journal processing depends on the

RRN

being intact and unchanging between journal dumps.

For example if Cheerios is item# 123 and was in RRN slot 789 yesterday,
then it had better be in slot 789 again tonight with the next journal
farming activity takes place.

I am afraid if we use RGZPFM while active, we would lose RRN integrity.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Steinmetz, Paul
Sent: Monday, November 18, 2013 3:41 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: estimating RGZPFM runtime

Joel,

If you use RGZPFM while active, no down time. We created a process (CLs
and qry), using RGZ While Active, does all PF with deleted records.

STRJRNPF FILE(&MBLIB/&MBFILE) JRN(QGPL/RGZPFM)

SNDPGMMSG MSG('RGZPFM starting for ' *BCAT &MBLIB *BCAT +
'/' *BCAT &MBFILE *BCAT '.')

RGZPFM FILE(&MBLIB/&MBFILE) MBR(&MBNAME) +
RBDACCPTH(*NO) ALWCANCEL(*YES) +
LOCK(*SHRUPD) /* rgzpfm while active */
MONMSG MSGID(CPF2981 CPF3135 CPF9801 CPF9809 +
CPF9810 CPF9820 CPF2982) EXEC(GOTO +
CMDLBL(ERROR2))

ENDJRNPF FILE(&MBLIB/&MBFILE) JRN(QGPL/RGZPFM)
MONMSG MSGID(CPF9803) EXEC(GOTO CMDLBL(ERROR4))

Thanks
Paul

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Monday, November 18, 2013 4:24 PM
To: Midrange Systems Technical Discussion
Subject: estimating RGZPFM runtime

How can I estimate down-time required to RGZPFM a file? Is there a
utility or rule-of-thumb on these?

Can it be killed if not completed OR will the file then be compromised?

Thanks!

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit

http://www.symanteccloud.com______________________________________________________________________

--
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.

--
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.

________________________________________________________________________

This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.

________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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.

--
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.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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.

--
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.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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.