Finding source members with the same name

How would I find if a source member with the same name is found in more than one source file? If there is, which one was modified most recently? I am sure those are questions many of us have asked ourselves. How could we can make this easy for ourselves to get this information? Fortunately, Db2 for i has everything I need to do it.

I start with the SYSMEMBERSTAT View, it was introduced a couple of Technology Refreshes ago, IBM i 7.5 TR4 and 7.4 TR10, and it is used in place of SYSPARTITIONSTAT when I need information about members.

What are the columns I am interested in:

• SYSTEM_TABLE_SCHEMA:  Library the source file is in.
• SYSTEM_TABLE_NAME:  Source file name.
• SYSTEM_TABLE_MEMBER:  Source file member name.
• SOURCE_TYPE:  Source type for the member.
• NUMBER_ROWS:  Number of rows/records in the source file member.
• CREATE_TIMESTAMP:  The timestamp of when the source file member was created.
• LAST_CHANGE_TIMESTAMP:  The timestamp of when the source file member was last changed.
• LAST_SOURCE_UPDATE_TIMESTAMP:  The timestamp of when the source file member was last updated.
• TEXT_DESCRIPTION:  Member's description.

Using SYSMEMBERSTAT my statement to retrieve these columns would be:

01 SELECT SYSTEM_TABLE_SCHEMA, 02 SYSTEM_TABLE_NAME, 03 SYSTEM_TABLE_MEMBER, 04 SOURCE_TYPE, 05 NUMBER_ROWS, 06 CREATE_TIMESTAMP, 07 LAST_CHANGE_TIMESTAMP, 08 LAST_SOURCE_UPDATE_TIMESTAMP, 09 TEXT_DESCRIPTION 10 FROM QSYS2.SYSMEMBERSTAT 11 LIMIT 5

Lines 1 – 9: The columns I desire.

Line 10: From SYSMEMBERSTAT.

Line 11: Only return five results. I have done this as I only want to show the first few results.

The returned results are:

SYSTEM_ SYSTEM_ SYSTEM_ TABLE_ TABLE_ TABLE_ SOURCE NUMBER SCHEMA NAME MEMBER _TYPE _ROWS CREATE_TIMESTAMP ---------- ---------- ---------- ---------- ------ -------------------------- HSCOMMON10 SALESCUSTY SALESCUSTY <NULL> 12 2014-10-26 18:37:50.000000 SYSTOOLS SPEC_AUTH SPEC_AUTH <NULL> 640 2024-06-24 14:48:04.000000 TOYSTORE3 ITEM_YQM ITEM_YQM <NULL> 36 2008-02-27 16:14:35.000000 TOYSTORE3 YQM_R00001 YQM_R00001 <NULL> 36 2013-10-25 10:01:50.000000 TOYSTORE7 ITEM_YQM ITEM_YQM <NULL> 0 2023-05-30 15:13:06.000000 LAST_SOURCE_ LAST_CHANGE_TIMESTAMP UPDATE_TIMESTAMP TEXT_DESCRIPTION -------------------------- -------------------------- ---------------- 2022-05-12 15:45:09.000000 <NULL> <NULL> 2025-05-26 17:42:10.000000 <NULL> <NULL> 2023-05-27 08:38:04.000000 <NULL> <NULL> 2023-05-27 08:38:05.000000 <NULL> <NULL> 2023-05-30 15:13:06.000000 <NULL> <NULL>

These members are from data files, not source files. How can I determine what is a data or a source file? For that I would use the SYSFILES View. SYSFILES contains a column, FILE_TYPE, that returns if the file is a data or source file:

01 SELECT SYSTEM_TABLE_SCHEMA, 02 SYSTEM_TABLE_NAME, 03 FILE_TYPE 04 FROM QSYS2.SYSFILES 05 WHERE FILE_TYPE = 'SOURCE' 06 LIMIT 5

Lines 1 and 2: Library and file name.

Line 3: This columns contains either 'DATA' or 'SOURCE'.

Line 4: From SYSFILES.

Line 5: Only select source files.

Line 6: Only return the first five results.

The results are:

SYSTEM_ SYSTEM_ TABLE_ TABLE_ SCHEMA NAME FILE_TYPE ---------- ---------- --------- #CGULIB QS36PRC SOURCE #COBLIB QSBLSRC SOURCE #DFULIB QS36PRC SOURCE #DSULIB QS36PRC SOURCE #LIBRARY QS36SRC SOURCE

All of the results are source files.

I want to combine the results from these two Views to make the following statement to see what I want:

01 SELECT A.SYSTEM_TABLE_SCHEMA AS "Library", 02 A.SYSTEM_TABLE_NAME AS "File", 03 A.SYSTEM_TABLE_MEMBER AS "Member", 04 A.SOURCE_TYPE AS "Type", 05 TO_CHAR(A.NUMBER_ROWS,'999G999') AS "Rows", 06 TO_CHAR(A.CREATE_TIMESTAMP,'YYYY-MM-DD HH.MI.SS') AS "Created", 07 TO_CHAR(A.LAST_CHANGE_TIMESTAMP,'YYYY-MM-DD HH.MI.SS') AS "Last changed", 08 TO_CHAR(A.LAST_SOURCE_UPDATE_TIMESTAMP,'YYYY-MM-DD HH.MI.SS') AS "Last updated", 09 A.TEXT_DESCRIPTION AS "Member text" 10 FROM QSYS2.SYSMEMBERSTAT A, QSYS2.SYSFILES B 11 WHERE (A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME) = (B.SYSTEM_TABLE_SCHEMA,B.SYSTEM_TABLE_NAME) 12 AND B.FILE_TYPE = 'SOURCE' 13 AND A.SYSTEM_TABLE_SCHEMA = ? 14 ORDER BY A.SYSTEM_TABLE_NAME,A.SYSTEM_TABLE_MEMBER 15 LIMIT 5

