02a - SQL (EN)

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

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

View Comments

Recent Posts

Managing Locked Display Files on IBM i: An Effective Solution

We are pleased to receive and share this "tip & trick" from Patrick Rizzi, which introduces a technique that allows…

2 weeks ago

Monitoring QSYSOPR Messages: SQL to Retrieve Messages and Replies

I take inspiration from a response by Michael Mayer on the Midrange.com mailing lists to someone who asked how to…

2 weeks ago

Why ERP is the Key to Success for Modern Businesses

Businesses are increasingly seeking tools to enhance efficiency, collaboration, and resource management. Enterprise Resource Planning (ERP) systems provide a comprehensive…

2 months ago

ACS Access Client Solution 1.1.9.5

Early April saw the release of the "Spring Version" of ACS Access Client Solution, version 1.1.9.5 Interesting new features especially…

7 months ago

Tim Rowe and Scott Forstie for CEC 2024 – Milan

If the packed agenda of sessions at Common Europe Congress 2024, June 3-6 Milan, wasn't enough for you, here's another…

7 months ago

Code for IBM i 2.10.0 – Debug IBM i App with Visual Studio Code

Debugging functions with Visual Studio Code have been available for some time but this new version 2.10.0 simplifies the handling…

7 months ago