Finding files to view using Iseries Navigator

Posted on 2006-04-05
Last Modified: 2008-02-01
Hi all

This question follows on from

I am using an as400 system and have a data on it, but I have no idea how to find this information using the Iseries Navigator. I have found the file system section but I have no idea what file I am looking for!

Can someone give me some pointers?

Question by:jim25
    LVL 13

    Expert Comment

    If the file is in IFS, you are in the right place. Example: /mydirectory/mysubdirectory/filename

    If the file is in a traditional library, look under Databases (Databases/Database1/Schemas/Schema1/Tables) rather than File Systems. Example mylibrary/myfile


    Author Comment

    I am not even sure I what the file would be called :)
    LVL 26

    Expert Comment


    Do you have any good idea what the file name might be? If not, do you know any of the field names in the file?

    Without some kind of identification, it's going to be tricky.

    Your best bet might be to run some queries (SQL) against the database catalog. This is a collection of tables and views in library QSYS2. One table is SYSTABLES; it lists all tables that are available for SQL access. A couple queries might be:




    Those can be modified with WHERE clauses to limit results. Perhaps WHERE UPPER(TABLE_TEXT) LIKE '%WAREHOUSE%' could find a list of files relating to a warehousing app for example.


    Author Comment

    Hi Tom under systables there is only one record and that is of zero size.

    How do you do a query search?
    LVL 26

    Accepted Solution


    A query search could be done through ODBC, JDBC, DRDA, OLE DB, .NET, interactive SQL on the server, or a bunch of other ways. Any way that allows you to enter and execute an SQL SELECT statement against a server database will work as long as you can select which server and you have been granted the authority.

    If you don't have the authority, you probably won't get help from here other than to be advised to ask whoever is in charge for it. If you don't know the server name, then you might need to justify why someone from here should help access an unknown server. If you don't have experience entering queries against a database, you might want to consider far more than 20 points for the training.

    My apologies if the tone can't come across in a helpful way, but there is much confusion.

    I have to ask... What is it that you're trying to do? I.e., what business problem needs to be resolved? It's possible that this is all going in a very wrong direction and that there are much more direct ways of getting it done. This forum generally expects programming problems, but that might not be what you have.

    In the previous thread, it seemed that you might be looking to get a spooled (print) file into Excel. There is very little relationship between a printed report and a database file. Are you still working with reports? or are you looking to bypass reports by accessing the database directly from a PC?

    An example of an alternative for a printed report and Excel could possibly be setting up a printer session on your PC and directing the output to a text file. Then, the report could automatically show up on the PC as soon as it was generated on the AS/400 and all you'd need to do would be supply the text file name on the PC. Accessing it from Excel is then no different from any other txt file.

    But that's just a specific scenario example. It's not applicable for most PC-AS/400 interactions.

    So... What are we trying to solve?


    Author Comment

    Hi Tom

    Sorry for giving bad information!

    I simply want to do this: I want to be able to access the database in the as400 so I can then extract the information to excel format and do what I will with it!

    Does that make sense?


    LVL 26

    Expert Comment

    This is marked as Answer: Accepted, but I don't know if that's the true state.

    I haven't visited this question for a while nor can I tell if others have. Some fundamentals need definition.

    For example, the previous comment says "I want to be able to access the database in the as400". There may be confusion over the term 'database'.

    In the overall sense, DB2 under OS/400 allows only a single 'database'. However, most sites use files in different libraries to act as if multiple 'databases' exist.

    There is a significant difference in authorities needed to handle those two levels of 'database'. In the overall sense, authorities may need to account for access at a system rather than only at an application level. Usually, the two levels of database access serve very different purposes. E.g., a system DBA needs to do much different work than someone who only needs to work with files in a Sales or Payroll application.

    For a tool such as Excel, there is an add-in available that allows Excel to communicate directly with OS/400 tables in an application. The add-in is part of iSeries Access and might be all that is needed. iSeries Access documentation discusses how to install and use. (I think) It uses the base iSeries Access ODBC.

    But it might not help if the user is unfamiliar with the database itself and doesn't know which tables exist.

    In short, it just isn't clear what the problem is.

    I'm not sure how to ask the necessary question. The OP stated "...but I have no idea what file I am looking for!" If the system catalog doesn't show what's in the database, then either there is insufficient authority (in which case a SysAdmin needs to be contacted) or the database is in error (in which case IBM support needs to be contacted).


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now