Deletion of records from a multi-member file by condition

[d7d1cd_]

Hey. There is a multi-member physical data file. Is there a “simple” way to remove from all its members records that satisfy a certain condition? For example, you must delete all records for which the DATA symbol field contains the text "delete". If there was one member in the file, then I would use something like this SQL expression:

delete from FILE where DATA like '%delete%'

But in my case there are several members and this expression must be applied to each of them.

[Vectorspace]

Not easily, no. SQL does not have a concept of multi member files.

SQL will always refer to the *FIRST member - though (I think) it does respect member overrides.

So the best thing I can think of is to DSPFD FILE(<myfile>) TYPE(*MBRLIST) to an outfile to get a list of members, then loop through that list, then for each member do an OVRDBF to that member, then run the delete SQL

[Ashish.Kulthe]

First way and the easiest way would be to declare the PF with EXTMBR(*ALL) in the RPGLE program later loop the records and delete as per the purge condition


================================================== ============
Second way would be to create a LF (logical file) over this PF (physical file). [CRTLF () DTAMBRS(*ALL) ]
The LF created will show records from all the members of that PF.
A simple RPGLE would be helpful to delete the matching records.

Note: When new members get added to PF, then delete LF member with RMVM and add again using ADDLFM



================================================== ============
Third way would be to loop through the list of members from the PF and delete the matching rows.

SQL to get the member list of a PF
Select TABPART
from SysPartitionStat
Where SYS_DNAME = {LIBRARY_NAME}
and SYS_TNAME = {PF_NAME}
Order By PARTNBR Desc

In RPGLE declare the file USROPN and use EXTMBR({Variable_name}) keyword, move the member name to the stand alone variable open the file and post delete close the file.

[d7d1cd_]

In the second method, can a logical file be created only through DDS?

[Vectorspace]

Yes and no. A Logical File can be used to do a number of things, including alternate key sequences, remote or reorder columns, select/omit records, or perform joins. SQL has equivalents to most of those (SQL indexes and views both create Logical Files). But a Logical File that targets a specific member of a table - SQL cannot create one of those.

Instead, SQL has the ALIAS command., which is functionally equivalent. It creates an object that is a "shortcut" to a specific table, and can be used directly in an SQL statement just like if it was a view. It can even target a specific member of that table if it's a multi member file. But note it does not create a logical file, it instead creates a DDM file (though it targets the local system specifically)

[d7d1cd_]

Yes, I know about file aliases and how they can point to a specific member. But to solve my problem, it is necessary to create an alias for each member separately. It's a pity that you can't create a nickname for all the members at once.

[CRinger400]

I don't know if this is possible in SQL .

OVRDBF FILE(SomeFile) MBR(*ALL)
Then read it in RPG, use RPG READ/DELETE opcodes.
If you need the member name, it's in the INFDS.

Or you could loop through the member names in CL, OVRDBF to the current member name, then run SQL delete.

Ringer