|
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
