RPG program using SETLL *LOVAL to position to the start of an overridden file that was opened using OPNQRYF with the KEYFLD() parameter specified, results in missing or no records returned
An RPG program using SETLL *LOVAL to position to the start of an overridden file that was opened using OPNQRYF with the KEYFLD() parameter specified, results in missing or no records returned."
Note that using SETLL *HIVAL to position to the end of the file can have a similar issues. For that case SETLL *END is a better choice.
SETLL *START is the best way to position the cursor at beginning of file.
Diagnosing The Problem
Apply PTF SI57839 and create the data area described in APAR 'SE62752' as well as below :
1) CRTDTAARA DTAARA(YOURLIB/QOPNQRYFKY) TYPE(*CHAR) LEN(1)
2) CHGDTAARA DTAARA(YOURLIB/QOPNQRYFKY *ALL) VALUE('3')
3) CRTMSGQ MSGQ(QGPL/QOPNQRYF)
4) ADDLIBLE YOURLIB
5) Run your program
The data collected with the PTF and data area enabled will show that the RPG program key format does not match the KEYFLD parameters on the OPNQRYF. Here is the message which shows there is a miss match.
CPF9898 Information 40 05/02/18 15:16:17.157545 QDBGETKY QSYS 081F MFU000 SNA_TST *STMT
To module . . . . . . . . . : MFU000
To procedure . . . . . . . : MFU000
Statement . . . . . . . . . : 130
Message . . . . : A KEY MISMATCH HAS BEEN DETECTED WHILE PERFORMING A
POSITIONING OPERATION FOR FILE TRHIST IN LIBRARY *LIBL (FOR
EXAMPLE, RPG SETLL USING *LOVAL). INCORRECT POSITIONING IS POSSIBLE. CORRECT
THE KEY SPECIFICATION IN YOUR PROGRAM TO MATCH THE KEY SPECIFIED ON THE
OPNQRYF KEYFLD PARAMETER.
Cause . . . . . : This message is used by application programs as a general
Resolving The Problem
To correct the problem make the RPG record format match the KEYFLD parameters of the OPNQRYF. Also, switching to use SETLL *START v.s. *LOVAL is a better performer. Without such a change, certain key values may be missed, even when using native CQE ; it depends on the actual field value(s).
For further information on the the use of *LOVAL (*HIVAL) even prior to v7r2 see the link below.
Additional recommended reading:
Classic Query Engine (CQE) and SQL Query Engine (SQE) Differences
For compiling, create a physical file with the same record format name and the same keys as the OPNQRYF will have. For the file definition in the RPG source, use the EXTDESC keyword to specify the phyisical file to be used at compile time.