How to extract the AS400 tablename from values seen on screen


   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
Daniel MarinA sort of Super UserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Gary PattersonVP Technology / Senior Consultant Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel MarinA sort of Super UserAuthor Commented:
Thank you so much... stay in touch for a representative reward
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.