sriedmue79 · January 30, 2026 14:57
diff --git a/IFS File Ownership Cleanup1a.sql b/IFS File Ownership Cleanup1a.sql
 --
 -- Description: Can we use SQL to clean up old unused IFS files/directories based on the owner?
 -- Warning: PLEASE READ :) Before executing the queries that utilize QSYS2.QCMDEXC() please carefully review the
 --          generated RMVLNK commands and ensure that you truly want those files/directories to be deleted!

 --First, let's retrieve a list of IFS streamfiles owned by user profile "JANDERSON"
 --
 --The QSYS2.OBJECT_OWNERSHIP() table function returns a list of objects owned by the user profile that is passed in.
 --  This includes IFS files and directories, as well as QSYS objects like *PGM, *FILE, and *LIB objects etc.  In the case
 --  of QSYS objects the "PATH_NAME" field will be null. Since we only want IFS files, we specify WHERE PATH_NAME IS NOT NULL

 SELECT PATH_NAME, OBJECT_TYPE
    FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') )
    WHERE PATH_NAME IS NOT NULL;

diff --git a/IFS File Ownership Cleanup1b.md b/IFS File Ownership Cleanup1b.md
diff --git a/IFS File Ownership Cleanup2a.sql b/IFS File Ownership Cleanup2a.sql
 --Next, let's see how to use IFS_OBJECT_STATISTICS to view details about one specific IFS file.
 --
 --The QSYS2.IFS_OBJECT_STATISTICS() table function can return various attributes of an IFS file/directory
 --  including the creation timestamp, last accessed timestamp, size, etc.  Those attributes are not returned
 --  by the OBJECT_OWNERSHIP function, but we need them for this cleanup effort.

 SELECT PATH_NAME, OBJECT_TYPE, CREATE_TIMESTAMP, ACCESS_TIMESTAMP, DATA_CHANGE_TIMESTAMP
    FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS(
                           START_PATH_NAME     => '/home/JANDERSON/very_important_file1.pdf',
                           SUBTREE_DIRECTORIES => 'NO') );

diff --git a/IFS File Ownership Cleanup2b.md b/IFS File Ownership Cleanup2b.md
diff --git a/IFS File Ownership Cleanup3a.sql b/IFS File Ownership Cleanup3a.sql
 --Let's create an example RMVLNK command for deleting a single IFS streamfile.
 --
 --The CL command "RMVLNK" can be used to delete a file from the IFS.  Here is an example of what such a command
 --   might look like.  Note that the value passed on the OBJLNK parameter must be enclosed in single quotes.
 --  Since we are building this command in an SQL string, we have to "double up" the single quotes:

 VALUES 'RMVLNK OBJLNK(''/home/JANDERSON/very_important_file1.pdf'')';

diff --git a/IFS File Ownership Cleanup3b.md b/IFS File Ownership Cleanup3b.md
diff --git a/IFS File Ownership Cleanup4a.sql b/IFS File Ownership Cleanup4a.sql
 --We can execute that RMVLNK command using QSYS2.QCMDEXC().  This will delete a single IFS streamfile.
 --  Using QSYS2.QCMDEXC() around the command string means the RMVLNK command will be executed on the server!

 VALUES QSYS2.QCMDEXC('RMVLNK OBJLNK(''/home/JANDERSON/very_important_file1.pdf'')');

