Re: Command to list all logicals and their key fields for a physical file?

Ok, here's your answer:

SELECT * FROM SYSPARTITIONINDEXES WHERE
SYSTEM_TABLE_SCHEMA='ERPLXF' AND SYSTEM_TABLE_NAME='IIM'

I know, it blows your mind to have a file name that long. Just like
columns can be defined with a long name and a short name, so can tables,
views and indexes. The short name is SYSPINDEX. It's in QSYS2. I
figured out the short name from reading SYSVIEWS.

Some sample data (from a modified sql statement)
LIBRARY LONG MEMBER
NAME FILE AKA
NAME PARTITION INDEX_NAME INDEX_TYPE COLUMN_NAMES
ERPLXF IIM IIM IIMI01 INDEX IID, IPROD
KFPJ01 LOGICAL IBUYC, IPROD
IIML20 LOGICAL IMFRMC

If you just HAVE to read that with RPG without using SQL then there's a
number of things you can do:
- Determine what makes up that view by using SQL to read SYSVIEWS.
- Try to open QSYS2/SYSPINDEX.
- Do a DSPFD QSYS2/SYSPINDEX and scan for "SQL view create statement".
Have a stiff drink ready.

You could always whip up your own sql statement to use this and
SYSCOLUMNS, etc.

Many of these names (like SYSCOLUMNS) should be familiar to anyone who has
used databases on other platforms such as MS SQL Server and others.

Rob Berendt