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

Rob, that does look like a function call, as you surmised - and at the later releases, there's much more of that, it seems.

Yeah, it'd be tough to get much out of QADBXREF. But the QADB*FLD tables are probably the source for this view, as well as the other 2 I mentioned - the latter, along with QADBXREF, might be enough to build what the OP wants.

Of course, I keep mentioning something that's already there - if you have Perf Tools licensed product. ANZDBF and ANZDBFKEY.

On 2/9/2012 12:07 PM, rob@xxxxxxxxx wrote:

Vern,
The sql to build SYSPARTITIONINDEXES is several pages long. The only
table I could see in it was QADBXREF. That's been there since V3R1. It
also shows this:
Files accessed by logical file PFILE
File Library LF Format
QADBXREF QSYS SYSPA00001

Now, if you can figure out how to get the key names out of just QADBXREF
I'd be impressed. I started to wonder if IBM is using UDT's, UDF's or
whatever to get the key names. Wait, here's more. But I've got to get
some "real work" done. After all, I am running 7.1 and I don't need to
know this info.
... FROM QSYS.QADBXREF A, LATERAL (SELECT * FROM TABLE
(QSYS2.INDEX_PARTITION_STATISTICS
(A.DBXLIB, A.DBXFIL)) AS X ) AS B ...

So there's the second index or view. And I suppose it cascades from
there...


Rob Berendt