New View lists all file members

If I wanted to get a list of members in a file with SQL I always had to work with the SYSPARTITIONSTAT View, which was not the purpose it was designed for. In the latest latest Technology Refreshes a new View has been added that is designed for information about members, SYSMEMBERSTAT.

The new SYSMEMBERSTAT View is in the library QSYS2 and contains all the columns I could want to know about members in a file.

I always recommend if this is the first time you are using a View you should look at all of the columns and determine, for yourself, which ones are important. The following statement displays all the columns in SYSMEMBERSTAT:

SELECT * FROM QSYS2.SYSMEMBERSTAT

Many of the columns I used in the in the SYSPARTITIONSTAT View have the same names as the ones in SYSMEMBERSTAT.

One of the most interesting columns I found was SOURCE_TYPE. If the member is from a source file this column contains the source type. In a data file this column is null. This allows me to quickly separate the data from source files, without having to use the SYSFILES View.

In this first example if I want to list all of the physical data members in my library, MYLIB, I can use the following statement:

01 SELECT SYSTEM_TABLE_NAME AS "File", 02 SYSTEM_TABLE_MEMBER AS "Member", 03 TEXT_DESCRIPTION AS "Description" 04 FROM QSYS2.SYSMEMBERSTAT 05 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 06 AND SOURCE_TYPE IS NULL

Lines 1 – 3: These columns have the same names in SYSPARTITIONSTAT. I have given them all column heading so that my results will fit on this page.

Line 4: Here I state that the columns are to come from just the SYSMEMBERSTAT View.

Line 5: This is the name of my library.

Line 6: I only want the rows for the members in my library where the source type is null, therefore, only the members from physical data files.

The results are:

File Member Description -------- ---------- -------------------------- CUSTOMER CUSTOMER Customer file DETAILS DETAILS Details file PDMOPTS QAUOOPT Model option file for PDM PERSON PERSON Person table PTFLETTERS PTFLETTERS PTF letters TESTFILE TESTFILE Test DDS file TESTFILE2 TESTFILE2 Test file 2 TESTTABLE TESTTABLE Test DDL table

In the next example I am going to "flip" this around and want to have a list of all the physical source members in my library:

01 SELECT SYSTEM_TABLE_NAME AS "File", 02 SYSTEM_TABLE_MEMBER AS "Member", 03 SOURCE_TYPE AS "Type", 04 TEXT_DESCRIPTION AS "Description" 05 FROM QSYS2.SYSMEMBERSTAT 06 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 07 AND SOURCE_TYPE IS NOT NULL 08 ORDER BY SYSTEM_TABLE_MEMBER 09 LIMIT 5

Line 3: I included the source type column in this statement as I want to know what kind of source is in the member. And yes I do know I can have "RPGLE" as the source type and the member can contain something like CL statements. But as a rule of thumb if it says "RPGLE" 99.9% of the time the source members will contain RPG code.

Line 7: Now the source type has to be not null for source members.

Line 8: As I only have one source file in my library I only need to order by results by the source member name.

Line 9: And I only want the first five members returned in my results, even though there are many more.

The results are:

File Member Type Description ------- -------- ----- ------------------------------------------- DEVSRC CHGAUTU4 CLLE Change authority to give RPGPGM all auth to DEVSRC CUSTOMER PF Customer file DEVSRC CUSTOMER0 LF Customer file: Region,Name DEVSRC DETAILS PF Details file DEVSRC INLPGM CLLE <NULL>

What I did use SYSPARTITIONSTAT for was to identify files that contain deleted records. This information could then be used to remove the deleted records using the RGZPFM< command.

I had to join SYSPARTITIONSTAT and SYSFILES together to be able to identify which members are in physical data files. Using SYSMEMBERSTAT it is possible to identify those files and members:

01 SELECT SYSTEM_TABLE_SCHEMA AS "Library", 02 SYSTEM_TABLE_NAME AS "File", 03 SYSTEM_TABLE_MEMBER AS "Member", 04 NUMBER_ROWS AS "Recs", 05 NUMBER_DELETED_ROWS AS "Nbr dlt" 06 FROM QSYS2.SYSMEMBERSTAT 07 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 08 AND SOURCE_TYPE IS NULL 09 AND NUMBER_DELETED_ROWS > 0 10 ORDER BY "Nbr dlt" DESC, "Recs" DESC

