Copying data from IFS file using SQL, real life example

This is the first of a two part story. I had found a number of libraries on an IBM i partition from the 2000s and 2010s for applications that had been used, but had been replaced by the main ERP application. These libraries were just wasting disk space, as during the conversion to the ERP all of the historical information had been copied from them to the ERP. I wanted to delete these libraries.

The list of these libraries had been circulated to all of the interested parties to confirm that no-one was using any of them. I copied the list from the email to Notepad, and saved as the file name: old_libraries.txt.

I uploaded the text file to my IFS folder using ACS's "Integrated File System" tool.

I wanted to check that all the data was copied successfully. To do that I use the IFS_READ SQL table function:

SELECT * FROM TABLE(QSYS2.IFS_READ('/home/SIMON/old_libraries.txt'))

In the "real world" there were 58 libraries. In this example I only have five:

LINE_NUMBER LINE ----------- ---------- 1 LIB1 2 LIB2 3 LIB3 4 LIB4 5 LIB5

I am going to need a DDS file or DDL table to copy the contents of the text file into. I created a DDL table with only one column, for the library name:

CREATE TABLE MYLIB.OLDLIBS (LIBRARY VARCHAR(10))

I could use the Copy from Stream File command, CPYFRMSTMF, to copy the data from the text file in the IFS to the table. As there is only one column of data I decided to use the IFS_READ table function to do it.

All it would take would be a SQL Insert statement:

01 INSERT INTO OLDLIBS 02 (SELECT LINE FROM TABLE(QSYS2.IFS_READ('/home/SIMON/old_libraries.txt')))

Line 2: I only want the LINE column from the table function, as it contains the names of the libraries.

The statement executed in a blink of an eye.

To check that the data from the text file was inserted into the table I would use the following SQL statement:

SELECT * FROM OLDLIBS

The results show that all of the library's names were copied:

LIBRARY ------- LIB1 LIB2 LIB3 LIB4 LIB5

In the second part of this story I will show how I used this table to check if any of these libraries are used in any job descriptions.

 

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