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.

01 SELECT PATH_NAME,OBJECT_TYPE 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 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:

01 VALUES SYSTOOLS.IFS_RENAME( 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:

00001 ------ 0

A value of zero means that the statement completed successfully.

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

01 SELECT PATH_NAME,OBJECT_TYPE,CREATE_TIMESTAMP 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 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:

PATH_NAME OBJECT_TYPE CREATE_TIMESTAMP -------------------------- ----------- ------------------- /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:

01 SELECT PATH_NAME, 02 SYSTOOLS.IFS_RENAME(PATH_NAME, 03 '/home/MyBackup/myfile' || CREATE_TIMESTAMP || '.txt', 04 'NO') 05 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/MyFolder','NO','*STMF'))

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:

01 SELECT PATH_NAME,OBJECT_TYPE 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 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.