MCH1825 - Problems with select/omit in OPNQRYF on an LF (Paging Dr. Pence)

[Jonathan Ball]

We have a batch job that intermittently gets an error MCH1825 followed
by CPF5147. The job does an OPNQRYF on a logical file specifying
QRYSLT. It then calls an RPGLE program that positions the file using a
relative record number in SETLL. It then reads from the open data path
and processes records until it reaches the highest RRN it is to process,
or EOF.

I saw a fragment of a nearly identical discussion of this in
COMP.LANG.AS400.MI from spring 2010, in which Chuck Pence participated.
In that case, an MI programmer (James Lampert) was using a SQL view
rather than an LF, but he was able to simulate the conditions in RPG,
and I believe he also produced the error using an LF.

In the fragment of the thread I could find in the other group, Chuck
wrote the following:

The error msg MCH1825 pretty much says it all ;-) That is.... The
rule option that was specified on the data access method is not
valid for the active cursor because the "cursor is over the data
space index, but the current cursor setting allows only the data
space relative, or the ordinal rule options." Accordingly, you
need to specify a different rule option :-)


Unfortunately, "rule option for cursor" doesn't really mean much to me,
nor does "allows only the data space relative, or the ordinal rule options."

One thing I did notice as a difference between the failing jobs and
other run instances that don't fail. In the failing jobs, CPF4123 is
thrown when the RPG program opens the file in the OPNQRYF statement.
For some reason, the programmer specified SEQONLY(*NO) on the OPNQRYF
statement, but the CPF4123 type code is 8, which says "The arrival
sequence option is not the same as that specified on the first open
operation." That suggests to me the RPG program wants to do
sequential-only processing. But the RPG program execution is the same
every time, so I don't know why it wouldn't throw that diagnostic every
time.

Finally, after the MCH1825, the job issues CPF5147 with a reason code 2:
"A get-next-unique, get-previous-unique, get-next-equal,
get-previous-equal, or get-same operation was requested, but the current
position is *START, *END, or there is no current position."

I determined that the starting RRN the program was using to try to
position the file did not exist in the file. However, when I attempted
to replicate the run conditions, I didn't get the error. I did SETLL
with a record number value that I knew to be in the middle of a range of
deleted records. When my test program then did a READ, it simply went
to the next record in the file that met the selection criteria in the
QRYSLT parameter on my OPNQRYF command.

I was not able to determine if a solution was ever found to the earlier
similar problem discussed in COMP.LANG.AS400.MI.

[CRPence]

On 11-Jan-2012 16:10 , Jonathan Ball wrote:
> We have a batch job that intermittently gets an error MCH1825
> followed by CPF5147.

RC2 noted later, for the DM I\O error.

> The job does an OPNQRYF on a logical file specifying QRYSLT.

Keyed LF? And what KEYFLD(), and what [matching?] keys for the compile?

> It then calls an RPGLE program that positions the file using a
> relative record number in SETLL. It then reads from the open data
> path and processes records until it reaches the highest RRN it is
> to process, or EOF.

If a non-keyed LF with KEYFLD(*FILE), I think that would ensure
arrival sequence.? At least for a non-keyed PF as I recall.

> I saw a fragment of a nearly identical discussion of this in
> COMP.LANG.AS400.MI from spring 2010, in which Chuck Pence
> participated. In that case, an MI programmer (James Lampert) was
> using a SQL view rather than an LF, but he was able to simulate the
> conditions in RPG, and I believe he also produced the error using an
> LF.
>
> In the fragment of the thread I could find in the other group,
> Chuck wrote the following:
>
>> The error msg MCH1825 pretty much says it all ;-) That is.... The
>> rule option that was specified on the data access method is not
>> valid for the active cursor because the "cursor is over the data
>> space index, but the current cursor setting allows only the data
>> space relative, or the ordinal rule options." Accordingly, you need
>> to specify a different rule option :-)
>
> Unfortunately, "rule option for cursor" doesn't really mean much to
> me, nor does "allows only the data space relative, or the ordinal
> rule options."

The former refers to "read key equal" or "position to RRN" or similar
read\positioning request, and the latter more specifically refers to the
positioning "relative from current position" or a "specific RRN".

