IBM i - find and interrogate multimember files

File Members.sql

--
-- Description: Multi-member files are unique to DB2 for i.  Some older applications in your environment might
--              be utilizing the concept of multiple member files.  Sometimes these applications continue to
--              create additional members, with no cleanup in place.  Even if a file has been configured with 
--              "*NOMAX" maximum members, there is still a limit within the database/OS (32,767).  If a program
--              attempts to add a member to a file which has already reached the maximum allowable limit, an error
--              will occur (CPF3213).  Ideally we should get ahead of this issue before our files reach the max.
--
--              The following queries can be used for this type of investigation, finding multi-member files
--              so they can be addressed before the member limit is reached causing a production work stoppage.
--

--Description: files in this LPAR which have more than 100 members (exclude source files)
--NOTE: this might take more than 60 minutes to run!
SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(1)
    FROM QSYS2.SYSPARTITIONSTAT
    WHERE TABLE_NAME NOT LIKE 'Q%SRC'
    GROUP BY TABLE_SCHEMA, TABLE_NAME
    HAVING COUNT(1) > 100
    ORDER BY COUNT(1) DESC;


--Description: same as above, but only for one specific library
SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(1)
    FROM QSYS2.SYSPARTITIONSTAT
    WHERE TABLE_NAME NOT LIKE 'Q%SRC'
    AND TABLE_SCHEMA='LIBRARYNAME'
    GROUP BY TABLE_SCHEMA, TABLE_NAME
    HAVING COUNT(1) > 100
    ORDER BY COUNT(1) DESC;


--Description: for a specific file, how many members does it have?
SELECT COUNT(1)
    FROM qsys2.SYSPARTITIONSTAT
    WHERE TABLE_NAME = 'TABLENAME'
          AND TABLE_SCHEMA = 'LIBRARYNAME';


--Description: for a specific file, list all of the members
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_PARTITION,
       PARTITION_NUMBER,
       NUMBER_ROWS,
       CREATE_TIMESTAMP,
       LAST_CHANGE_TIMESTAMP,
       LAST_USED_TIMESTAMP,
       LAST_SOURCE_UPDATE_TIMESTAMP,
       PARTITION_TEXT
    FROM qsys2.SYSPARTITIONSTAT
    WHERE TABLE_NAME = 'TABLENAME'
          AND TABLE_SCHEMA = 'LIBRARYNAME';