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