IBM Support

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

Troubleshooting


Problem

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.
 


Cause

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
                                       escape message.

   


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.

https://itknowledgeexchange.techtarget.com/itanswers/usage-of-figurative-constant-loval-with-setll/

https://www.mcpressonline.com/programming/rpg/dont-be-misled-by-setll-loval

Additional recommended reading:

Classic Query Engine (CQE) and SQL Query Engine (SQE) Differences

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Classic%20Query%20Engine%20(CQE)%20and%20SQL%20Query%20Engine%20(SQE)%20Differences

NOTE:

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.

 


Document information

More support for: IBM i 7.3

Software version: All Versions

Operating system(s): IBM i

Reference #: 0870826

Modified date: 07 February 2019