James H. H. Lampert wrote:
I've just isolated a problem that's occurring with SQL views
that have selects/omits, but NOT with DDS logicals that have
functionally the same selects/omits.
Is the S\O logic via cursor selection [DYNSLT], index\key
selection, or a combination? SQL access can not be assumed to have
any specific implementation; i.e. the query implementation may or
may not be via an index, and even if by index, the index may or may
not have any select\omit logic with key length and number of keys
similarly unpredictable.
It seems that we have a call equivalent to an RPG SETLL,
followed by one equivalent to an RPG READ.
Does not the SETLL require a keyed [access path] open.? SQL
access never uses QDBGETKY [which IIRC SETLL does], only QDBGETSQ
and QDBGETM; i.e. only relative position access. Or probably I am
forgetful, such that a non-keyed SETLL is direct positioning for
*START, *END, and RRN? Hmmm... maybe QDBGETDR is used by SQL and I
forgot that too.
Code the /same/ processing in an RPG program, reproduce the
error, and report it as a defect :-)
If that SETLL-equivalent is for a record that is omitted, it
fails. On the DDS logical, we're able to recover, but not on
the SQL view. On the SQL view, we blow up with an MCH1825 and
a CPF5147
Fails how? With MCH1803 perhaps? What OS database program is
called and which LIC program gives what SLIC exception [message ID
is acceptable]?
What is the control list used for the SETLL. Perhaps I am using
the wrong term; the intent is to determine what input to the data
management database I\O request is being used to identify which row
to position to?
What happens in the DDS LF scenario for its failure and what is
the recovery action(s), as compared with the equivalent SQL scenario
which fails with the noted error? Is any recovery attempted from
the VIEW failure, or is the cursor deactivated? IIRC the noted
failure is a terminating condition effecting deactivate by #DBDACR,
but in response to a prior msgMCH1803 [perhaps also for msgMCH1823];
i.e. if the prior error is handled properly, the cursor can remain
active.
What did the query implementation show [debug messages may be
sufficient] for the failing run? Perhaps the query effected
index-only access method. I am not sure, but that method may
require relative positioning requests only, query ODP or not.
Although I had intended to code to that method for some tooling,
AFaIK only a query ODP has code supporting that; i.e. I have no
experience coding to that access method.
Any idea what could be wrong?
The error msgMCH1825 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 :-)
FWiW a keyed operation which fails due to accessing a row that
was removed from the index by key selection will leave the cursor
with "no current position". Thus the RC2 presumably was the Return
Code seen for the CPF5147.? I know the query component code had
several times over the years experienced very similar issues for
recovery from prior failed access of rows which were removed from
either the index selection or from the cursor selection; so probably
APARs with kwd msgMCH1803 will describe similar, for example:
http://www-01.ibm.com/support/docview.wss?uid=nas304338f102193a5c78625717a0073bcb4
(I could be more specific, but I wouldn't want to be obligated
to kill the entire List.) ;-)
The LIC & OS programs & procedures that sent each message were
not noted, nor was the RC for the error msgCPF5147 noted. No
secrets would be revealed by having given that, as minimally
appropriate failure detail. :-)
Regards, Chuck
midrange.com
