List of source members in a file with SQL

Question

Is it possible to generate a list of all source members within an iSeries source file using SQL?

Might be similar to getting table definitions from SYSTABLES and SYSCOLUMNS, but I'm unable to find anything so far.

Answer 1

More tables and views have been added to the system catalog since the other answers were presented. Now, you can get the list of members (a.k.a. "partitions" in SQL parlance) for a given file (a.k.a. table) like this:

SELECT TABLE_PARTITION FROM SYSPARTITIONSTAT
WHERE TABLE_NAME = myfile AND TABLE_SCHEMA = mylib

You can also get other information from SYSPARTITIONSTAT such as the number of rows in each member, and timestamps for the last change, save, restore, or use.

Answer 2

Similar to @john-y's answer, you can also get a list of source physical file members like this:

SELECT SYSTEM_TABLE_MEMBER, SOURCE_TYPE FROM QSYS2/SYSPARTITIONSTAT WHERE
SYSTEM_TABLE_SCHEMA = 'MYLIB' AND SYSTEM_TABLE_NAME = 'QRPGLESRC'