MS Query/Excel with AS/400 - IBM AS400

This is a discussion on MS Query/Excel with AS/400 - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: MS Query/Excel with AS/400

  1. MS Query/Excel with AS/400

    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.

    thanks,
    ga
    ga
    nospam@nospam.fmctc.com

  2. Re: MS Query/Excel with AS/400

    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.


  3. Re: MS Query/Excel with AS/400

    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

  4. Re: MS Query/Excel with AS/400

    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


  5. Re: MS Query/Excel with AS/400

    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

  6. Re: MS Query/Excel with AS/400

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


+ Reply to Thread