Appendix C: CLRPFM for Database Monitor files

The CLRPFM command on the IBM i does not delete records individually, but instead clears the whole file member in a single step. Because of this, the function cannot be performed on a file that has a DELETE trigger attached to it. Since Database Monitor inherently needs to track each time a record is deleted from a file, this presents a dilemma. Normally, a file that you would like to use Database Monitor on would never be cleared, but individual work members might be cleared if they are used for holding temporary records. So, for such files that already have application code that uses the CLRPFM command, a workaround to the problem must be performed.

Option 1: Modify those CL programs which clear the file in question, to utilize the Database Monitor DTCLRPFM command.

This is the preferred approach if you only have a small number of files and occurrences of CLRPFM in your application code. By simply replacing all occurrences of CLRPFM with DTCLRPFM in your application code (only for those files you are adding to Database Monitor), the problem is effectively solved. See below for a description of the DTCLRPFM command, showing exactly what it does. Note that in order to call the DTCLRPFM command and for it to operate properly, either the Database Monitor installation library must be in your library list or the following objects must be duplicated into a standard system library that's always in the list when the command is used: DTCLRPFM (CMD), IDTCLRPFMC (CL), IDTCLRPFM (SQLRPGLE).

Option 2: Rename the standard IBM command, and place the Database Monitor CLRPFM member command in QSYS.

This is the easiest solution in terms of effort required on your part. Database Monitor ships with a command called CLRPFM which duplicates the functionality of CLRPFM but recognizes when a file with a DELETE trigger is being cleared and, for such files only, deletes the records in the file one at a time using an SQL DELETE. This allows the DELETE trigger to remain on the file during the deletion process. After the clear is performed using SQL, the file is then reorganized using RGZPFM, to simulate the state a file is in after a true CLRPFM. For this approach, the following steps must be taken:

  • Rename object QSYS/CLRPFM to QSYS/IBMCLRPFM
  • Copy the objects CLRPFM (CMD), IDTCLRPFMC (CL), and IDTCLRPFM (SQLRPGLE) into a system library, such as QUSRSYS.

Note that with this approach, care must be taken when upgrading to a new version of the operating system that the new copy of CLRPFM is also renamed after the upgrade.

Option 3: Do not use the Trigger function to capture deletes

Database Monitor can be configured to capture the delete of a record through the use of the journal. For this to work, both before and after image of the record has to be captured. You can simply configure DT to use the DTJOURNAL functionality and the system will take care of the rest. The only limitation of this solution is that a contemporaneous signature can not be captured for deletes being monitored through journaling.

What the Database Monitor "clear" commands do

The DTCLRPFM and CLRPFM commands shipped with Database Monitor perform the exact same functions, with one minor difference, which is that DTCLRPFM actually invokes the operating system version of CLRPFM first, so that those files without triggers may be cleared as normal. In the case of Database Monitor's CLRPFM, it is assumed that the system version of CLRPFM has been renamed to the name of IBMCLRPFM, so this is the first command attempted when the command runs. The rest of the operation of these two programs is identical. Notice in the flow of the commands, which follows, the program invoking these commands should not have to know that the original IBM-supplied CLRPFM was not called. Any errors that are encountered via the clear are passed back to the calling program just as CLRPFM would have passed them. In fact, for non-triggered files, the actual messages received are simply forwarded back to the calling program just as CLRPFM sent them.

Flow of DTCLRPFM (and Database Monitor's version of CLRPFM)

  • Invoke the operating system command CLRPFM to clear the file
  • If no errors, simply exit
  • If errors other than DELETE trigger preventing the clear, send these messages to the calling program
  • If error CPF3157 (DELETE trigger prevented the clear)
    • Use SQL to delete all of the records in the member
    • Reorganize the member using RGZPFM