Re: SQL weirdness: can anybody explain this malfunction scenario with a customer's file?

[CRPence <CRPbottle@xxxxxxxxx>]

  Hmmm, I figured that would have been reported as a defect to IBM 
the first time it was posted here without any replies; that seems 
the appropriate response.  I would suggest however, clarifying the 
re-create scenario.  For example the "if use RLA" and "unless DFU 
first" are best described as steps of explicit actions, if only 
because, as stated, they seem to be the same RLA but in conflict for 
"first" versus "unless"; or was the RLA something other than DFU.? 
Also by review of DSPFD against the PF, that will clarify if the 
file was created by SQL; i.e. if there is a lack of indication that 
the "SQL type" is "TABLE", then the physical file was not created 
with SQL.

  There had been a similar past defect which was a problem with the 
VIEW object.  IIRC something to do with the Access Plan in the VIEW 
or something else about the VIEW whereby the first open had effected 
a /self correction/ of sorts.  I am not sure if it might not also 
have been related to SQE performing the query for the native\RLA 
versus CQE; record of debug messages might be useful too, to include 
in the recreate materials.  I would expect that a step-by-step of 
restoring just the PF and then creating the VIEW followed by each 
access will give necessary re-create for a developer to investigate 
the origin.

Regards, Chuck

James H. H. Lampert wrote:
> Can anybody explain why this would happen under V5R4:
>
> A customer's physical file, FOO, in library FROBOZZ. Don't know
> whether it was created with a CRTPF or in SQL.
>
> It has an unkeyed logical file that was created as an SQL view: 
> CREATE VIEW FOOV2 AS SELECT FOO01, FOO02, FOO03, FOO04, FOO05, 
> FOO06, FOO07, FOO08, FOO09, FOO10, FOO11, FOOBAR FROM FROBOZZ.FOO
> WHERE FOOBAR <> 'HISTORY' RCDFMT FOOREC
>
> This should produce a bunch of records that meet the
> where-clause. Thousands, in fact.
>
> It seems that if you look at the logical using native
> record-level access, though, you initially get one record that
> meets the where-clause, followed by a bunch that don't. Unless
> you open the file in DFU first. Then, after perhaps some
> intermittent recurrence of the problem until you sign off and
> sign back on, it behaves as intended.
>
> Then, if you remove all of the logicals, and the physicals, from
> the library, and restore them from the save file from whence they
> came, expecting the malfunction to return, it STILL behaves
> correctly.
>
> (The file, library, and field names have been changed to protect
> the innocent.)