|
If I followed Chucks comments correctly, changing to a time stamp--
replaces a journal sequence re-set with the much hated/loved daylight
savings fall forward/back.
Pick your poison.
Maybe time stamp wins because it has more visibility ?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, December 11, 2013 11:00 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: journal record reduction issue using SQL
On 11-Dec-2013 08:07 -0800, Stone, Joel wrote:
After DSPJRN command to an outfile named "jrnflALL", I want to
reduce ALL the journal records to only the oldest and newest pair
(for each JOOBJ/RRN).
So if Sally changed "Vanilla extract" item #123 from 2 oz to 201 oz
to
20.01 oz to 2.01 oz throughout the weekend, I want to reduce that to
the oldest BEFORE journal image and the newest AFTER image when
extracting the journal Sunday evening - to identify NET changes to
that record.
I am using the following SQL and it works well.
INSERT INTO JRNFLB4AF
select * from jrnflALL
where joseqn in
( select min(joseqn) from jrnflALL
group by joobj, joctrr
union
select max(joseqn) from jrnflALL
group by joobj,joctrr
)
order by joobj,joctrr,joseqn
So IT ops did some maintenance Saturday night and IPLed. This reset
the JOSEQN back to 1 for new journal writes.
Now the SQL stmt returns misleading info. The min JOSEQN is no
longer the EARLIEST journal record.
Questions:
- How does JOSEQN reset - does an IPL cause this?
If using Manage Receivers (MNGRCV) set to *SYSTEM, then the system
will indeed attempt to reset the Sequence number on an IPL; according
to the system-managed rules, seen in the following help text:
pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/cl/chgjrn.htm
_i Change Journal (CHGJRN) i_
...
The sequence numbering of journal entries can be reset when the
receiver is changed. If the sequencing is not reset, an informational
message is sent indicating the first sequence number in the newly attached receiver.
If RCVSIZOPT(*MAXOPT1 or *MAXOPT2) is in effect for the journal, an
informational message (CPF7019) is sent to the system operator
recommending that the sequence numbers be reset when the first
sequence number is greater than 9,500,000,000. If RCVSIZOPT(*MAXOPT3)
is in effect for the journal, an informational message (CPF7019) is
sent to the system operator recommending that the sequence numbers be
reset when the first sequence number is greater than 18,446,600,000,000,000,000.
Otherwise, CPF7019 is sent when the first sequence number is greater
than 2,000,000,000.
...
_Manage receivers_ (MNGRCV)
Specifies how the changing of journal receivers (detaching the
currently attached journal receiver and attaching a new journal receiver) is managed.
...
Also, if the journal receiver was attached while RCVSIZOPT(*MAXOPT1 or
*MAXOPT2) was in effect for the journal, the system attempts to
perform a CHGJRN command to reset the sequence number when the journal
receiver's sequence number exceeds 9,900,000,000. If the journal
receiver was attached while RCVSIZOPT(*MAXOPT3) was in effect for the
journal, the system attempts to perform a CHGJRN command to reset the
sequence number when the journal receiver's sequence number exceeds
18,446,644,000,000,000,000. For all other journal receivers, the
system attempts this CHGJRN when the sequence number exceeds 2,147,000,000.
Additionally, during an initial program load (IPL) or the vary on of
an independent ASP, the system performs a CHGJRN command to create and
attach a new journal receiver and to reset the journal sequence number
of journals that are not needed for commitment control recovery for
that IPL or vary on, unless the RCVSIZOPT is *MAXOPT3. The sequence
number will not be reset and a new journal receiver will not be
attached if the RCVSIZOPT is
*MAXOPT3 unless the sequence number exceeds the sequence number
threshold which is 18,446,600,000,000,000,000. If there are
outstanding commitment control transactions associated with one of
these journals, a new journal receiver will be attached but the
sequence number will not be reset. If there are less than three
journal entries in the attached receiver for one of these journals at
the time of the IPL, the system will not attempt to attach a new journal receiver.
..."
- What is a good workaround? Should I always add 1,000,000,000 to
JOSEQN when processing? Will this do the trick (forcing the post-IPL
sequence numbers to the end)? Or is that problematic?
The use of a constant is sure to eventually be exposed as a poor
idea; i.e. another situation of incorrect\undesirable results, likely
would occur eventually. But even a /calculated/ value is potentially difficult; i.e.
potentially there could be multiple incidents of Sequence Option
(SEQOPT) having been *RESET either during IPL activity or by explicit
request on the Change Journal (CHGJRN) command for which multiple
increment-values would have to be determined\calculated and then
applied to the JOSEQN values following each successive reset.
- Should I add JODATE || JOTIME to the min and max stmt?
Not as easy to _add_ as to _replace_ the determination of the
min\max using the date\time vs sequence number; using JOTSTP [the
timestamp vs separate\concatenated DATE and TIME], if available. Be
conscious of the sensitivity to time vs sequences; i.e. any date\time
changes to a
*prior* value may be just as problematic as a sequence number being reset.
Any ideas would be appreciated.
Switch to User Managed, or use *MAXOPT3 for the Receiver Size
Options
(RCVSIZOPT) to limit the effects of System Managed reset of the
Sequence numbers during IPL. In either case, be sure to schedule the
SEQOPT(*RESET) activity to prevent an interruption in the availability
of the journal receiver. If one could presume the ability to be
necessarily proactive while allowing for recovery-time from any
restrictions on the reset of the sequence number, then no other change
except to schedule the CHGJRN SEQOPT(*RESET) is a possible alternative.
--
Regards, Chuck
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