> One thing I did notice as a difference between the failing jobs
> and other run instances that don't fail. In the failing jobs, CPF4123
> is thrown when the RPG program opens the file in the OPNQRYF
> statement.

Debug messages or monitor details may show the implementation details
and differences for each scenario.

> For some reason, the programmer specified SEQONLY(*NO) on
> the OPNQRYF statement,

Since the query was probably forcing the SEQONLY(*NO) in testing, and
a message to that effect was being left in the joblog... the programmer
probably figured explicitly specifying SEQONLY(*NO) is "cleaner".

> but the CPF4123 type code is 8, which says "The arrival sequence
> option is not the same as that specified on the first open
> operation." That suggests to me the RPG program wants to do
> sequential-only processing. But the RPG program execution is the same
> every time, so I don't know why it wouldn't throw that diagnostic
> every time.

The default for many RPG file spec is to request sequential-only;
IIRC the BLOCK(*NO) enables otherwise. While a message is issued when
SEQONLY(*YES) can not be effected, I am not sure any message is issued
when sequential-only processing is enabled even though SEQONLY(*NO)
suggested that sequential-only was not mandatory. Reviewing the help
text for OVRDBF SEQONLY() parameter, notice the wording for *NO suggests
that "The database file is not restricted to sequential only
processing."; a bit clearer than the help on OPNQRYF SEQONLY() parm.

The ODP [Open Data Path] created for the OPNQRYF is opened according
to the specifications on the Open Query File command plus the
implementation as defined by the access plan. The RPG program is
"sharing" the ODP created by the query, which is apparently not always
being opened the same; evidenced by the diagnostic sometimes but not
others. For example consider a query which implements as a sort with a
temporary result versus a query implemented with an index\access-path.
The OPTIMIZE and ALWCPYDTA are two important parameters in the decision.

With a bunch of details like the F-spec, the open, I\O request, the
file definition accessed at run-time, file definition used for compile,
the full OPNQRYF specification and any overrides, and the optimizer
debug messages for a failing and successful request I might be able to
setup a scenario to refresh my understanding to better explain what is
probably happening.

> Finally, after the MCH1825, the job issues CPF5147 with a reason code
> 2: "A get-next-unique, get-previous-unique, get-next-equal,
> get-previous-equal, or get-same operation was requested, but the
> current position is *START, *END, or there is no current position."
>
> I determined that the starting RRN the program was using to try to
> position the file did not exist in the file. However, when I
> attempted to replicate the run conditions, I didn't get the error. I
> did SETLL with a record number value that I knew to be in the middle
> of a range of deleted records. When my test program then did a READ,
> it simply went to the next record in the file that met the selection
> criteria in the QRYSLT parameter on my OPNQRYF command.

Effectively that is how to go about re-creating, but a bit more
complex because a query ODP includes no previously-deleted rows; i.e.
deleted rows can not be selected directly, neither via index nor physically.

I do not have what is necessary to investigate due to limited access
to a system with any capabilities to test and refresh. Basically the
RPG has "lost" its position by setting the RRN position to a row which
was perhaps: deleted, does not exist in the result set, by a change has
disappeared from the result set [no longer meets selection], or is in
physical processing but omitted by dynamic selection. For a keyed ODP,
AFaIK there should be no request to SETLL except to *START or *END to be
sure to avoid the error.

> I was not able to determine if a solution was ever found to the
> earlier similar problem discussed in COMP.LANG.AS400.MI.

