Wednesday, January 31, 2024

Renaming IFS files using SQL

In the past if I needed to rename files in the IFS I would either use the Rename Object command, RNM, or used the 'Integrated File System' tool in ACS.

The latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, brings us a Db2 for i, or SQL, scalar function that allows me to rename files in the IFS, IFS_RENAME. The scalar function has three parameters:

  1. FROM_OBJECT:  The path of the file to be renamed.
  2. TO_OBJECT:  The path of what the file will be called.
  3. REPLACE:  If there is a file with the To Object name should it be replaced? 'YES' and 'NO' are the allowed values. If none is given the default is 'NO'.

I have uploaded three files to my folder, MyFolder. I can get a list of files in this folder using the IFS_OBJECT_STATISTICS Table function.

03                 START_PATH_NAME => '/home/MyFolder',
04                 SUBTREE_DIRECTORIES => 'NO',
05                 OBJECT_TYPE_LIST => '*STMF'))

Line 1: I only want the Path Name and the Object Type columns returned in my results.

Lines 2 – 5: I only want the following returned from the IFS_OBJECT_STATISTICS Table function:

  • Objects in my IFS directory
  • No objects to be listed in any subdirectories that may be in my IFS directory
  • List only Stream Files

The results show the three files I copied to the IFS:

PATH_NAME                   OBJECT_TYPE
--------------------------  -----------
/home/MyFolder/myfile.txt   *STMF
/home/MyFolder/myfile2.txt  *STMF
/home/MyFolder/myfile3.txt  *STMF

The first one, 'myfile.txt', is missing a number. I can use the IFS_RENAME to rename it so it has a number in the file name like the other two files:

02                    FROM_OBJECT => '/home/MyFolder/myfile.txt',
03                    TO_OBJECT => '/home/MyFolder/myfile1.txt',
04                    REPLACE => 'YES')

Line 1: As it is a Scalar function I use VALUES to execute it.

Line 2: This is the path of the existing file.

Line 3: This is path of what I want the file to be called

Line 4: If the to object exists, replace it.

When executed this statement returns:


A value of zero means that the statement completed successfully.

I can check that the rename happened using the IFS_OBJECT_STATISTICS again:

03                 START_PATH_NAME => '/home/MyFolder',
04                 SUBTREE_DIRECTORIES => 'NO',
05                 OBJECT_TYPE_LIST => '*STMF'))

The only difference are the columns I selected. This time I wanted the create timestamp column. You'll see why I wanted it in just a moment.

The results are:

--------------------------  -----------  -------------------
/home/MyFolder/myfile1.txt  *STMF        2023-12-31 14:51:16
/home/MyFolder/myfile2.txt  *STMF        2023-12-31 14:52:01
/home/MyFolder/myfile3.txt  *STMF        2023-12-31 14:52:10

How could I rename more than one IFS file at a time? In this example I want to copy all the files from '/home/MyFolder' to '/home/MyBackup', and rename the files to include the create timestamp as part of the file name. For this I would combine the IFS_OBJECT_STATISTICS Table function with the IFS_RENAME scalar function.

This is how I would do it:

03                             '/home/MyBackup/myfile' || CREATE_TIMESTAMP || 
04                             'NO')

Line 1: I need the path name.

Lines 2 – 4: This is the IFS_RENAME Scalar function. I am not using the parameter descriptions, so the first parameter, line 2, is the from path name. The second parameter, line 3, is the to path name. I have hard coded the IFS directory and that the file name will start as 'myfile', then concatenated the create timestamp, and the file extension to create the to path. As the third parameter, line 4, is 'NO' I will not replace an existing file with the to path name if it already exists.

Line 5: This is the same as the IFS_OBJECT_STATISTICS parameters without their names.

When result the results are:

PATH_NAME                  00002
-------------------------  ------
/home/MyFolder/myfile1.txt      0
/home/MyFolder/myfile2.txt      0
/home/MyFolder/myfile3.txt      0

Three rows with zeroes means that the rename was successful.

I use the IFS_OBJECT_STATISTICS to list the files in the directory I copied them to:

03               START_PATH_NAME => '/home/MyBackup',
04               SUBTREE_DIRECTORIES => 'NO',
05               OBJECT_TYPE_LIST => '*STMF'))

Line 3: This is the only difference from the previous statements. Here I want to see what files are in my backup directory.

The results are:

PATH_NAME                                     OBJECT_TYPE
--------------------------------------------  ----------- 
/home/MyBackup/myfile2023-12-31-14.52.10.txt  *STMF
/home/MyBackup/myfile2023-12-31-14.51.16.txt  *STMF
/home/MyBackup/myfile2023-12-31-14.52.01.txt  *STMF

I have successfully moved them to my backup directory, and renamed them all to contain their create timestamp.

I can see myself using this a lot, and it is going to be simpler than using the RNM command.


You can learn more about the IFS_RENAME SQL Scalar function command from the IBM website here.


This article was written for IBM i 7.5 TR3 and 7.4 TR9.


  1. In "As the third parameter, line 4, is 'NO' I will replace an existing file" i think there is a "not" missing. I'd discard it and mention that NO is the default.
    And you're sometimes calling this "copying" when it's actually a move, if i understand that beast correctly.

  2. is it best practice to have SQL functions performing updates on the system when the SQL SELECT statement is run? In the applications I write I find it best to avoid having an SQL function update the database. Maybe this is different since the update is being done to the IFS.

    1. It depends... there are some SQL functions I do let work with objects and some that I do not.
      I would say do what you are comfortable with But don't exclude using SQL to create/change/delete objects as sometimes it is just easier to have it do so.

    2. The problem with functions doing updates is concurrency. As I understand it, SQL will select rows in parallel. Which causes multiple instances of the function to run at the same time. If the functions are updating the database there are scenarios where you get unpredictable results. I think that is what ATOMIC is for in the SQL function declaration. ATOMIC forces SQL to run the function one instance at a time. These additional rules make your code more of a chore for other programmers to work on.


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.