We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Finding files to view using Iseries Navigator

jim25 asked
Medium Priority
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?

Watch Question

Barry HarperConsultant

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



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

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.



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

How do you do a query search?

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?


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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?


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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.