Question

Vatchy  |   Jun 16 2008  4:20PM GMT

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.

Alicsc  |   Jun 16 2008  4:36PM GMT

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

Sloopy  |   Jun 17 2008  4:04PM GMT

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

Gilly400  |   Jun 18 2008  8:56AM GMT

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.

Alicsc  |   Jun 18 2008  12:34PM GMT

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

Gilly400  |   Jun 18 2008  3:52PM GMT

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.

CRTSRCPF QTEMP/QDDSSRC
ADDPFM QTEMP/QDDSSRC MEMBER
OVRDBF SOURCE QTEMP/QDDSSRC 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 :-

CRTLF Yourlib/YourLF QTEMP/QDDSSRC MEMBER

You should now have your logical file with th keys you want.

Regards,

Martin Gilbert.

Cwc  |   Jun 19 2008  2:32PM GMT

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.

Sloopy  |   Jun 20 2008  3:36PM GMT

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

Cwc  |   Jun 20 2008  6:00PM GMT

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.