Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

AS/400 - Getting Around Basics

Posted on 2014-04-04
Last Modified: 2014-04-08
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
Question by:Amour22015
  • 2
  • 2
LVL 18

Assisted Solution

daveslash earned 100 total points
ID: 39979109
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

LVL 27

Expert Comment

ID: 39979623
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.


Author Comment

ID: 39983983
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???

LVL 27

Accepted Solution

tliotta earned 160 total points
ID: 39984879
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.


Author Closing Comment

ID: 39985765
Thank You all...

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question