Wednesday, September 18, 2024

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.

2 comments:

  1. Thanks, excellent decripted as always!

    ReplyDelete
  2. Checking SOURCE_TYPE IS NULL doesn't guarantee a data file. I used ACS to generate the SQL source for a trigger and placed it in a source physical file. This resulted in the source type being null. I would presume all generated source would do the same.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.