Wednesday, August 7, 2024

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:

 

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

No comments:

Post a Comment

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.