Guru: SQL Functions Can Do Non-function Things
January 27, 2020 Ted Holt
We all know that the SQL SELECT statement only retrieves data, right? If you want to modify data, you have to use INSERT, UPDATE, DELETE, or MERGE, correct? Well, to quote the great George Gershwin, It Ain’t Necessarily So. You can modify data from a SELECT statement, and maybe sometimes you should. Here’s how it’s done.
I’ll illustrate with a SELECT statement that will run the Reorganize Physical File (RGZPFM) command over physical files that have at least 10 percent deleted records. I could do this with plain ol’ CL, of course, and that’s probably the approach I would take if I had to do this task for real. But I need a simple example that you can run on your system, so this fits the bill.
First, I need the pertinent information for all the physical file members in a library. The Display File Description (DSPFD) command can do that.
DSPFD FILE(SOMELIB/*ALL) TYPE(*MBRLIST) + OUTPUT(*OUTFILE) OUTFILE(QTEMP/FD)
If you prefer to use SQL to retrieve this data, take a look at the SYSPARTITIONSTAT catalog view.
Second, I need an SQL function that can reorganize a physical file member.
create or replace function mylib.ReorgPF (p_Library varchar(10), p_File varchar(10), p_Member varchar(10)) returns integer modifies sql data returns null on null input begin declare v_Command varchar(80); declare exit handler for sqlexception begin return 0; end; set v_command = 'RGZPFM FILE(' concat trim(p_Library) concat '/' concat p_File concat ') MBR(' concat p_Member concat ')'; call qsys2.qcmdexc(v_Command); return 0; end;
Does something strike you as odd? It should. A function is a routine that, given zero or more values, returns a value. This function hardly lives up to that ideal. What we have here is a stored procedure masquerading as a function.
Notice the MODIFIES SQL DATA clause. If you omit that clause, the system nips the reorg in the proverbial bud.
Last, I need a query that selects the members that have at least 10% deleted records. (Specifying 9.5 instead of 10 is one way to round.)
select MLLIB,MLFILE,MLNAME,MLNRCD,MLNDTR, (MLNDTR/(MLNRCD + MLNDTR)), ReorgPF(f.MLLIB,f.MLFILE,f.MLNAME) from qtemp.fd as f where MLNDTR > 0 and (MLNDTR/(MLNRCD + MLNDTR)) >= .095;
Notice the last column. It executes the function for each selected file member. Here’s my test data.
In this case, files MFGORDDTL and VENDORS were reorganized.
The purist in me says that a function should never modify data. The realist says not to use the words never and always. As long as everything is open and above-board, I don’t see a problem using this type of function.