RE: Building a logical file with 1 member over a physical file with manymembers

Mark,

If you're at v5r3 or higher consider re-creating the file as an SQL defined partitioned table.

Behinds the scenes, the OS creates a multi membered PF just like you have now. You should still be
able to have all the logicals you have now and you shouldn't even have to recompile any programs since
you should be able to keep the File level ID the same for the PF and any LFs. Not 100% sure on that
as I've never replaced a multi-member PF with a partitioned table. But I know it works replacing a
single member PF with a SQL defined table.

The benefit to you is that when you use SQL to access the file, you will automatically be accessing
all the data in all the members.

Just a thought,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of J M Plank
Sent: Thursday, September 13, 2007 3:25 PM
To: Midrange Systems Technical Discussion
Subject: Building a logical file with 1 member over a
physical file with manymembers

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.js
p?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.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.