Wondering - MS Query/excel and Iseries & SQL??? - IBM AS400

This is a discussion on Wondering - MS Query/excel and Iseries & SQL??? - IBM AS400 ; I have an odbc connection to our as/400 defined. In excel, if I do "data/import external data" (and then uncheck the use query wizard box), it will then bring up a list of all files in my library list. One ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: Wondering - MS Query/excel and Iseries & SQL???

  1. Wondering - MS Query/excel and Iseries & SQL???

    I have an odbc connection to our as/400 defined. In excel, if I do
    "data/import external data" (and then uncheck the use query wizard
    box), it will then bring up a list of all files in my library list.
    One of the options in the box that comes up with the list of files in
    the ms query is what to include - tables, views, etc. I have both
    checked.

    Problem; most of my logical files appear, but several also do not. I
    have a logical file that is built over multiple members in two files
    (that both exist in my library list). The problem is, these logical
    files do not appear. I don't know if it has to do with the fact that
    the members say "All_active" or whether the file is a logical built
    across two physical files in different libraries. Does anybody know
    why this would be? Could it perhaps be that there are too many
    records???

    The file description is shown at the end of this message; as I said,
    this logical file does not show up in the microsoft query list even
    though other logicals do - I am thinking it is either the members
    parameter(s) or the fact that it's two files from two different
    libraries, even though both are in my library list, or that there are
    so many records....

    If anyone can tell me why this is happening and if there is a
    workaround I would appreciate it. I could maybe create "aliases" for
    all the members and do a UNION (I'm not too proficient with SQL), but
    I don't know if unions work across multiple libraries?

    If anyone has encountered something similar and has a solution I would
    be most grateful, or for any other suggestions. I have marked the
    things I am wondering if could be the issues as to why the file
    doesn't show up...as I said, other logical files do.

    thanks,
    ga

    Here is the file dds:

    5722SS1 V5R4M0 060210 Display File Description
    8/02/07 12:45:16 Page 1
    File . . . . . . . . . . . : SSALMSTZ
    Library . . . . . . . . . : CONTROLFIL
    Type of information . . . . : *ALL
    File attributes . . . . . . : *ALL
    System . . . . . . . . . . : *LCL
    Processor . . . . . . . . . : IBM AS/400 Display File Description
    Processor
    File . : SSALMSTZ Library . : CONTROLFIL Type of file . :
    Logical *DATA Auxiliary Storage Pool ID . : 00001
    Data Base File Attributes
    Externally described file . . . . . . . . . : Yes
    File level identifier . . . . . . . . . . . :
    1050424074438
    Creation date . . . . . . . . . . . . . . . : 04/24/05
    Text 'description' . . . . . . . . . . . . : TEXT Sales
    Ticket Master LF (All Data) TM
    Distributed file . . . . . . . . . . . . . : No
    Partitioned SQL Table . . . . . . . . . . . : No
    DBCS capable . . . . . . . . . . . . . . . : No
    Maximum members . . . . . . . . . . . . . . : MAXMBRS *NOMAX
    Number of triggers . . . . . . . . . . . . : 0
    Number of members . . . . . . . . . . . . . : 2
    Access path maintenance . . . . . . . . . . : MAINT *IMMED
    Access path recovery . . . . . . . . . . . : RECOVER *NO
    Force keyed access path . . . . . . . . . . : FRCACCPTH *NO
    Preferred storage unit . . . . . . . . . . : UNIT *ANY
    Record format selector program . . . . . . : FMTSLR *NONE
    Records to force a write . . . . . . . . . : FRCRATIO *NONE
    Maximum file wait time . . . . . . . . . . : WAITFILE *IMMED
    Maximum record wait time . . . . . . . . . : WAITRCD 60
    With check option . . . . . . . . . . . . . : NONE
    Allow read operation . . . . . . . . . . . : Yes
    Allow write operation . . . . . . . . . . . : Yes
    Allow update operation . . . . . . . . . . : ALWUPD *YES
    Allow delete operation . . . . . . . . . . : ALWDLT *YES
    Record format level check . . . . . . . . . : LVLCHK *YES
    Access path . . . . . . . . . . . . . . . . : Keyed
    Access path size . . . . . . . . . . . . . : ACCPTHSIZ *MAX1TB
    Access path logical page size . . . . . . . : PAGESIZE *KEYLEN
    Maximum key length . . . . . . . . . . . . : 4
    Maximum record length . . . . . . . . . . . : 179
    5722SS1 V5R4M0 060210 Display File Description
    8/02/07 12:45:16 Page 2
    File . : SSALMSTZ Library . : CONTROLFIL Type of file . :
    Logical *DATA Auxiliary Storage Pool ID . : 00001
    Access Path Description
    Access path maintenance . . . . . . . . . . : MAINT *IMMED
    Unique key values required . . . . . . . . : UNIQUE No
    Key order . . . . . . . . . . . . . . . . . : Not
    specified
    Select/omit specified . . . . . . . . . . . : No
    Access path journaled . . . . . . . . . . . : No
    Access path . . . . . . . . . . . . . . . . : Keyed
    Number of key fields . . . . . . . . . . . : 1
    Record format . . . . . . . . . . . . . . . : SSALMST1
    Key field . . . . . . . . . . . . . . . . : TMTICK
    Sequence . . . . . . . . . . . . . . . : Ascending
    Sign specified . . . . . . . . . . . . : SIGNED
    Zone/digit specified . . . . . . . . . : *NONE
    Alternative collating sequence . . . . : No
    Record format . . . . . . . . . . . . . . . : SSALMST1
    Key field . . . . . . . . . . . . . . . . : TMTICK
    Sequence . . . . . . . . . . . . . . . : Ascending
    Sign specified . . . . . . . . . . . . : SIGNED
    Zone/digit specified . . . . . . . . . : *NONE
    Alternative collating sequence . . . . : No
    Files accessed by logical file PFILE
    File Library LF Format
    SSALMST CONTROLFIL SSALMST1 ????????
    P.SSMST CONTROLHIS SSALMST1 ????????
    Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ *HEX
    Language identifier . . . . . . . . . . . . : LANGID ENU
    5722SS1 V5R4M0 060210 Display File Description
    8/02/07 12:45:16 Page 3
    File . : SSALMSTZ Library . : CONTROLFIL Type of file . :
    Logical *DATA Auxiliary Storage Pool ID . : 00001
    Member Description
    Member . . . . . . . . . . . . . . . . . . : MBR ALL_ACTIVE
    Member level identifier . . . . . . . . . :
    1060831203742
    Member creation date . . . . . . . . . . : 08/31/06
    Text 'description' . . . . . . . . . . . : TEXT
    Expiration date for member . . . . . . . : EXPDATE *NONE
    Access path maintenance . . . . . . . . . : MAINT *IMMED
    Access path recovery . . . . . . . . . . : RECOVER *NO
    Preferred storage unit . . . . . . . . . : UNIT *ANY
    Record format selector program . . . . . : FMTSLR *NONE
    Records to force a write . . . . . . . . : FRCRATIO *NONE
    Share open data path . . . . . . . . . . : SHARE *NO
    Access Path Activity Statistics . . . . . :
    Access path logical reads . . . . . . . :
    381
    Access path physical reads . . . . . . :
    Index size . . . . . . . . . . . . . . :
    34631680
    Access path valid . . . . . . . . . . . : Yes
    Implicit access path sharing . . . . . : No
    Access path journaled . . . . . . . . . : No
    Last change date/time . . . . . . . . . . : 09/18/06
    07:10:18
    Last save date/time . . . . . . . . . . . : 08/02/07
    01:48:55
    Last restore date/time . . . . . . . . . :
    Last used date . . . . . . . . . . . . . : 08/02/07
    Days used count . . . . . . . . . . . . . : 322
    Reset date . . . . . . . . . . . . . . :
    Number of data members . . . . . . . . . : 9
    Based on file . . . . . . . . . . . . . . : SSALMST
    Library . . . . . . . . . . . . . . . . : CONTROLFIL
    Member . . . . . . . . . . . . . . . . : SSALMST
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 91
    Based on file . . . . . . . . . . . . . . : SSALMST
    Library . . . . . . . . . . . . . . . . : CONTROLFIL
    Member . . . . . . . . . . . . . . . . : THIS_YTD
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 149110
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : YEAR_00
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 146911
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : YEAR_01
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 169997
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : YEAR_02
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 164736
    5722SS1 V5R4M0 060210 Display File Description
    8/02/07 12:45:16 Page 4
    File . : SSALMSTZ Library . : CONTROLFIL Type of file . :
    Logical *DATA Auxiliary Storage Pool ID . : 00001
    Member Description
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : YEAR_03
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 159938
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : YEAR_04
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 154359
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : YEAR_05
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 154487
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : YEAR_06
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 160285
    Member . . . . . . . . . . . . . . . . . . : MBR ALL_DELETE
    Member level identifier . . . . . . . . . :
    1060831203804
    Member creation date . . . . . . . . . . : 08/31/06
    Text 'description' . . . . . . . . . . . : TEXT
    Expiration date for member . . . . . . . : EXPDATE *NONE
    Access path maintenance . . . . . . . . . : MAINT *IMMED
    Access path recovery . . . . . . . . . . : RECOVER *NO
    Preferred storage unit . . . . . . . . . : UNIT *ANY
    Record format selector program . . . . . : FMTSLR *NONE
    Records to force a write . . . . . . . . : FRCRATIO *NONE
    Share open data path . . . . . . . . . . : SHARE *NO
    Access Path Activity Statistics . . . . . :
    Access path logical reads . . . . . . . :
    Access path physical reads . . . . . . :
    Index size . . . . . . . . . . . . . . :
    266240
    Access path valid . . . . . . . . . . . : Yes
    Implicit access path sharing . . . . . : No
    Access path journaled . . . . . . . . . : No
    Last change date/time . . . . . . . . . . : 09/18/06
    07:10:31
    Last save date/time . . . . . . . . . . . : 08/02/07
    01:48:55
    Last restore date/time . . . . . . . . . :
    Last used date . . . . . . . . . . . . . : 08/02/07
    Days used count . . . . . . . . . . . . . : 317
    Reset date . . . . . . . . . . . . . . :
    Number of data members . . . . . . . . . : 9
    Based on file . . . . . . . . . . . . . . : SSALMST
    Library . . . . . . . . . . . . . . . . : CONTROLFIL
    Member . . . . . . . . . . . . . . . . : DLT_TODAY
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 1
    5722SS1 V5R4M0 060210 Display File Description
    8/02/07 12:45:16 Page 5
    File . : SSALMSTZ Library . : CONTROLFIL Type of file . :
    Logical *DATA Auxiliary Storage Pool ID . : 00001
    Member Description
    Based on file . . . . . . . . . . . . . . : SSALMST
    Library . . . . . . . . . . . . . . . . : CONTROLFIL
    Member . . . . . . . . . . . . . . . . : DLT_YTD
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 871
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : DLT_00
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 938
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : DLT_01
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 886
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : DLT_02
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 604
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : DLT_03
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 769
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : DLT_04
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 887
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : DLT_05
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 827
    Based on file . . . . . . . . . . . . . . : P.SSMST
    Library . . . . . . . . . . . . . . . . : CONTROLHIS
    Member . . . . . . . . . . . . . . . . : DLT_06
    Logical file format . . . . . . . . . . : SSALMST1
    Number of index entries . . . . . . . . : 865
    5722SS1 V5R4M0 060210 Display File Description
    8/02/07 12:45:16 Page 6
    File . : SSALMSTZ Library . : CONTROLFIL Type of file . :
    Logical *DATA Auxiliary Storage Pool ID . : 00001
    Record Format List
    Record Format Level
    Format Fields Length Identifier
    SSALMST1 45 179 45EBD1E0D15D7
    Text . . . : Sales Ticket Master
    SSALMST1 45 179 45EBD1E0D15D7
    Text . . . : Sales Ticket Master
    Total number of formats . . . . . . . . . : 2
    Total number of fields . . . . . . . . . . : 90
    Total record length . . . . . . . . . . . : 358
    5722SS1 V5R4M0 060210 Display File Description
    8/02/07 12:45:16 Page 7
    File . : SSALMSTZ Library . : CONTROLFIL Type of file . :
    Logical *DATA Auxiliary Storage Pool ID . : 00001
    Member List
    Source Creation Last Change
    Member Size Type Date Date Time
    ALL_ACTIVE 34631680 08/31/06 09/18/06 07:10:18
    Text:
    ALL_DELETE 266240 08/31/06 09/18/06 07:10:31
    Text:
    Total number of members . . . . . . . . . : 2
    Total number of members not available . . : 0
    Total of member sizes . . . . . . . . . . : 34897920

    ga
    nospam@nospam.fmctc.com

  2. Re: Wondering - MS Query/excel and Iseries & SQL???

    As noted in the prior response, the logical file is not visible in
    the list because it is not considered /relational/ and thus the SQL
    catalog will not show the file as being available to query via SQL.
    Unless the UNION can be represented by only the first member of each
    of the physical files referenced by the multi-format logical file, the
    UNION can not be part of a VIEW. This is true irrespective of in which
    libraries the underlying [based-on] files exist. If each of the members
    of the files are converted into a TABLE or all members of each file are
    converted into a TABLE, then a UNION VIEW can be created [single member
    physical non-SQL would suffice, but if converting, take the next step].
    If such a conversion is not an option, then a query referencing each
    ALIAS that points to the specific members of the physical files must be
    performed [ignoring overrides]; i.e. the full query UNION SELECT must be
    specified as the statement for the data access, rather than the simpler
    SELECT * from a UNION VIEW.

    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:
    > I have an odbc connection to our as/400 defined. In excel, if I do
    > "data/import external data" (and then uncheck the use query wizard
    > box), it will then bring up a list of all files in my library list.
    > One of the options in the box that comes up with the list of files in
    > the ms query is what to include - tables, views, etc. I have both
    > checked.
    >
    > Problem; most of my logical files appear, but several also do not. I
    > have a logical file that is built over multiple members in two files
    > (that both exist in my library list). The problem is, these logical
    > files do not appear. I don't know if it has to do with the fact that
    > the members say "All_active" or whether the file is a logical built
    > across two physical files in different libraries. Does anybody know
    > why this would be? Could it perhaps be that there are too many
    > records???
    >
    > The file description is shown at the end of this message; as I said,
    > this logical file does not show up in the microsoft query list even
    > though other logicals do - I am thinking it is either the members
    > parameter(s) or the fact that it's two files from two different
    > libraries, even though both are in my library list, or that there are
    > so many records....
    >
    > If anyone can tell me why this is happening and if there is a
    > workaround I would appreciate it. I could maybe create "aliases" for
    > all the members and do a UNION (I'm not too proficient with SQL), but
    > I don't know if unions work across multiple libraries?
    >
    > If anyone has encountered something similar and has a solution I would
    > be most grateful, or for any other suggestions. I have marked the
    > things I am wondering if could be the issues as to why the file
    > doesn't show up...as I said, other logical files do.
    >
    > <>


+ Reply to Thread