A DDS logical file over multiple members is implemented as having
multiple formats; a MFLF Multi-Format Logical File. This is considered
non-relational, and thus DBXREL='N' in QADBXREF which prevents the file
from being visible in the SQL catalog VIEWs. That although similar to a
UNION, it is a different animal in its implementation. The SQL is able
to access only the first format and first member of any file --
excepting override or ALIAS to a specific member, but a member in a MFLF
may not have format *FIRST.
The ALIAS can be used to combine members in a run-time SELECT just as
if each were a separate TABLE. An ALIAS can not be referenced in a
VIEW. The ALIAS is persistent; implemented via the DDM file, just
create them and leave them as effective symbolic links. There are few
reasons to use an ALIAS except for directing to a specific member of a
DDS created file.? As just a link for a name, what happens to the file
to which the ALIAS points is moot until run-time; if the named
file.member exists [as named as reference in the creation of the ALIAS],
it will be used. The file.member does not have any knowledge that an
ALIAS references it, so any action can be performed [e.g. remove member,
rename file or member] that might break the link.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

ga wrote:
> We are using a lot of excel, importing external data using MS query
> and then getting data from the iseries through odbc. Very powerful,
> very easy to use. We don't use the query wizard, just use data,
> import external data in excel and go from there.
>
> Having some problems with multi-member files. Also, logical files
> show up and can be accessed, but I have some logical files built over
> multi-member files and also multi-member files that are in more than
> one library. For some reason they don't show up for me to select under
> the selection criteria.
>
> I'd like to talk/communicate with someone who is a litle more advanced
> on the use of these than I am. If anyone with more knowledge than me
> would be willing to share some information I'd appreciate it.
>
> For instance, can I create some SQL aliases over the physical files
> that enable me to combine the members? If I do this, are those
> aliases "persistent"?? Is there any risk to creating aliases in SQL
> over DDS files?? I mean, if the file somehow gets reorganized, does
> the alias existence get in the way??
>
> Just a few questions to start, I'm a real novice with SQL but I sure
> like the MS query interface to get external data under excel. Very,
> very powerful.

CRPence wrote:
< snip >
> There are few
> reasons to use an ALIAS except for directing to a specific member of a
> DDS created file.?
< snip >
>
> Regards, Chuck

One possibility is referencing the file (even a single member DDS
created file) using a longer name. The alias name limit is 128. A view
would be an alternative, but if no other view function (eg row/column
selection) is needed, alias redirection may be (?) less overhead.
http://publib.boulder.ibm.com/infoce...mstlimtabs.htm

--
Karl Hanson

I guess I meant to say "few reasons where there exists a _need_ to
use an ALIAS..." :-)
As implemented, IMO the redirection in both ALIAS and [especially]
Long Names add as much or more overhead than a short name VIEW; except
storage where the VIEW will almost always take more storage. The worst
part is that the overhead cost of long names is variable [unpredictable]
rather than mostly fixed.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

Karl Hanson wrote:
> CRPence wrote:
> < snip >
>> There are few reasons to use an ALIAS except for directing to a
>> specific member of a DDS created file.?
> < snip >
>>
>> Regards, Chuck
>
> One possibility is referencing the file (even a single member DDS
> created file) using a longer name. The alias name limit is 128. A view
> would be an alternative, but if no other view function (eg row/column
> selection) is needed, alias redirection may be (?) less overhead.
> http://publib.boulder.ibm.com/infoce...mstlimtabs.htm

Chuck,

One more question for you. It obviously isn't the same, but if I use
the iseries access plug-in in excel for data transfer, which does
allow for some query-like selection, the files show up in the list.
I'm just curious what odbc driver the iseries access excel plug-in
uses as opposed to the ms query one.

Maybe that's going beyond the scope of this discussion but it just
seems weird that if I use the excel plug-in provided by IBM it can see
the file and allow some selection, linking, selection based on field
values, etc...and sees the file that the other way doesn't.
Unfortunately I feel the plug-in isn't graphical enough, kind of too
restrictive in many cases, etc. But someone naive like me would think
if it sees the file with the plug-in, it should see it the other way
too...

thanks,
ga

CRPence wrote:

> A DDS logical file over multiple members is implemented as having
>multiple formats; a MFLF Multi-Format Logical File. This is considered
>non-relational, and thus DBXREL='N' in QADBXREF which prevents the file
>from being visible in the SQL catalog VIEWs. That although similar to a
>UNION, it is a different animal in its implementation. The SQL is able
>to access only the first format and first member of any file --
>excepting override or ALIAS to a specific member, but a member in a MFLF
>may not have format *FIRST.
> The ALIAS can be used to combine members in a run-time SELECT just as
>if each were a separate TABLE. An ALIAS can not be referenced in a
>VIEW. The ALIAS is persistent; implemented via the DDM file, just
>create them and leave them as effective symbolic links. There are few
>reasons to use an ALIAS except for directing to a specific member of a
>DDS created file.? As just a link for a name, what happens to the file
>to which the ALIAS points is moot until run-time; if the named
>file.member exists [as named as reference in the creation of the ALIAS],
>it will be used. The file.member does not have any knowledge that an
>ALIAS references it, so any action can be performed [e.g. remove member,
>rename file or member] that might break the link.
>
>Regards, Chuck

ga
nospam@nospam.fmctc.com

I believe the MS Excel plugin uses the System i ODBC driver added by
installing Client Access, and that uses a specific server feature that
is enablement for "Data Transfer". I believe there are two server
features for ODBC, but I do not recall how they are named, nor if or how
a choice can be made [I think so] in configuration of the DSN/driver
[¿Client Access ODBC Administration?]. So although the file may appear
in the list, a -7003 [WRKMSGD SQL7003 QSQLMSG] error will occur in at
least some cases where a non-relational logical file is selected and
used in a transfer request; i.e. although the file may appear in the
list, it may not be usable by the SQL request.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

ga wrote:
> Chuck,
>
> One more question for you. It obviously isn't the same, but if I use
> the iseries access plug-in in excel for data transfer, which does
> allow for some query-like selection, the files show up in the list.
> I'm just curious what odbc driver the iseries access excel plug-in
> uses as opposed to the ms query one.
>
> Maybe that's going beyond the scope of this discussion but it just
> seems weird that if I use the excel plug-in provided by IBM it can see
> the file and allow some selection, linking, selection based on field
> values, etc...and sees the file that the other way doesn't.
> Unfortunately I feel the plug-in isn't graphical enough, kind of too
> restrictive in many cases, etc. But someone naive like me would think
> if it sees the file with the plug-in, it should see it the other way
> too...