Building the Key in RPG/400 itself

370 pts.
Can we able to build key for externally described file in RPG/400 itself to retrieve records on this Key?. IF we can Pls.......give sample code.

Answer Wiki

Thanks. We'll let you know when a new response is added.


I’m not sure I follow what you mean by this. Do you mean build a logical file from an RPG program with key fields determined by the program?

Can you give a more detailed explanation of what you want to do?


Martin Gilbert.


The simplest way to build a “logical file with keys” is to use embedded SQL to run a CREATE INDEX statement. Next simplest but much more flexible, use QCAPCMD or QCMDEXC to run an OPNQRYF command.

Neither of those is particularly difficult.

However, there is no way to use either one in the program if random keyed access is the purpose. The file format with its key structure must exist when you compile the program. Creating it at run-time won’t work. The exception is when you create the LF to match a format that the program is already compiled over.

But that doesn’t seem to be what the question needs to know.

If dynamic creation of a keyed LF must be combined with random keyed access in the same program, you will need to investigate MI functions at a much deeper level.


Discuss This Question: 13  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Vatchy
    There are two methods you can use for this. You can using embedded SQL which will allow you to select records on the fly using any fields for the keys. You can also use a CL program - either before calling the RPG program or in the middle - to select records using OpnQryF - open query file. Either way would work but if you have SQL on your system then it would be easier to use embedded SQL.
    1,415 pointsBadges:
  • Alicsc
    Hi Martin/Vatchy I am Ali. Yes I mean to build a logical file( can we use same externally described file(PF)) from an RPG program with key fields determined by the program?. I want to build keys for PF in the RPG itself. Thanks, Ali
    370 pointsBadges:
  • Sloopy
    Hi, Alicsc. I suggest you read the section on defining program-described files in chapter 15 of the Websphere ILE RPG Development Guide at You will be able to see that you can't just decide to read a file by any 'key' you want - the key has to already exist as an access path created through DDS (or by having copied or duplicated a file that is keyed), even if you define the file as program-described in your RPG program. Of course, as Vatchy has said, you can use embedded SQL or OpnQryF to create an access path for the duration of the program. But you can't build keys for an existing PF or a LF inside an RPG program; you can only use the keys that are currently defined. Sloopy
    2,195 pointsBadges:
  • Gilly400
    Hi, You *could* create a logical file by writing a DDS source file from your RPG program and calling QCMDEXC to run a CRTLF to build the logical file, then use an OVRDBF to access that file from your program, but I wouldn't recommend this. Regards, Martin Gilbert.
    23,730 pointsBadges:
  • Alicsc
    Hi Martin, Can you pls give one example for this statement 'You *could* create a logical file by writing a DDS source file from your RPG program ?. I did not get this how to do. Thanks, Ali
    370 pointsBadges:
  • Gilly400
    Hi Ali, I wouldn't recommend this way of doing this, as it can get quite messy, but here's an overview of how it works :- First create a Source File in your QTEMP library, and add a Source Member to it. I would take care of this in a CL program before your RPG. OVRDBF to your Source File & Member.
    CALL Yourpgm
    In Yourpgm you will need the file SOURCE defined for output. Then you write one record for the definition of the record format and one record for the PFILE, followed one record for each of the keys you need in your logical file. Then from Yourpgm you execute a CRTLF using QCMDEXC :-
    You should now have your logical file with th keys you want. Regards, Martin Gilbert.
    23,730 pointsBadges:
  • Cwc
    Instead of creating the file through DDS, I think it would be much cleaner to create the file via embedded SQL, with a Create Index statement. That way, the one step would take care of it all. However, I question what the value of creating a dynamic index or logical file really would be, as it would take time to create the index, depending on how many records are in the physical file or table. And would you then be keeping that index, or deleting it once you're done? Without knowing how your database files are designed, it still sounds like a lot of extra overhead to me. You'd be better off using embedded SQL to run a Select statement to retrieve the result set you want and then loop through each record for whatever processing you want to do. Also, you mentioned RPG/400 -- I hope you're not using RPG III to do this? If so, I urge you to use the modern version of the language (RPG IV), which gives much more flexibility and power over what you can do and how you can accomplish it.
    4,290 pointsBadges:
  • Sloopy
    Correct me if I'm wrong (which is often the case), but, CWC, can't an SQL index only be used by SQL? You wouldn't be able to open it in a standard RPGIV program? But I do agree with the thrust of your comments - what is Ali trying to do? If, Ali, you want to build a keyed view of your data based on what users enter on a screen - well, this is a good job for embedded SQL. You should not be thinking of creating a logical file 'on-demand' for screen displays! Creating a logical file, or an SQL view, is a matter of analysis and judgement. These things are expected to be permanent, and so you would create a keyed view when that view will make your day-to-day processing faster and less complicated. Especially if the based-on physical file is large! There are plenty of methods for speeding up screen programs where the users can make lots of different selections. Some depend on a good stock of logical files with different, useful keys; and others depend on embedded SQL, which is a better way to go than creating logicals just for a particular screen. OPNQRYF is not nowadays a good method. Ali, we can give you more specific advice if you can tell us WHY you want to create logicals from inside your RPG programs, maybe giving us some examples....? Regards, Sloopy
    2,195 pointsBadges:
  • Cwc
    Hi Sloopy, Yes, an SQL index can be used inside of an RPG or other HLL program. This is because an SQL index is implemented as a logical file, so an application program doesn't know the difference. However, an SQL statement cannot refer directly to an index; it needs to refer to the base table or physical file, and then the SQL query engine will choose the index it uses to access the data behind the scenes. So an embedded SQL Select should use the physical file name, while an application program can use either one, depending on what keys it needs. I learned more on this when one of our important data files exceeded its maximum record count, and we implemented DB2 Multisystem Support as a solution. So we have an SQL index over a multiple membered file, and that index is built over all members/SQL partitions. And our application programs just see one large file with the same keyed access they had before.
    4,290 pointsBadges:
  • Yorkshireman
    OPNQRYF woudl seem to be a way to go here..
    6,085 pointsBadges:
    My favorite is when I have two files and need joined records keyed by fields in both files. I create a "format" work file with the fields from both files. the OVRDBF is the first file overriden to my work file. The program references my work file for the compile. Use OPNQRYF join the 2 files and build the keys.The work file goes away when the job ends.The same workfile can be used over for a different sequence.
    1,235 pointsBadges:
  • Splat
    You can create keys on the fly using OPNQRYF and process them in an RPGLE program. The following fragments are from some programs written some time ago:
                 MBR([member name]) SECURE(*YES) SHARE(*YES)       
    OPNQRYF    FILE((QTEMP/QADSPDBR [member name]))               +
               QRYSLT('(WHNO *NE 0)')                       +
               KEYFLD((WHRLI)                               +
                      (WHRFI)                               +
                      (WHRELI)                              +
    FQadspdbr  If   f  372    30aiDisk    keyloc(1)   
    D Ddbr          e Ds                  extname(qadspdbr)
    D  dbjref       e                     extfld(whjref)   
    D  dbsysn       e                     extfld(whsysn)
    D                 Ds                
    D  qwhf01                 1     20  
    D  qwhf02                 1     30
    IQadspdbr  ba  02                              
    I                                  1  372  ddbr
    Csr   qwhf01        SetLl     Qadspdbr                                 
    Csr                 Dow       not *in31                                
    Csr   qwhf01        ReadE     Qadspdbr                               31
    Csr                 If        not *in31                                
    Csr                 EndDo
    Let me note that even though it can be done, it's not something I'd likely do again - this was as much an experiment as anything else. But the program, originally written in 1988, does still work.
    12,915 pointsBadges:
  • TomLiotta
    You can create keys on the fly using OPNQRYF... You can indeed create keyed files with OPNQRYF. But that's not creating the key in RPG, nor does it allow RPG/400 to access a file by key if the program isn't already written and compiled to process a keyed file. If an externally-described file is keyed and the program is written to access by key, there doesn't seem to be any point to the original question unless it's asking about accessing by key when the program doesn't already define the file as keyed. OPNQRYF won't help with that. The point of the question has unfortunately never been made. Tom
    125,585 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: