Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


AS/400 - Getting Around Basics

Posted on 2014-04-04
Medium Priority
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

by:Dave Ford
Dave Ford earned 400 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 640 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

'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 …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

569 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