Override to Specific Member

IBM is working on easier ways to issue OVRDBF commands that impact SQL statements. They recently delivered one that will specify the "number of records" for buffering via the OVERRIDE_TABLE() function. Another that has not yet be implemented but is sorely needed, is overriding to a specific member in a file. Granted Member processing is "weird" except for source files, nonetheless, it is still widely used.

You might be thinking, why not use QCMDEXC with the OVRDBF command? And that's a valid question. The only issue is when using SQL Server Mode, a call to the QCMDEXC API will have no impact as the actual SQL statements as they are being processed by a different job. You can however, use the SQL QCMDEXC() procedure instead of the QCMDEXC API. This will perform the OVRDBF in your job if running in non-server mode, and in the SQL Server Job, when running in, well, Server Mode. The SQL version of QCMDEXC is evoked as follows:

EXEC SQL QSYS2.QCMDEXC( :cmdString );

There is an option 2nd parameter for command string length, but as mentioned, it is now optional.

To me, building those "OVRDBF FILE(MYLIB/MYFILE) TOMBR(ABCDEFG)" command strings are a pain. I don't like having them not work because I forget to include OVRSCOPE(*JOB) in the command string.

So I wrote an OVRDBF_MBR() "stored" Procedure. There are actually 3 variations that support various levels of laziness. If you notice within OVRDBF_MBR I eventually do call the QSYS2.QCMDEXC SQL function.

Here is the SQL Source Code for the 3 variations of the SQL OVRDBF_MBR Procedure that I wrote.

1. EXEC SQL CALL COZQRYF.OVRDBF_MBR( file, toMbr );
2. EXEC SQL CALL COZQRYF.OVRDBF_MBR( file, toFile, toMbr );
3. EXEC SQL CALL COZQRYF.OVRDBF_MBR( file, library, toFile, toMbr );

 CREATE OR REPLACE PROCEDURE OVRDBF_MBR(
             IN dbFile  VARCHAR(10),
             IN toMbr   VARCHAR(10) )
       LANGUAGE SQL
       SPECIFIC OVRDBF_FILE_MBR
       NOT DETERMINISTIC
       SET OPTION DBGVIEW=*SOURCE
 a: BEGIN
      call OVRDBF_MBR(dbFile, '*', '*FILE', toMbr);
  end;

 CREATE OR REPLACE PROCEDURE OVRDBF_MBR(
             IN dbFile  VARCHAR(10),
             IN toFile   VARCHAR(21),
             IN toMbr   VARCHAR(10) )
       LANGUAGE SQL
       SPECIFIC OVRDBF_FILE_LIB_MBR
       NOT DETERMINISTIC
       SET OPTION DBGVIEW=*SOURCE
 b: BEGIN
      call OVRDBF_MBR(dbFile, '*', toFile, toMbr);
  end;

 CREATE OR REPLACE PROCEDURE OVRDBF_MBR(
             IN dbTable VARCHAR(10),
             IN tolib   VARCHAR(10),
             IN toFile  VARCHAR(21),
             IN toMbr   VARCHAR(10) )
       LANGUAGE SQL
       SPECIFIC OVRDBF
       NOT DETERMINISTIC
       SET OPTION DBGVIEW=*SOURCE
 c: BEGIN
      DECLARE ovrdbf VARCHAR(256);
      DECLARE cmdLen INTEGER;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
        RETURN;
      END;

    if (toFile = '*FILE') THEN
       SET toFile = dbFile;
    end if;

    if (toLib  = '' or toLib = '*') THEN
    SET ovrdbf = 'OVRDBF  FILE(' || rtrim(dbTable) || ') ' ||
               'TOFILE(' || rtrim(toFile) || ') ' ||
               'MBR(' || toMbr || ') OVRSCOPE(*JOB)';
    else
    SET ovrdbf = 'OVRDBF  FILE(' || rtrim(dbTable) || ') ' ||
               'TOFILE(' || rtrim(toLib) || '/' || rtrim(toFile) || ') ' ||
               'MBR(' || toMbr || ') OVRSCOPE(*JOB)';
    end if;
    SET cmdLen = length(ovrdbf);
    CALL QSYS2.QCMDEXC( ovrdbf, cmdlen );
 END;

The reason for 3 versions of the same procedure is to provide various parameter lists. SQL supports function/procedure overloading, but requires a bit a creative declaration to make that happen. Recent enhancements to parameters includes DEFAULT value support; this will help the excessive instances, but obviously n-2 support isn't always there with enhancements so we continue to use the most compatible legacy syntax.

In all 3 syntaxes, the OVRSCOPE(*JOB) is included in the OVRDBF command that is generated and run.

exec SQL call QUSRSYS.ovrdbf_mbr('CUSTOMER', 'PRODDATA', 'CUSTMAST','JUNE');
exec SQL call QUSRSYS.ovrdbf_mbr('CUSTOMER', 'CUSTMAST','JUNE');
exec SQL call QUSRSYS.ovrdbf_mbr('CUSTOMER', 'JUNE');

There is an alternate special "4th syntax" that is really just a variation on the 2nd syntax. If you specify qualified TOFILE, the override is still applied properly. For example:

 exec SQL call QUSRSYS.ovrdbf_mbr('CUSTOMER', 'PRODDATA/CUSTMAST','JUNE');

Question: Does the procedure support host variables? Yes it does, as illustrated below:

 exec SQL call QUSRSYS.ovrdbf_mbr('CUSTOMER', :toMbr );

Question: But Bob, what if I only want to override from one table to another, and not specify the member name at all?

Answer: That's a different scenario and doesn't really apply here. However, if you know the OVRDBF command syntax, you know that you can specify MBR(*FIRST). Therefore to simply override to a different file, and ignore the member parameter, call the OVRDBF_MBR procedure as follows:

 exec SQL call QUSRSYS.ovrdbf_mbr('CUSTMAST', '*LIBL' , 'CM01PF', '*FIRST');

This would override the CUSTMAST file reference to file CM01PF and set the member to *FIRST, which would be used even if MBR wasn't specified. A more appropriate syntax would be to enhance the OVRDBF_MBR procedure to check for a blank member name, and if none specified, then simply avoid inserting the MBR parameter into the OVRDBF command itself. Perhaps I'll work on that.