SQL: SELECT with “superpowers”

We all use the SELECT statement daily to retrieve data from the database. SELECT is therefore generally considered a “passive” statement, which simply returns results without modifying anything.
But with a small addition, even an ordinary SELECT can turn into an “active” action, able to act on the database but also on the system!
The secret lies in the coupling between SELECT and the well-known QCMDEXC procedure, which allows you to execute a CL command, starting from versions 7.4 TR4 and 7.3 TR10 it is also supplied as a scalar function. QCMDEXC can therefore be invoked by any SQL query.

Let’s see a practical example right away: the mass rename of a bunch of IFS files.
In applications it is common to have functions that create IFS files with a certain naming, then send them to remote servers and then rename them so that they are no longer considered in the next processing cycle. In this example we have 5 .csv files in our IFS folder:

Suppose we have sent them all together to the remote server (for example via FTP with an MPUT command), now we want to rename them all by changing the extension from “.csv” to “.ok” in order to identify the processed files.
Thanks to SQL functions and services, we are able to rename all files in one shot, changing their extension.
Here is the instruction:

---- Mass rename of IFS files
-- 1: retrieve IFS file list
WITH a AS (
SELECT path_name, 
 UPPER(REPLACE(path_name, '/home/MDUCA/FAQ400_15/', '')) filename   
FROM TABLE(ifs_object_statistics(
START_PATH_NAME => '/home/MDUCA/FAQ400_15',
SUBTREE_DIRECTORIES => 'NO' , 
OBJECT_TYPE_LIST =>  '*ALLSTMF'
))
WHERE  UPPER(path_name) LIKE '%/FILE%.CSV'
)
-- 2: build CL commands
, b AS (
SELECT 
'REN OBJ(''' CONCAT TRIM(path_name) CONCAT ''') NEWOBJ(''' 
CONCAT REPLACE(filename, '.CSV', '.OK') CONCAT
''')' cmd
FROM   a
)
-- 3: execute commands
SELECT b.*, qsys2.qcmdexc(cmd)
FROM   b
;

Let’s review it in detail: in the first CTE, with the IFS_OBJECT_STATISTICS service we fetch the list of files in the /home/MDUCA/FAQ400_15 folder; full path (path_name) and the single file name are retrieved (file name is converted to uppercase so that it is case insensitive). The list filters only files with names starting with “FILE” and with “.csv” extension.

With the second CTE, the file list is read and the object rename command (REN) is composed; this is the command that will be fed to QCMDEXC procedure. In this step, the worst effort is, as always, fighting with the quotes…

Finally, by reading with a SELECT the list of previously composed REN commands and passing them as a parameter to QCMDEXC function, the system commands will be executed. The QCMDEXC function returns “1” if the command was successful, otherwise it will return the value “-1”.

And here are our files renamed as desired.

The winning couple between SELECT and QCMDEXC can turn into a useful and powerful tool for massively executing commands against a list of elements, avoiding having to do it one by one manually. Think, for example, of recompiling all sources of a certain library, moving unused objects to other libraries, and so on.
DB2 services and functions prove again to be highly effective tools for simplifying developers’ lives. Knowing them and applying them correctly allows having applications that are clear, easy-to-maintain and above all… modern !

DOCUMENTATION:
Table function IFS_OBJECT_STATISTICS
https://www.ibm.com/docs/en/i/7.4?topic=services-ifs-object-statistics-table-function

Scalar function QCMDEXC
https://www.ibm.com/docs/en/i/7.4?topic=services-qcmdexc-scalar-function