Deleting objects in the IFS with a SQL scalar function

This post is a two-for-one where I will explain how two of the additions that came in the latest Technology Refreshes go together.

The first is a new scalar function IFS_UNLINK, which is found in the SYSTOOLS library, that deletes IFS objects that are passed to it as a parameter. Behind the scenes it calls the unlink() C API. The scalar function returns the integer value that is returned by the C API. If it completed successfully, IFS_UNLINK returns a value of zero. If there was an error the scalar function returns an errno value.

The second addition, ERRNO_INFO, which is also found in the SYSTOOLS library, is a Table function that will return the description of the errno passed to it.

The easiest way I know to be able to show you how it works is to show an example of using it.

I am going to need a IFS directory with some files in it I want to delete. Fortunately, I have objects in the IFS path /home/MyDir/subfolder2 that I no longer need. I can get a list of those objects by 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/MyDir/subfolder2', 04 SUBTREE_DIRECTORIES => 'NO'))

Line 3: This is the path where I want to list the objects in.

line 4: I do not want to include any subdirectories the directory may have. It does not have any, but if it did, I am not interested in the contents of those subdirectories.

The results are:

PATH_NAME OBJECT_TYPE -------------------------------------- ----------- /home/MyDir/subfolder2/ *DIR /home/MyDir/subfolder2/pdf_file_1.pdf *STMF /home/MyDir/subfolder2/csv_file_1.csv *STMF /home/MyDir/subfolder2/text_file_2.txt *STMF /home/MyDir/subfolder2/text_file_1.txt *STMF

I thought a long time about the statement I would use to delete those objects and list the errno with its description. The easiest to write, and I think to understand, is to use a Common Table Expression, CTE. The SQL statement I created was:

01 WITH T0(PATH_NAME,OBJECT_TYPE,ERRNO) 02 AS 03 (SELECT PATH_NAME,OBJECT_TYPE,SYSTOOLS.IFS_UNLINK(PATH_NAME) 04 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 05 START_PATH_NAME => '/home/MyDir/subfolder2', 06 SUBTREE_DIRECTORIES => 'NO'))) 07 SELECT T0.*,SYSTOOLS.ERRNO_INFO(T0.ERRNO) AS "ERRNO description" 08 FROM T0

Lines 1 – 6: In the first part of the CTE I make a temporary table, T0, containing:

• PATH_NAME:  IFS path name
• OBJECT_TYPE:  IFS object type
• ERRNO:  The errno returned from IFS_UNLINK

I get the first two columns from the IFS_OBJECT_STATISTICS Table function, line 3, and the errno comes from IFS_UNLINK when I delete the object contained with the PATH_NAME column.

Lines 7 and 8: I then join the results contained within the temporary table, T0, with the ERRNO_INFO Table function to get the description of the returned errno.

The results are:

OBJECT PATH_NAME _TYPE ERRNO ERRNO description --------------------------------------- ------ ----- ------------------------ /home/MyDir/subfolder2 *DIR 3027 Operation not permitted. /home/MyDir/subfolder2/pdf_file_1.pdf *STMF 0 There is no error. /home/MyDir/subfolder2/csv_file_1.csv *STMF 0 There is no error. /home/MyDir/subfolder2/text_file_2.txt *STMF 0 There is no error. /home/MyDir/subfolder2/text_file_1.txt *STMF 0 There is no error.

The stream files, *STMF, were successfully deleted, but the IFS_UNLINK will not delete a directory.

I can check that the stream files were deleted by using the same statement I used before with the IFS_OBJECT_STATISTICS:

01 SELECT PATH_NAME,OBJECT_TYPE 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 03 START_PATH_NAME => '/home/MyDir/subfolder2/', 04 SUBTREE_DIRECTORIES => 'NO'))

The only result is for the directory:

PATH_NAME OBJECT_TYPE ---------------------------------------- ----------- /home/MyDir/subfolder2/ *DIR

If I was to use this in a "live" environment I would not want to be running the above statements via ACS's Run SQL Scripts, RSS. I would want to be running this in a program to make sure I run the statement the same way every time, and I capture the results from the statement(s). This is an example RPG I wrote to do this.

The program consists of a main body and two subprocedures. I will be showing each in turn and then describe what is happening in each segment. The first segment is the main body:

01 **free 02 ctl-opt dftactgrp(*no) ; 03 SetUp() ; 04 exec sql SET MYLIB.MY_PATH_NAME = '/home/MyDir/subfolder2' ; 05 Delete_IFS_Files() ; 06 *inlr = *on ;

