How to extract the AS400 tablename from values seen on screen

Daniel Marin
Daniel Marin used Ask the Experts™

   When we see values on greenscreen from a AS400 programs, there are needs to download massive data.

   Sometimes we see a number in screen but to avoid going from screen to screen to check multiple values, we know all those values are in a table

   Do you know how to extract the "table name" to download massive data in a SQL query from a number you see in the screen?

   Picture attached to explain better.

Thank in advance
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AS400 green screen does not gather information from one source or "table", if you will.

The information may be coming from multiple sources, example if you are looking at an invoice on the green screen, depending on how your system is configured (yes, not all systems are configured the same), the following tables may be involved: the Customer Master, the Customer Master Location (for customers with multiple locations), the Product Master, Inventory, Warehouse location, Accounts Receivables, and the list goes on and on.

What you need is an AS400 Programmer or someone who already may be working for you that knows your system and can see "behind the scene" what is happening. You can also do the following to see what files are used by a program:

This is not as simple a question as you think, but the answer may be simpler than you believe, IF the right individuals are looking in the right places.

That's all I can offer.
As you can see in the attached picture, this is a query for a simple label program. There are multiple tables/files involved: The Sales Order file, the Sales Order details, the Product file, the Product detail and the Customer File, which is actually a subquery based on its own set of files.
VP Technology / Senior Consultant
Applications vary in complexity.  Some.may use a large collection of tables.  Some may use a single table.  Downloading data is a common request, so when someone asks me to do it, here is the basic process I follow:

When a green screen running, you can use SysReq Option 3 to view the current job.  SysReq on my emulator is mapped to Shift-Escape.

Option 10 displays the call stack (programs), and option 14 displays open files (tables).   If you have source code for.your programs, you can review the programs on the call stack to see the files they open or SQL queries they run.

There are other tricks, too.  For example, in applications that use SQL IO, a DBA can run an SQL Performance Monitor to capture all of the SQL statements executed in a given object or by a particular user.

These are not techniques and tools intended for end user, so it is likely that if you aren't a developer or DBA, you are going to need some help.
Daniel MarinA sort of Super User


Thank you so much... stay in touch for a representative reward

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial