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)
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.
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).