On 28-May-2011 10:12 , Jon Paris wrote:
Maybe my memory is failing me but I'm sure in the past I've used SQL
to scan multiple source members by specifying an OVR to member *ALL.

But when I try to do it I get an error message *ALL in *N type *MEM
not found on the SELECT * FROM ... Tried it on V5R4 and V7 just in
case it was a PTF thing but same result.

I can see various posts on assorted lists that indicate that others
have used the technique, but the actual code was not shown so if I'm
missing some "magic parm" I couldn't see it. Is my memory just that
bad or ...

I have decided on an alternative approach for the task I wanted this
for - but would still like to know what I'm missing here.


As I recall, the SQL SELECT always has issued an error message for an attempt to refer to [at least any non-partitioned] database *FILE where the name was overridden to refer to a database file using the MBR(*ALL) specification.

Beyond that, comments which may or may not be of any interest...

With the partitioned TABLE support [as an extension to the DB2 MultiSystem support] I infer that specific restriction may have been lifted only for when a non-SQL interface has been directed against a partitioned TABLE [a "partition file" as noted in the CPF4268, which may precede the SQL0204] with the intent to access all of the data versus the data from just one member. The reason...

The native access for Common Data Management would have the non-SQL Open method still processing any individual member of the partitioned TABLE [e.g. *FIRST by default for most interfaces], so some type of means to request *ALL for the member must be utilized to access all of the data. And for any interfaces for which there were no special value *ALL, I suppose that would leave just the override to database file MBR(*ALL) specification; though even the member name on the QRYFILE() parameter of RUNQRY supports *ALL, with QRY1613 issued when the named file is not a partitioned TABLE.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbmult/partrestrict.htm
The mention of DLTNETF in the above doc seems in error.

AFaIK... If the access to the multiple members of a TABLE does not reflect the relational model as provided by the SQL partitioning, then the SQL request should continue to be prevented. Thus for accessing multiple members of a file that do not implement SQL partitioning, I expect the SQL would need to be formulated instead, as a UNION of all of the members. Having the PARTITION function available to identify which member of the data for a non-partitioned multi-member database file would seem to be a minimum requirement to make a SELECT against MBR(*ALL) appear relational.?

FWiW I noticed that the CPF4152 seems to suggest that the MBR(*ALL) is not supported for a DB2 MultiSystem distributed partition file, although that seems moot since AFaIK such files are limited to MAXMBRS(1).

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].