Create Open List (QGYxxxx) API for FIle Member List

The "IBM i Service" or SQL View as we call it, is painfully slow when initially accessed.

This is a workable way to the data in question, but no better than and usually worse than using legacy techniques such as DSPFD to an OUTFILE. #Disappointing

This support is possible with an IBM i Service

This support has been delivered with an IBM i Service .. Use the following

The CAAC has reviewed this requirement and recommends that IBM not implement this request since a solution already exists, as Rob Berendt pointed out below.

Background: The COMMON Americas Advisory Council (CAAC) members have a broad range of experience in working with small and medium-sized IBM i customers. CAAC has a key role in working with IBM i development to help assess the value and impact of individual RFEs on the broader IBM i community, and has therefore reviewed your RFE.

For more information about CAAC, see www.common.org/caac

For more details about CAAC's role with RFEs, see http://www.ibmsystemsmag.com/Blogs/i-Can/May-2017/COMMON-Americas-Advisory-Council-%28CAAC%29-and-RFEs/

Nancy Uthke-Schmucki - CAAC Program Manager

I reread your request. The API wasn't desired for speed, it was so that you could do something like:

SELECT system_table_schema, system_table_name, system_table_member, source_type, last_change_timestamp
FROM QSYS2.SYSPARTITIONSTAT
WHERE SYSTEM_TABLE_SCHEMA = 'ROUTINES'
AND SYSTEM_TABLE_NAME like('Q%')
ORDER BY system_table_member;

Try this.

However I get that if you have 2,000,000 partitions (barring any system limits) it may take awhile to generate that list. Cursor perhaps?

Try this:
-- category: Custom
-- description: List members (partitions) in a table
--
-- List partitions in a table
--
SELECT system_table_member
FROM QSYS2.SYSPARTITIONSTAT
WHERE SYSTEM_TABLE_SCHEMA = 'ROUTINES'
AND SYSTEM_TABLE_NAME = 'QRPGLESRC';
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzcatsyspstat.htm