SQL scalar function to break apart IFS path name

As part of the new new release, IBM i 7.6, and the latest Technology Refresh for IBM i 7.5, TR6, I can break apart an IFS path name into its parts:

• File extension
• File name
• File prefix (file name without the file extension)
• Path prefix (path without the file name)

This new SQL scalar function is called IFS_PATH, and it is found in the library SYSTOOLS.

Before I start with my examples I need to check if there are objects in the IFS I can use. This is a simple task using IFS_OBJECT_STATISTICS:

01 SELECT PATH_NAME 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 03 START_PATH_NAME => '/home/MyFolder', 04 SUBTREE_DIRECTORIES => 'NO', 05 OBJECT_TYPE_LIST => '*STMF')) 06 ORDER BY 1

Line 1: I am only interested in the path name.

Line 2: Use the SQL table function IFS_OBJECT_STATISTICS.

Line 3: I want a list of all the files in my folder.

Line 4: Not including a subfolders within my folder.

Line 5: And only stream files.

Line 6: Sort the results by the path name.

The results are:

PATH_NAME --------------------------- /home/MyFolder/csv_data.csv /home/MyFolder/file1.txt /home/MyFolder/file2.txt

As I have files in my folder, I can now demonstrate what IFS_PATH will do.

The syntax for this scalar function is:

01 SYSTOOLS.IFS_PATH(< path name >, < subsection >)

I have used the phrase subsection as that is what IBM call it. It is which part of the path name do you want? Possible values are:

• FILE EXTENSION
• FILE NAME
• FILE PREFIX
• PATH PREFIX

I can add multiple uses of IFS_PATH to my previous statement to demonstrate what it returns:

01 SELECT PATH_NAME, 02 SYSTOOLS.IFS_PATH(PATH_NAME,'FILE EXTENSION') AS "File ext", 03 SYSTOOLS.IFS_PATH(PATH_NAME,'FILE NAME') AS "File name", 04 SYSTOOLS.IFS_PATH(PATH_NAME,'FILE PREFIX') AS "File prefix", 05 SYSTOOLS.IFS_PATH(PATH_NAME,'PATH PREFIX') AS "Path prefix" 06 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 07 START_PATH_NAME => '/home/MyFolder', 08 SUBTREE_DIRECTORIES => 'NO', 09 OBJECT_TYPE_LIST => '*STMF')) 10 ORDER BY 1

Lines 2 – 5: Are IFS_PATH occurrences for each of types of data it will return. The results are:

File File PATH_NAME Ext File name prefix Path prefix --------------------------- ---- ------------ -------- --------------- /home/MyFolder/csv_data.csv csv csv_data.csv csv_data /home/MyFolder/ /home/MyFolder/file1.txt txt file1.txt file1 /home/MyFolder/ /home/MyFolder/file2.txt txt file2.txt file2 /home/MyFolder/

The above examples show what this scalar function can do.

I wanted to come up with a more "real world" example. In this scenario I need to delete all text files from a folder that are more than 30 days old. I also want to capture the names of all the files I have deleted.

I have a SQL DDL Table to contain the list of deleted files:

01 CREATE TABLE MYLIB.DELETED_TXT_FILES 02 FOR SYSTEM NAME "DLTTXTFILE" 03 (PATH_NAME FOR COLUMN "PATHNAME" VARCHAR(100), 04 CREATED_DATE FOR COLUMN "CRTDATE" DATE, 05 DELETED_DATE FOR COLUMN "DLTDATE" DATE, 06 ERRNO_CODE FOR COLUMN "ERRNOCDE" DEC(4,0), 07 ERRNO_DESCRIPTION FOR COLUMN "ERRNODESC" VARCHAR(100)) ;

Line 1: The SQL name for the table is DELETED_TXT_FILES, and it will created in my library.

Line 2: As the SQL name is longer than ten characters, I have given the table a short system name.

Lines 3 – 7: The columns I want in this table. Each one has a long SQL name and a short system name.

Now for the RPG program, with embedded SQL, to delete the files from the IFS and insert rows into this Table:

01 **free 02 exec sql 03 INSERT INTO DELETED_TXT_FILES 04 WITH T0 (PATH_NAME,CREATED,DELETED,ERRNO) AS 05 (SELECT PATH_NAME, 06 DATE(CREATE_TIMESTAMP), 07 CURRENT_DATE, 08 SYSTOOLS.IFS_UNLINK(PATH_NAME) 09 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 10 START_PATH_NAME => '/home/MyFolder', 11 SUBTREE_DIRECTORIES => 'NO', 12 OBJECT_TYPE_LIST => '*STMF')) 13 WHERE DATE(CREATE_TIMESTAMP) < CURRENT_DATE - 30 DAYS 14 AND UPPER(SYSTOOLS.IFS_PATH(PATH_NAME,'FILE EXTENSION')) = 'TXT') 15 SELECT T0.*, 16 SYSTOOLS.ERRNO_INFO(T0.ERRNO) 17 FROM T0 18 ORDER BY PATH_NAME ; 19 *inlr = *on ;

The above program just contains one SQL Insert statement to add data to the SQL table I created.

Line 3: Insert into the table DELETED_TXT_FILES the results from the following SQL statement.

I have used a Common Table Expression, CTE, as I want to do this in two steps. First step deletes the files, and the second returns the description for the ERRNO.

Lines 4 – 14: This is the first part, to delete the files.

Line 4: My temporary table is called T0 and has the columns:

• PATH_NAME:  Full path name of the file that was deleted
• CREATED:  The date the file was created
• DELETED:  The date the file was deleted, in other words today's date
• ERRNO:  ERRNO value

Line 5 – 8: The columns that will become the above mentioned columns in T0.

Line 8: IFS_UNLINK is the scalar function that will delete an object from the IFS. Here I have passed it the path name. It returns an ERRNO value, which is why I need a column for ERRNO in T0.

Lines 9 – 12: This has been the same in all my previous examples of using IFS_OBJECT_STATISTICS above.

Line 13: Select all files that are more than 30 days older than today.

Line 14: This is where I am using IFS_PATH. With the FILE_EXTENSION option I only want to select files with extension of "TXT". Notice that I converted the extension to upper case so that this would include all files with "txt" or "TXT" as the file extension.

Lines 15 - 18: This is the second part that "makes" the finished row to insert into DELETED_TXT_FILES.

Line 15: Include all the columns from T0.

Line 16: Use the ERRNO_INFO scalar function to get the description for the ERRNO generated by IFS_UNLINK.

Line 18: Order the results by the path name.

After creating the program, I call it, and then check the contents of DELETED_TXT_FILES Table:

01 SELECT * FROM DELETED_TXT_FILES

Which returns:

CREATED DELETED ERRNO PATH_NAME _DATE _DATE _CODE ERRNO_DESCRIPTION ------------------------ ---------- ---------- ----- ------------------ /home/MyFolder/file1.txt 2025-04-10 2025-05-12 0 There is no error. /home/MyFolder/file2.txt 2025-04-11 2025-05-12 0 There is no error.

As the ERRNO is zero I know that both of the text files I had in my folder were deleted.

 

You can learn more about the IFS_PATH SQL scalar function from the IBM website here.

 

This article was written for IBM i 7.6 and 7.5 TR6.