Using SQL LIKE with a Subselect

This is the second part of a two part story. In the first part I described how I had downloaded a list of libraries from an IFS file to a DDL table just using SQL. In this part of the story I want to check any of job descriptions on this IBM i partitions contain one of these libraries.

In the "real world" there were 58 libraries. In this example I am only using five. To check what these libraries are called I can using the following SQL statement over the file I built in part one:

SELECT * FROM OLDLIBS

The libraries I will be using are:

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.

Searching for empty IFS folders using SQL

I was asked how to make a list of empty IFS folders using SQL. My first thought was to go to the table function IFS_OBJECT_STATISTICS. By using this table function I can retrieve a list of, for examples, just folders or the objects in a particular folder.

My thought was to do this in two steps:

1. Make a list of all the folders
2. Generate a count of the objects in each folder

New columns added to SYSCOLUMNS2 SQL View

I need to keep reminding myself to use the SYSCOLUMNS2 SQL View rather that SYSCOLUMNS as IBM tell us that it gets the results faster than the old View. IBM keeps enhancing the new View, and as part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, they added 14 new columns.

These new columns are particularly useful when working with DDS files as some of these columns deal with field attributes that are not available in DDL Tables and Views.

Prior to these additions I would use the Display Field Description command, DSPFFD, to generate an output file for the files I desired. This meant that this was always a two-step process:

1. Create the output file
2. "Read" the data in the output file

Now I can get the information I want in just one step, by accessing the data directly from the SYSCOLUMNS2 View.

User index information by SQL

As part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, we received a SQL View and Table function to use to get information about User Indexes.

I do not recall ever using a User Index, but some of the ERP I have worked with through the years had them. The IBM documentation describes them as:

A user index is an object that allows search functions for data in the index and automatically sorts data based on the value of the data...
They have an object type of *USRIDX and a maximum size of 1 terabyte. They help streamline table searching, cross-referencing, and ordering of data.

Until these TRs I would need to use APIs, ILE C, or MI instructions to access a User Index and the information contained within. Now IBM has provided us with the following:

• USER_INDEX_INFO View
• USER_INDEX_ENTRIES Table function