Lines 1 – 5: These are the columns I need to identify and pass to the RGZPFM command.

Lines 7: I have limited the results just to my library so I don't have to wait a long time to get results I can manage.

Line 8: If source type is null then this member is in a physical data file.

Line 9: I only want the members that contain deleted records.

line 10: And I can use the column headings I created for the columns in the Order by clause.

There is only one result as all the physical files I create reuse delete records. The file that does contain a deleted record I created for this post:

Library File Member Recs Nbr dlt ------- --------- --------- ----- -------- MYLIB TESTFILE2 TESTFILE2 9 1

When I do a mass RGZPFM I output the eligible files to an output file. I then review the files and members and if I don't want to reorganize them I can delete the row from the output file.

This statement creates that output file using the results from SYSMEMBERSTAT:

01 CREATE TABLE MYLIB.TO_RGZPFM 02 (LIBRARY,FILE,MEMBER,RECORDS,DLT_RECORDS) 03 AS 04 (SELECT SYSTEM_TABLE_SCHEMA, 05 SYSTEM_TABLE_NAME, 06 SYSTEM_TABLE_MEMBER, 07 NUMBER_ROWS, 08 NUMBER_DELETED_ROWS 09 FROM QSYS2.SYSMEMBERSTAT 10 WHERE SYSTEM_TABLE_SCHEMA IN ('MYLIB','MYLIB1','MYLIB2') 11 AND SOURCE_TYPE IS NULL 12 AND NUMBER_DELETED_ROWS > 0 13 ORDER BY NUMBER_DELETED_ROWS DESC) 14 WITH DATA

Line 1: The table will be created in my library.

Line 2: I am giving the columns short names as we all know what each one contains.

Lines 4 – 8: Same columns as the previous statement.

line 10: I only want to list the eligible members from a few libraries. I do not want to RGZPGM files in other libraries, for example IBM libraries or in various third part tool libraries.

Line 11 and 12: Same as before.

Line 14: I want to list the files with the most deleted records first in the output file.

Line 14: And I want data in my file.

I can use the following statement to see all of my eligible members:

SELECT * FROM TO_RGZPFM

And the results are exactly what I expected:

LIBRARY FILE MEMBER RECORDS DLT_RECORDS ------- --------- --------- ------- ----------- MYLIB TESTFILE2 TESTFILE2 9 1

Another good feature is rather than use the IDENTITY_VAL_LOCAL Scalar function to return the next value for the member's identity column, I can the get the same data from the column NEXT_IDENTITY_VALUE in SYSMEMBERSTAT.

01 SELECT SYSTEM_TABLE_SCHEMA AS "Library", 02 SYSTEM_TABLE_NAME AS "File", 03 SYSTEM_TABLE_MEMBER AS "Member", 04 NEXT_IDENTITY_VALUE AS "Nxt identity" 05 FROM QSYS2.SYSMEMBERSTAT 06 WHERE SOURCE_TYPE IS NULL 07 AND NEXT_IDENTITY_VALUE IS NOT NULL 08 ORDER BY NEXT_IDENTITY_VALUE DESC 09 LIMIT 5

Line 4: This is the column for the next identity value.

Line 7: If the next identity value is null for a member it does not contain an identity column. I am only interested in table with identity columns, therefore, I need this column to not be null.

Line 8: Order by in descending order the largest value.

My results are:

Library File Member Nxt identity ------- --------- --------- ------------ QLWIRADM QALWIRR QALWIRR 1481 QNEWNAVSRV METAINF METAINF 21 QNEWNAVSRV MNTLOG MNTLOG 21 QNEWNAVSRV MNTSVR MNTSVR 21 QLWIRADM QALWIUR QALWIUR 1

IMHO this is a wonderful addition to Db2 for i. I know I will be using this many times in the future.

 

You can learn more about the SYSMEMBERSTAT command from the IBM website here.

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.