Link to home
Start Free TrialLog in
Avatar of jim25
jim25

asked on

Finding files to view using Iseries Navigator

Hi all

This question follows on from

https://www.experts-exchange.com/questions/21782107/Transfer-Data-from-AS-400-to-Excel.html

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?

Cheers!
Avatar of Barry Harper
Barry Harper
Flag of Canada image

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

Barry
Avatar of jim25
jim25

ASKER

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

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:


SELECT SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA, TABLE_TEXT
   FROM QSYS2.SYSTABLES;

and

SELECT SYSTEM_COLUMN_NAME, SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA, COLUMN_TEXT
   FROM QSYS2.SYSCOLUMNS;

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.

Tom
Avatar of jim25

ASKER

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

How do you do a query search?
ASKER CERTIFIED SOLUTION
Avatar of Member_2_276102
Member_2_276102

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jim25

ASKER

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?

Thanks

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

Tom