diff --git a/IFS File Ownership Cleanup4b.md b/IFS File Ownership Cleanup4b.md
diff --git a/IFS File Ownership Cleanup5a.sql b/IFS File Ownership Cleanup5a.sql
 --Now let's start putting these steps together.  We want to:
 --  1. Retrieve the list of IFS files owned by JANDERSON
 --  2. Also retrieve the relevant attributes about each file
 --
 --We can combine the OBJECT_OWNERSHIP() and IFS_OBJECT_STATISTICS() table functions to retrieve the list of files owned
 --  by JANDERSON, plus the  additional attributes about each of those streamfiles.  We can select records based on the
 --  "ACCESS_TIMESTAMP" which indicates the last time the file/directory was accessed.

 --Note: If IFS_OBJECT_STATISTICS receives a directory as input, it will list all files within that directory.
 --      This means the results could include files that are NOT owned by the user profile we are targeting!
 --      To mitigate this, we include "WHERE PATH_NAME=OWNED_FILES.PATH_NAME" within the inner query.

 WITH OWNED_FILES AS (
        SELECT PATH_NAME, OBJECT_TYPE
            FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') ) )
    SELECT 'RMVLNK OBJLNK(''' || OWNED_FILES.PATH_NAME || ''')' AS DLT_COMMAND, 
           OWNED_FILES.PATH_NAME, 
           PATH_ATTR.CREATE_TIMESTAMP, 
           PATH_ATTR.ACCESS_TIMESTAMP, 
           PATH_ATTR.DATA_CHANGE_TIMESTAMP
        FROM OWNED_FILES, LATERAL (
                 SELECT CREATE_TIMESTAMP, 
                        ACCESS_TIMESTAMP, 
                        DATA_CHANGE_TIMESTAMP
                     FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS ( START_PATH_NAME     => OWNED_FILES.PATH_NAME, 
                                                                SUBTREE_DIRECTORIES => 'NO') ) 
                     WHERE PATH_NAME=OWNED_FILES.PATH_NAME ) AS PATH_ATTR
        WHERE PATH_NAME IS NOT NULL
              AND OWNED_FILES.OBJECT_TYPE = '*STMF'
              AND PATH_ATTR.ACCESS_TIMESTAMP <= '2026-01-08'
        ORDER BY PATH_ATTR.ACCESS_TIMESTAMP;

diff --git a/IFS File Ownership Cleanup5b.md b/IFS File Ownership Cleanup5b.md
diff --git a/IFS File Ownership Cleanup6a.sql b/IFS File Ownership Cleanup6a.sql
 --For each of those files we can build the RMVLNK CL commands for deleting them.  We build those RMVLNK
 --  commands dynamically by concatenating the PATH_NAME into the OBJLNK() parameter.

 WITH OWNED_FILES AS (
        SELECT PATH_NAME, OBJECT_TYPE
            FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') ) )
    SELECT 'RMVLNK OBJLNK(''' || OWNED_FILES.PATH_NAME || ''')' AS DLT_COMMAND, 
           OWNED_FILES.PATH_NAME, 
           PATH_ATTR.CREATE_TIMESTAMP,
           PATH_ATTR.ACCESS_TIMESTAMP, 
           PATH_ATTR.DATA_CHANGE_TIMESTAMP
        FROM OWNED_FILES, LATERAL (
                 SELECT CREATE_TIMESTAMP, 
                        ACCESS_TIMESTAMP, 
                        DATA_CHANGE_TIMESTAMP
                     FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS ( START_PATH_NAME     => OWNED_FILES.PATH_NAME, 
                                                                SUBTREE_DIRECTORIES => 'NO') ) 
                     WHERE PATH_NAME=OWNED_FILES.PATH_NAME ) AS PATH_ATTR
        WHERE PATH_NAME IS NOT NULL
              AND OWNED_FILES.OBJECT_TYPE = '*STMF'
              AND PATH_ATTR.ACCESS_TIMESTAMP < '2026-01-08'
        ORDER BY PATH_ATTR.ACCESS_TIMESTAMP;

diff --git a/IFS File Ownership Cleanup6b.md b/IFS File Ownership Cleanup6b.md
diff --git a/IFS File Ownership Cleanup7a.sql b/IFS File Ownership Cleanup7a.sql
 --After verifying that the commands look ok, and that we truly want to delete those files, we can add
 --  the QSYS2.QCMDEXC() function to actually execute those RMVLNK commands!

 WITH OWNED_FILES AS (
        SELECT PATH_NAME, OBJECT_TYPE
            FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') ) )
    SELECT QSYS2.QCMDEXC('RMVLNK OBJLNK(''' || OWNED_FILES.PATH_NAME || ''')') AS DLT_COMMAND, 
           OWNED_FILES.PATH_NAME, 
           PATH_ATTR.CREATE_TIMESTAMP,
           PATH_ATTR.ACCESS_TIMESTAMP, 
           PATH_ATTR.DATA_CHANGE_TIMESTAMP
        FROM OWNED_FILES, LATERAL (
                 SELECT CREATE_TIMESTAMP, 
                        ACCESS_TIMESTAMP, 
                        DATA_CHANGE_TIMESTAMP
                     FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS ( START_PATH_NAME     => OWNED_FILES.PATH_NAME, 
                                                                SUBTREE_DIRECTORIES => 'NO') ) 
                     WHERE PATH_NAME=OWNED_FILES.PATH_NAME ) AS PATH_ATTR
        WHERE PATH_NAME IS NOT NULL
              AND OWNED_FILES.OBJECT_TYPE = '*STMF'
              AND PATH_ATTR.ACCESS_TIMESTAMP < '2026-01-08'
        ORDER BY PATH_ATTR.ACCESS_TIMESTAMP;

diff --git a/IFS File Ownership Cleanup7b.md b/IFS File Ownership Cleanup7b.md
diff --git a/IFS File Ownership Cleanup8a.sql b/IFS File Ownership Cleanup8a.sql
 --Finally, we can confirm that the files are no longer listed under the user's ownership.
 --
 --Note: If the target user owned any directories, the deletion of one of those directories will fail if 
 --      there are still other files within the directory.

 SELECT PATH_NAME, OBJECT_TYPE
    FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') )
    WHERE PATH_NAME IS NOT NULL
          AND OBJECT_TYPE = '*STMF';

diff --git a/IFS File Ownership Cleanup8b.md b/IFS File Ownership Cleanup8b.md
	--
	-- Description: Can we use SQL to clean up old unused IFS files/directories based on the owner?
	-- Warning: PLEASE READ :) Before executing the queries that utilize QSYS2.QCMDEXC() please carefully review the
	-- generated RMVLNK commands and ensure that you truly want those files/directories to be deleted!

	--First, let's retrieve a list of IFS streamfiles owned by user profile "JANDERSON"
	--
	--The QSYS2.OBJECT_OWNERSHIP() table function returns a list of objects owned by the user profile that is passed in.
	-- This includes IFS files and directories, as well as QSYS objects like PGM, FILE, and *LIB objects etc. In the case
	-- of QSYS objects the "PATH_NAME" field will be null. Since we only want IFS files, we specify WHERE PATH_NAME IS NOT NULL

	SELECT PATH_NAME, OBJECT_TYPE
	FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') )
	WHERE PATH_NAME IS NOT NULL;
	--Next, let's see how to use IFS_OBJECT_STATISTICS to view details about one specific IFS file.
	--
	--The QSYS2.IFS_OBJECT_STATISTICS() table function can return various attributes of an IFS file/directory
	-- including the creation timestamp, last accessed timestamp, size, etc. Those attributes are not returned
	-- by the OBJECT_OWNERSHIP function, but we need them for this cleanup effort.

	SELECT PATH_NAME, OBJECT_TYPE, CREATE_TIMESTAMP, ACCESS_TIMESTAMP, DATA_CHANGE_TIMESTAMP
	FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS(
	START_PATH_NAME => '/home/JANDERSON/very_important_file1.pdf',
	SUBTREE_DIRECTORIES => 'NO') );
	--Let's create an example RMVLNK command for deleting a single IFS streamfile.
	--
	--The CL command "RMVLNK" can be used to delete a file from the IFS. Here is an example of what such a command
	-- might look like. Note that the value passed on the OBJLNK parameter must be enclosed in single quotes.
	-- Since we are building this command in an SQL string, we have to "double up" the single quotes:

	VALUES 'RMVLNK OBJLNK(''/home/JANDERSON/very_important_file1.pdf'')';
	--We can execute that RMVLNK command using QSYS2.QCMDEXC(). This will delete a single IFS streamfile.
	-- Using QSYS2.QCMDEXC() around the command string means the RMVLNK command will be executed on the server!

	VALUES QSYS2.QCMDEXC('RMVLNK OBJLNK(''/home/JANDERSON/very_important_file1.pdf'')');
	--Now let's start putting these steps together. We want to:
	-- 1. Retrieve the list of IFS files owned by JANDERSON
	-- 2. Also retrieve the relevant attributes about each file
	--
	--We can combine the OBJECT_OWNERSHIP() and IFS_OBJECT_STATISTICS() table functions to retrieve the list of files owned
	-- by JANDERSON, plus the additional attributes about each of those streamfiles. We can select records based on the
	-- "ACCESS_TIMESTAMP" which indicates the last time the file/directory was accessed.

	--Note: If IFS_OBJECT_STATISTICS receives a directory as input, it will list all files within that directory.
	-- This means the results could include files that are NOT owned by the user profile we are targeting!
	-- To mitigate this, we include "WHERE PATH_NAME=OWNED_FILES.PATH_NAME" within the inner query.

	WITH OWNED_FILES AS (
	SELECT PATH_NAME, OBJECT_TYPE
	FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') ) )
	SELECT 'RMVLNK OBJLNK(''' \|\| OWNED_FILES.PATH_NAME \|\| ''')' AS DLT_COMMAND,
	OWNED_FILES.PATH_NAME,
	PATH_ATTR.CREATE_TIMESTAMP,
	PATH_ATTR.ACCESS_TIMESTAMP,
	PATH_ATTR.DATA_CHANGE_TIMESTAMP
	FROM OWNED_FILES, LATERAL (
	SELECT CREATE_TIMESTAMP,
	ACCESS_TIMESTAMP,
	DATA_CHANGE_TIMESTAMP
	FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS ( START_PATH_NAME => OWNED_FILES.PATH_NAME,
	SUBTREE_DIRECTORIES => 'NO') )
	WHERE PATH_NAME=OWNED_FILES.PATH_NAME ) AS PATH_ATTR
	WHERE PATH_NAME IS NOT NULL
	AND OWNED_FILES.OBJECT_TYPE = '*STMF'
	AND PATH_ATTR.ACCESS_TIMESTAMP <= '2026-01-08'
	ORDER BY PATH_ATTR.ACCESS_TIMESTAMP;
	--For each of those files we can build the RMVLNK CL commands for deleting them. We build those RMVLNK
	-- commands dynamically by concatenating the PATH_NAME into the OBJLNK() parameter.

	WITH OWNED_FILES AS (
	SELECT PATH_NAME, OBJECT_TYPE
	FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') ) )
	SELECT 'RMVLNK OBJLNK(''' \|\| OWNED_FILES.PATH_NAME \|\| ''')' AS DLT_COMMAND,
	OWNED_FILES.PATH_NAME,
	PATH_ATTR.CREATE_TIMESTAMP,
	PATH_ATTR.ACCESS_TIMESTAMP,
	PATH_ATTR.DATA_CHANGE_TIMESTAMP
	FROM OWNED_FILES, LATERAL (
	SELECT CREATE_TIMESTAMP,
	ACCESS_TIMESTAMP,
	DATA_CHANGE_TIMESTAMP
	FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS ( START_PATH_NAME => OWNED_FILES.PATH_NAME,
	SUBTREE_DIRECTORIES => 'NO') )
	WHERE PATH_NAME=OWNED_FILES.PATH_NAME ) AS PATH_ATTR
	WHERE PATH_NAME IS NOT NULL
	AND OWNED_FILES.OBJECT_TYPE = '*STMF'
	AND PATH_ATTR.ACCESS_TIMESTAMP < '2026-01-08'
	ORDER BY PATH_ATTR.ACCESS_TIMESTAMP;
	--After verifying that the commands look ok, and that we truly want to delete those files, we can add
	-- the QSYS2.QCMDEXC() function to actually execute those RMVLNK commands!

	WITH OWNED_FILES AS (
	SELECT PATH_NAME, OBJECT_TYPE
	FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') ) )
	SELECT QSYS2.QCMDEXC('RMVLNK OBJLNK(''' \|\| OWNED_FILES.PATH_NAME \|\| ''')') AS DLT_COMMAND,
	OWNED_FILES.PATH_NAME,
	PATH_ATTR.CREATE_TIMESTAMP,
	PATH_ATTR.ACCESS_TIMESTAMP,
	PATH_ATTR.DATA_CHANGE_TIMESTAMP
	FROM OWNED_FILES, LATERAL (
	SELECT CREATE_TIMESTAMP,
	ACCESS_TIMESTAMP,
	DATA_CHANGE_TIMESTAMP
	FROM TABLE ( QSYS2.IFS_OBJECT_STATISTICS ( START_PATH_NAME => OWNED_FILES.PATH_NAME,
	SUBTREE_DIRECTORIES => 'NO') )
	WHERE PATH_NAME=OWNED_FILES.PATH_NAME ) AS PATH_ATTR
	WHERE PATH_NAME IS NOT NULL
	AND OWNED_FILES.OBJECT_TYPE = '*STMF'
	AND PATH_ATTR.ACCESS_TIMESTAMP < '2026-01-08'
	ORDER BY PATH_ATTR.ACCESS_TIMESTAMP;
	--Finally, we can confirm that the files are no longer listed under the user's ownership.
	--
	--Note: If the target user owned any directories, the deletion of one of those directories will fail if
	-- there are still other files within the directory.

	SELECT PATH_NAME, OBJECT_TYPE
	FROM TABLE ( QSYS2.OBJECT_OWNERSHIP(USER_PROFILE => 'JANDERSON') )
	WHERE PATH_NAME IS NOT NULL
	AND OBJECT_TYPE = '*STMF';