Thanks for all the info!!
Is JOTSTP new in V6 or V7? Or is this a "hidden entry"?
I don't see that field in my DSPJRN output.
If the system date/time can be "backspaced", is there a bullet-proof method to ensure the pgm is finding the oldest and newest entry? Add the journal receiver name into the MIN()? Is that even available in the *outfile of DSPJRN?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, December 11, 2013 12:00 PM
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.
midrange.com
