Re: Building a logical file with 1 member over a physical file with many members

----- Original Message -----
From: "Jerry Adams" <jerry@xxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Subject: Re: Building a logical file with 1 member over a physical file with many members
Date: Thu, 13 Sep 2007 14:58:35 -0600


Could you use an API or DSPFD to an outfile, then use that to select
just the members that you want? I'm a little brain dead right now (too
much coffee, too few gray cells), but my thought would be to build the
CRTLF command string in an RPG program and then either put it in a
source member for later execution or just use QCMDEXC to run the CRTLF
from within that program.

* Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
615.995.7024
fax
615.995.1201
email
jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>



J M Plank wrote:

We have a physical file that stores the current year's data (GL detail) in
one file for all companies. Older data is stored in separate members by
company and year.

We have the need to run inquiries across years. I must be brain-dead today,
because I can't come up with a *good* solution to access all the members of
the physical file. One minor detail - because of the way historical data is
stored, we currently have 34 members in the physical file. Running CRTLF
FILE(QTEMP/MVGLDET99) DTAMBRS(*ALL) OPTION(*NOSRC *NOLIST) results in the
following job log:
File MVGLDET99 created in library QTEMP.
Number of members not valid for file MVGLDET99.
Member MVGLDET99 not added to file MVGLDET99 in QTEMP.
Secondary help for the second message has:
Message . . . . : Number of members not valid for file
MVGLDET99.
Cause . . . . . : The requested operation was not performed for
file
MVGLDET99 in library QTEMP because the number of members is not valid.
When
adding a logical file member, with either the CRTLF command or the
ADDLFM
command, the number of member names specified on the DTAMBRS parameter
must
be 1 to 32. When a join logical file is created, the number of member
names
specified must be 1 to 256. When a physical file is created, the number
of
members must be
1.
Recovery . . . : No recovery. Report the problem (ANZPRB
command).
I can see why this failed - 34 is not in a range of 1-32.

I next tried to read a list of the physical file members and tried to do an
ADDLFM, but it failed because it was trying to recreate a new logical file
member -- I only want/need 1 member in the logical file pointing to all the
physical file members. I'm hoping this is the step where I was missing
something obvious.

A very clunky work-around was to copy all the physical file members to a
single member in a physical file in QTEMP, then create the logical file over
the new physical file. This works, but it takes 15-20 seconds to copy the
records and the user doesn't like that.

This will likely be used 5-6 times a month, usually at month end, by the
same user. But, this accountant says that several plant managers are
interested in this, so it might be used more widely than that.

Is there another solution I'm just not seeing?

I checked out <
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbafologfm.htm>
but didn't see anything different from what I'm doing. Other searches
seemed to say to create the logical, then use addlfm.

To summarize, I need to be able to access some of the data in 34 members of
a physical file (with more members added after year end), keyed in a certain
order for which a logical file (built over 1 member of the physical file)
already exists. The department head does NOT want more logical files kept
on a permanent basis, but will allow them in QTEMP. There is a naming
standard to the members:
VD##CCYY -- all start with VD, then a 2 digit company number, then the
century and year. Usually, but not always, company 01 will be desired. At
least 5 of the companies are no longer active. But, I didn't see a way to
select members based on VD92* or something similar.

Mark Plank

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.