Lines 1 – 9: The columns I want in my results. I have given them all shorter column headings.

Line 5: I want to format the number of rows with a thousand separator. To do this I use the TO_CHAR scalar function.

Lines 6 – 8: The milliseconds of the CREATE_TIMESTAMP, LAST_CHANGE_TIMESTAMP, and LAST_SOURCE_UPDATE_TIMESTAMP are always zero. Why include them in the results? I can use the TO_CHAR scalar function to format a timestamp without milliseconds.

Line 10: These are the two Views I need data from.

Line 11: This is a simple way to do a WHERE clause without the AND clause.

Line 12: I only want to return source files identified by SYSFILES.

Line 13: The question mark ( ? ) allows me to select the library dynamically when I run this statement. When the statement is executed a pop-up window is displayed, and I enter the name of the library I want the results for.

Line 14: Sort by the library, file, and member names.

Line 15: Only return the first five results.

When I execute the statement the pop-up appears:

I enter the library name in upper case, and press Enter.

The following results are returned:

Library File Member Type Rows Created Last changed ------- ------ --------- -------- ----- ------------------- ------------------- MYLIB DEVSRC ARRAY_TXT TXT 311 2023-08-11 03.14.53 2025-04-28 06.14.02 MYLIB DEVSRC ARRAYFILE PF 4 2023-08-11 12.12.31 2025-04-28 06.14.02 MYLIB DEVSRC ARRAYPGM1 RPGLE 48 2023-08-11 12.14.25 2025-04-28 06.14.02 MYLIB DEVSRC ARRAYPGM2 SQLRPGLE 72 2023-08-11 12.17.05 2025-04-28 06.14.02 MYLIB DEVSRC ARRAYPGM3 SQLRPGLE 70 2023-08-11 12.23.40 2025-04-28 06.14.02 Last updated Member text ------------------- --------------------------------- 2023-08-11 03.23.59 <NULL> 2023-08-11 02.12.45 <NULL> 2023-08-11 03.29.48 Simple array 2023-08-11 02.23.37 First example with DS array 2023-08-11 02.27.17 Second example with modern array

As these results are from a source file I have values in the source type and last source updated columns, which were null for the data files. The last change date is all April 28, 2025, as that is the date I restored this source file to this partition.

I know I am going to use this again, rather than have to enter the statement again and again, I am going to create a SQL View:

01 CREATE OR REPLACE VIEW MYLIB.MY_SOURCE_SEARCH 02 FOR SYSTEM NAME "MYSRCSRCH" 03 (LIBRARY,FILE,MEMBER,TYPE,ROWS,CREATED,LAST_CHANGED,LAST_UPDATED,MEMBER_TEXT) 04 AS 05 SELECT A.SYSTEM_TABLE_SCHEMA, 06 A.SYSTEM_TABLE_NAME, 07 A.SYSTEM_TABLE_MEMBER, 08 A.SOURCE_TYPE, 09 TO_CHAR(A.NUMBER_ROWS,'999G999'), 10 TO_CHAR(A.CREATE_TIMESTAMP,'YYYY-MM-DD HH.MI.SS'), 11 TO_CHAR(A.LAST_CHANGE_TIMESTAMP,'YYYY-MM-DD HH.MI.SS'), 12 TO_CHAR(A.LAST_SOURCE_UPDATE_TIMESTAMP,'YYYY-MM-DD HH.MI.SS'), 13 A.TEXT_DESCRIPTION 14 FROM QSYS2.SYSMEMBERSTAT A, QSYS2.SYSFILES B 15 WHERE (A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME) = (B.SYSTEM_TABLE_SCHEMA,B.SYSTEM_TABLE_NAME) 16 AND B.FILE_TYPE = 'SOURCE' ;

This is pretty much the same as my previous statement, except for:

Lines 1 and 2: My view has the long SQL name of MY_SOURCE_SEARCH and the system short name of MYSRCSRCH.

Line 3: I am going to use these names for the columns, rather than the names from SYSMEMBERSTAT.

After creating this View I can then use it, for example, searching all the RPG source files for a member name that start with 'TESTRPG':

01 SELECT * FROM MYLIB.MY_SOURCE_SEARCH 02 WHERE FILE = 'QRPGLESRC' 03 AND MEMBER LIKE 'TESTRPG%' 04 ORDER BY LIBRARY,FILE,MEMBER

Line 1: Select all the columns from my new View.

Lines 2 and 3: Where the source file name is the RPG source file, and the member names start with 'TESTRPG'.

The results return two source members:

LIBRARY FILE MEMBER TYPE ROWS CREATED LAST_CHANGED ---------- --------- --------- -------- ----- ------------------- ------------------- ENCRYPT QRPGLESRC TESTRPG1 RPGLE 16 2014-04-21 02.20.56 2022-05-12 03.44.41 RDIUSEROLD QRPGLESRC TESTRPG1 RPGLE 16 2018-05-19 01.08.02 2022-05-30 04.25.51 LAST_UPDATED MEMBER_TEXT ------------------- --------------------------------- 2012-05-17 02.48.17 <NULL> 2012-05-17 02.48.17 <NULL>

I could now compare the two to determine what the differences between them are.

If I wanted to search source members for a particular string then the above will not give you what you want. My post here explains a novel way to search multiple source files for the same string.

 

This article was written for IBM i 7.6, and should work for some earlier releases too.