AS/400 - Getting Around Basics

Hi and thanks,

I just want to be able to get around in the AS/400 developments


How to list all the fields
I have done a Select * From Table
But that does not show you the field names

Say I want to extend the SQL statement:
Select *
From Table
On fields
Where fields

how do you find the Table(s) to know what to put into the From Table???
How do you find the Field names to do the On or Where statement???
If you do manage to find a Table then how to find which library it is located in???

Please help and thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
The easiest way to determine the tables and columns is to use the native GUI ("System i Navigator") and drill down in the Database tab.

Outside of that, if you're stuck using the text-based, "green-screen" interface. You'd go into the SQL environment using the StrSQL command, and the following queries should give you info on the tables and columns.

select table_schema,
  from SysTables

select table_schema
  from SysColumns

Open in new window

Can you clarify a little? Are you asking how to learn SQL? Are you asking the table and view names of the DB2 database catalog?

What platforms are you comfortable with? If we know some of your background/experience, we can probably give answers that make more sense. It's possible to interact with a reasonably current system in the AS/400 series as if it was a Unix or even somewhat a Windows system, as well as through its unique native methods. Some methods might make more sense to you if we know more.

What programming languages are you comfortable with? What tools have been provided for you? Are you restricted to access through a telnet/terminal interface? Or are you allowed/encouraged to use various GUI interfaces? (E.g., iSeries Access, iSeries Navigator, various web interfaces, WDSC or RDi IDEs, Data Studio, etc.)

A few comments from you on those areas will help us a lot.

Amour22015Author Commented:
Thank you for all your help.

What I was looking for was:

On AS/400 i5

StrSQL statement

Select *
From TableName

On Specify Select Statement
put cursor on: Select Fields

This gives you the Field(s) that are in the Table/File

This is the kind of things I am looking for, if you have anymore???

Yes, you can type a statement into the STRSQL entry line and press <Enter> to execute it immediately, or you can type as much of a statement as you're certain about and press <F4> to get prompted help.

Once a statement is prompted, you can position the cursor to one of the areas and press <F4> for detailed prompts.

The prompting can depend on how much of the statement you typed in. If you only typed SELECT before asking for prompted help, you can't ask for help on the SELECT fields until you get a table chosen. And it'll want you to choose a schema (a library) first, so it knows where it can find any tables to list.

As it lists different elements, you can select one or more things to put into the statement you're building.

It's a fairly basic interface. It's reasonably quick, though, and only takes a couple minutes for someone familiar with standard SQL to learn.

At any point, you can press <F12> to cancel the current panel and return to the previous one. Or press <F3> to cancel the function and return to whatever the current return point is.

(Upon exit from STRSQL, you'll have the option to save the history in a source file member. I suggest learning how that works and making use of it from time to time. STRSQL will remember your history and bring it back whenever you go into it again later, but there are a couple quirks that may be confusing the first time you run into them. It'll take some explaining before understanding why it works as it does. You are likely to have multiple histories at some point, and you'll want to know where they are, why they happened, etc. Also, having statements saved in a source file can give benefits for some work you'll do in other ways.)

Those F-key rules apply to almost everything you're going to see when you're using a terminal emulator (telnet) on this system. If you get into no other habits, knowing those can be enough to get you through almost any difficulties you get into.

<F3> exits the current function and returns to its exit point. That will either drop you all the way out or put you back to some major point of choice within the function.

<F12> will cancel the current panel.

<F4> will provide any prompting that's defined for whatever area of the panel that the cursor is currently in. Prompting is provided only where it makes sense, but there's a lot of it all over.

And <F1> provides 'Help' for whatever area the cursor is in. There is a huge amount of help available, even for functions deep inside the 'Help' function.

If you remember those four F-keys, you can learn pretty much anything you'll ever need about any panel you happen to be in.

The telnet screens are generally quick and fairly easy (once you get those F-keys as a habit). For developers using features like SQL, they can be limiting.

As any kind of developer or even administrator, the first thing you'll want on your PC is iSeries Navigator (iNav or System i Navigator if it's newer). That provides a GUI interface into most system components. It's part of the iSeries Access for Windows product (iNav is a free part; a couple Iseries Access parts require user license).

The iNav feature has a number of optionally installed components. One component is 'Database' (different from any 'File Transfer' components). The iNav Database component provides a number features beyond the basic telnet interface.

The full iNav product deserves some exploration. You should ensure you have it installed and configured and begin to explore it. You can review lists of schemas, lists of tables, views, procedures, etc., create schemas, tables, views, functions, etc., and run or test any of them (that you have authority for).

Within iNav Database, Visual Explain can help with understanding complex queries. Index Advisor can help with creating appropriate indexes. And on and on... I've used it for a couple decades and still have stuff to learn. Other iNav components can be installed, but I don't know what you need.

First thing to do upon installation of iSeries Access is to locate, download and install the most current fix pack. You can find it at IBM i Access for Windows Support. Many additional links are available from there if you want to explore.

Not sure where to go after that. I don't doubt you'll have more questions. Any questions directly related to this thread are probably reasonable to ask back here. There can be a lot of clarification possible.

New questions of a different nature should start in a new thread. Be sure to summarize the question topic in the subject to catch the interest of an appropriate expert. There are a couple here who will give serious top-notch help.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
Thank You all...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.