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!
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!
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
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
ASKER
Hi Tom under systables there is only one record and that is of zero size.
How do you do a query search?
How do you do a query search?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
If the file is in a traditional library, look under Databases (Databases/Database1/Schem
Barry