Guru: Where’s The Table?

June 8, 2026 Ted Holt

It began with an irritation. I considered it a simple request. “Which file did my SQL query read?” I was using the Run SQL Scripts tool to modify an SQL query with unqualified table names. And yet I knew of no ready way to determine the schema (library) name of any of the files. How was I to know which tables I had just queried?

Let me back up a bit. My SQL client of choice is the Run SQL Scripts tool, which is part of IBM’s Access Client Solutions (ACS). It is not unusual for me to copy and paste an SQL SELECT statement from an RPG program or a view into Run SQL Scripts, modify the statement as needed, and copy and paste the modified statement back into the source member from which it came.

A few years ago, I was working on a query and not getting the results I expected. The thought occurred to me that perhaps one or more of the tables was in a library other than the one I thought I was accessing. But how to know? I looked through the menus, but couldn’t find the information at hand.

There are ways. First, there’s Visual Explain.

The answer’s there, but that’s an awful lot to go through, and it could take a while.

There are the F4 key and Ctl+Space. They’ll show me a list, but they won’t answer my question.

What I wanted was to see a message in the messages panel, and that was easily thrown together with a quick CL program and a command interface. I called it FL, for File Location.

Here’s the command interface:

/* Command: FL - File location                         */
/* Program: FLC                                        */
/* Show what library a file is in from Run SQL Scripts */
/* Example from Run SQL Scripts                        */
/*    Input:      cl:FL customers;                     */
/*    Output:     CPF9897: THH1/CUSTOMERS *FILE *PF    */
/*                                                     */
/* To create:                                          */
/*    CRTCMD CMD(<lib>/FL)                             */
/*           PGM(*LIBL/FLC)                            */
/*           SRCFILE(<lib>/QCMDSRC)                    */
/*           SRCMBR(FL)                                */
                                                         
CMD   PROMPT('File location')                            
                                                         
PARM  OBJ  TYPE(Q1) min(1) PROMPT('Object')              
                                                         
Q1:  QUAL  TYPE(*NAME) MIN(1) EXPR(*YES)                 
     QUAL  TYPE(*NAME) DFT(*LIBL) SPCVAL((*LIBL)) +      
              EXPR(*YES) PROMPT('Library')

Here’s the CL source:

/* Send a message to tell what library a file is in. */
/* CPP for command FL */
pgm (&iQualObj)

   dcl  &iQualObj     *char     20

   dcl  &ObjName      *char     10
   dcl  &ObjLib       *char     10
   dcl  &Msg          *char    132
   dcl  &MsgType      *char     10     '*INFO'
   dcl  &FileAtr      *char      3
   dcl  &Text         *char     50

   monmsg cpf0000    exec(goto Abend)

   chgvar   &ObjName      %sst(&iQualObj   1  10)
   chgvar   &ObjLib       %sst(&iQualObj  11  10)

   if (&ObjLib *eq ' ' *or %sst(&ObjLib 1 1) *eq '*') do
      rtvobjd  &ObjLib/&ObjName ObjType(*file) +
                  RtnLib(&ObjLib) Text(&Text)
   enddo

   RtvMbrD &ObjLib/&ObjName FileAtr(&FileAtr)
   if (&FileAtr *ne ' ') do
      ChgVar  &Msg   &FileAtr
   enddo

   chgvar    &Msg   (%trimr(&ObjLib)   *cat '/' *cat +
                     %trimr(&ObjName)  *cat ' ' *cat +
                            *FILE      *cat ' ' *cat +
                     %trimr(&Msg)      *cat ' ' *cat +
                            &Text)
   SndPgmMsg MsgID(CPF9897) MsgF(QCPFMSG) +
                MsgDta(&MSG) MsgType(&MsgType)

return

Abend:
   call QMHRSNEM parm(' ' x'00000000')
return

endpgm

It’s far from elegant code, but I threw it together in just a few minutes and it works fine. I sent the message as an informational message – MSGTYPE(*INFO) – but a completion message works just as well.

A good while (at least a year) after I had written this little tool, I happened to stumble on the answer to my question. I happened to notice one day that the query result grid shows the schema used for each column. This is true whether or not you open the result set in a new window. Select View, Details.

I had reinvented the wheel yet again, but if a search engine won’t handily answer your question, then taking a few minutes to throw together a tool may be the smart thing to do. You may find out later, as I did, that the facility to answer your question was there all along. At that point, you can throw away the tool you wrote or keep it, as you see fit.

I suppose that IBM will eventually release an AI-enabled Run SQL Scripts. We’ll be able to tell it “list the customers who . . .” instead of “select * from customers where . . .”. Run SQL Scripts will build a SELECT statement from our English (or whatever language) query and execute that statement. Voilà!

We’ll also be able to ask it questions, such as “What schema is the customer’s table in?” Until then, I’ll keep writing and sharing little tools, or at least try to give you something to think about.

Ted Holt is the original, the one and only, chief of the Four Hundred Gurus. We are glad he is back with us writing technical material that helps IBM i programmers. He is a self-employed, independent programmer living near Tupelo, Mississippi, who is old enough to retire but is not ready to do so. He still enjoys programming and is available to help others as needed. He welcomes your comments, questions, and suggestions.

2 thoughts on “Guru: Where’s The Table?”

Marco Riva says:

June 8, 2026 at 7:39 am

Alternatively, you can also use this query

select TABLE_SCHEMA “Libreria”
from QSYS2.SYSFILES
where FILE_TYPE = ‘DATA’
and TABLE_SCHEMA in (select SYSTEM_SCHEMA_NAME from QSYS2.LIBRARY_LIST_INFO)
and TABLE_NAME = :Tabella
limit 1;

Perhaps encapsulated in a view or a SQL scalar function

Alex Krashevsky says:

June 10, 2026 at 11:37 am

Hi Ted,

Here is a way to instantly identify the schema the table from the SQL statement resides in I came up with. It takes advantage of the fact that ACS will keep the file open in the job (QZDASOINIT) after the query is executed.

SELECT * FROM ;

SELECT
LIBRARY_NAME
FROM TABLE(QSYS2.OPEN_FILES(
‘*’
))
WHERE FILE_NAME
=
”
;

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search