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.

RELATED STORIES

SYSPARTITIONSTAT

Guru: SQL Table Functions Can Do Non-function Things

It Ain’t Necessarily So