Solved

AS/400 - Getting Around Basics

Posted on 2014-04-04
5
483 Views
Last Modified: 2014-04-08
Hi and thanks,

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

Like:

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
0
Comment
Question by:Amour22015
  • 2
  • 2
5 Comments
 
LVL 18

Assisted Solution

by:daveslash
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,
       table_name,
       SYSTEM_TABLE_NAME
  from SysTables

select table_schema
       table_name,
       column_name,
       DATA_TYPE,
       LENGTH
  from SysColumns

Open in new window


HTH,
DaveSlash
0
 
LVL 27

Expert Comment

by:tliotta
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.

Tom
0
 

Author Comment

by:Amour22015
ID: 39983983
Thank you for all your help.

What I was looking for was:

On AS/400 i5

StrSQL statement

Select *
From TableName
F4

On Specify Select Statement
put cursor on: Select Fields
F4

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???

Thanks
0
 
LVL 27

Accepted Solution

by:
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.

Tom
0
 

Author Closing Comment

by:Amour22015
ID: 39985765
Thank You all...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now