Line 1: Of course, the RPG is modern free format!

Line 2: As I am calling subprocedures this program cannot run in the default activation group.

Line 3: This is the call to the first procedure, which will be described next.

Line 4: In the SetUp subprocedure I create a Global Variable. Here I update the Global variable with the path name I want to delete objects from.

Line 5: Then I call the subprocedure to delete the objects in the IFS path. I don't need to pass the path name as that is contained in the Global variable.

On to the first procedure, SetUp:

07 dcl-proc Setup ; 08 exec sql DROP TABLE IF EXISTS QTEMP.OUTFILE ; 09 exec sql CREATE TABLE QTEMP.OUTFILE 10 (PATH_NAME,OBJECT_TYPE,ERRNO,ERRNO_DESCRIPTION) 11 AS 12 (WITH T0(PATH_NAME,OBJECT_TYPE,ERRNO) 13 AS 14 (SELECT PATH_NAME,OBJECT_TYPE, 15 SYSTOOLS.IFS_UNLINK(PATH_NAME) 16 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 17 START_PATH_NAME => '/', 18 SUBTREE_DIRECTORIES => 'NO'))) 19 SELECT T0.*,SYSTOOLS.ERRNO_INFO(T0.ERRNO) 20 FROM T0) 21 DEFINITION ONLY ; 22 exec sql CREATE OR REPLACE VARIABLE MYLIB.MY_PATH_NAME 23 VARCHAR(256) ; 24 end-proc ;

The purpose of this subprocedure is to create all the objects that will be used later in the program. These are:

• A table to contain the output from my SQL statement that delete the IFS objects
• The global variable that contains the path name to delete files in

Line 8: If the output table exists delete it.

Lines 9 – 21: This is the statement that builds the output file. It contains the following columns:

• PATH_NAME:  IFS path name
• OBJECT_TYPE:  IFS object type
• ERRNO:  The errno returned from IFS_UNLINK
• ERRNO_DESCRIPTION:  The description for the errno returned from the ERRNO_INFO Table function

Otherwise this statement is the pretty much the same as the CTE I showed previously. The only difference is only line 17, where I have given the root directory. As I am using the statement to create the output table, I have the "DEFINTION ONLY" which means that the file will be created, and not contain any data.

Lines 22 and 23: Here I define a SQL global variable. I cannot use a RPG variable in the SQL statement that will delete the IFS objects. But I can use a global variable to pass the path name to the IFS_OBJECT_STATISTICS Table function. I have guessed that I don't have any path names of more than 256 characters. If I do, I will need to make this global variable larger.

Onto the subprocedure that actually does the deleting of IFS objects:

25 dcl-proc Delete_IFS_Files ; 26 exec sql INSERT INTO QTEMP.OUTFILE 27 (PATH_NAME,OBJECT_TYPE,ERRNO,ERRNO_DESCRIPTION) 28 WITH T0(PATH_NAME,OBJECT_TYPE,ERRNO) 29 AS 30 (SELECT PATH_NAME,OBJECT_TYPE, 31 SYSTOOLS.IFS_UNLINK(PATH_NAME) 32 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 33 START_PATH_NAME => MYLIB.MY_PATH_NAME, 34 SUBTREE_DIRECTORIES => 'NO'))) 35 SELECT T0.*,SYSTOOLS.ERRNO_INFO(T0.ERRNO) 36 FROM T0 ; 37 end-proc ;

The subprocedure contains one SQL statement. It is an Insert statement to insert data into the output file, created in SetUp. It contains the CTE I have shown before. The only difference is on line 33, where I have used the Global variable I created for the path name.

After the program has been compiled, I call it, and when it finishes use the following SQL statement to retrieve the results from the output table:

SELECT * FROM QTEMP.OUTFILE

The results are:

OBJECT PATH_NAME _TYPE ERRNO ERRNO_DESCRIPTION --------------------------------------- ------ ----- ------------------------ /home/MyDir/subfolder2 *DIR 3027 Operation not permitted. /home/MyDir/subfolder2/pdf_file_1.pdf *STMF 0 There is no error. /home/MyDir/subfolder2/csv_file_1.csv *STMF 0 There is no error. /home/MyDir/subfolder2/text_file_2.txt *STMF 0 There is no error. /home/MyDir/subfolder2/text_file_1.txt *STMF 0 There is no error.

These are two SQL features I will be using in the future, a great combination.

 

You can learn more about this from the IBM website:

• IFS_UNLINK scalar function
• ERRNO_INFO table function

 

This article was written for IBM i 7.5 TR4 and 7.4 TR10.