[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

Finding files to view using Iseries Navigator

Hi all

This question follows on from

http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_21782107.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!
0
jim25
Asked:
jim25
  • 3
  • 3
1 Solution
 
_b_hCommented:
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
0
 
jim25Author Commented:
I am not even sure I what the file would be called :)
0
 
tliottaCommented:
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
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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

How do you do a query search?
0
 
tliottaCommented:
jim25:

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?

Tom
0
 
jim25Author Commented:
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
0
 
tliottaCommented:
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now