As I recall that was a similar case of using position by RRN against
a query [as noted, implemented by a SQL VIEW versus an OPNQRYF ODP]
which similarly might implement via an index, and the OP was coding to
the Database I\O programs outside of [and effectively mimicking the
run-time I\O requests of the] RPG... IIRC, as I did not go back and
re-read the thread [which moved to another forum] nor some of the draft
messages I never sent... if even I have those, since my Thinkpad laptop
died and I switched to a new system w/out carrying over any data :-(

I looked up the archived message threads:
"Problem with an SQL View that has selects/omits"
http://archive.midrange.com/mi400/201003/threads.html#00018
"Failed SETLL leaving program at undefined position in SQL VIEW, but not
in DDS LF"
http://archive.midrange.com/rpg400-l/201003/threads.html#00407

Regards, Chuck

[CRPence]

On 12-Jan-2012 01:36 , CRPence wrote:
> On 11-Jan-2012 16:10 , Jonathan Ball wrote:
>> Finally, after the MCH1825, the job issues CPF5147 with a reason
>> code 2: "A get-next-unique, get-previous-unique, get-next-equal,
>> get-previous-equal, or get-same operation was requested, but the
>> current position is *START, *END, or there is no current
>> position."
>>
>> I determined that the starting RRN the program was using to try to
>> position the file did not exist in the file. However, when I
>> attempted to replicate the run conditions, I didn't get the error.
>> I did SETLL with a record number value that I knew to be in the
>> middle of a range of deleted records. When my test program then did
>> a READ, it simply went to the next record in the file that met the
>> selection criteria in the QRYSLT parameter on my OPNQRYF command.
>
> Effectively that is how to go about re-creating, but a bit more
> complex because a query ODP includes no previously-deleted rows;
> i.e. deleted rows can not be selected directly, neither via index
> nor physically.
>

Another thing to consider. I believe a direct positioning request
[i.e. specific RRN] would operate against the primary dataspace of a
query unless the query effected a temporary result, whereby the RRN
positioning instead would be within that temporary file result-set
[wherein no deleted rows would be included]. If the query ODP were
generated using OPTION(*ALL) or with a combination including *UPD and\or
*DLT, that would influence the implementation, because updates against a
temporary result would be moot; like, as previously noted,
specifications on OPTIMIZE and ALWCPYDTA might influence the query
implementation. A direct positioning request to a deleted row [or to
any row which is excluded from selection by the query] I presume would
not have a valid position within the query result set.... and thus could
give rise to the error when any "rule option" is specified for which a
valid position must currently be established in order to function properly.

FWiW I am not sure why a "direct positioning access" method\request
is even allowed against a query ODP, except to *START or *END perhaps.
The SQL queries only support "[relative\scrollable] sequential access"
and non-SQL query ODPs additionally support "keyed access" methods"
[when implemented via index per KEYFLD()], but direct positioning makes
no sense within a query result. That the "get direct" requests are not
prevented [by a hard error] may either be an oversight or simply
overlooked because, perhaps, sometimes the database feature itself may
utilize the capability and perhaps also there was a presumption that
nobody else would combine them.? However... since that has long been
allowed, they would unlikely prohibit that capability now, nor likely
issue a warning, even if that were decidedly a good idea [had that been
the design since the inception, to prevent the get-direct access method
against the query ODP].

> For a keyed ODP, AFaIK there should be no request to SETLL except to
> *START or *END to be sure to avoid the error.

Revision: For any query ODP, AFaIK there should be no requests to
SETLL for non-keyed positioning to be sure to avoid the error. Even
*START and *END are subject to the vagaries of changed implementation,
plus neither of those non-numeric [special value] choices establishes a
RRN position.

As alluded in the earlier portion of my reply... I expect that
temporary versus non-temporary results may experience different effects
when using SETLL for direct positioning. However I also expect that
several different types of temporary results are possible per query
implementation; e.g. a "sort temporary" is probably "the" result-set.
Yet there is the potential to have either "cursor selection" [i.e. much
like DYNSLT] to further refine the selected data or a [derived] index
used to further refine the selected data, from a temporary result which
includes more rows than the selection specified; i.e. such that some
rows which could be positioned-to within the temporary result are not
actually part of the result-set. Thus I am not sure that even the
guarantee of a temporary result as implementation would ensure avoiding
problems with attempting direct positioning on the query ODP; i.e. not
all "temporary result" queries are necessarily capable of tolerating
get-direct positioning [even if that might be inferred from results of a
query that /always/ implements as a sort temporary].


I suppose now, with these additional thoughts, I could try some
variations to get a recreate to better understand. But since there is
little value to me, and my users access has minimal capabilities [e.g.
no access to trace], and I already fail to follow up on so many other
"interesting" things to play around with, I doubt I will make the time.

Regards, Chuck