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
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
  • 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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