Reorganize (by database relations) Physical File Mbr (RGZDBRPFM) Directory

Directory .ZIP


These items go into making a RGZDBRPFM command, a command to help with speeding up those troublesome, lengthy reorgs for those huge PFs with lots of associated LFs. Numerous APIs are involved, all of which should be found in this folder or in various other folders at this site.

Please read the @GENERAL DISCLAIMER document for any items you should be aware of if you download any of these items.


RGZDBRPFM -- Reorganize (by DBR) Physical File Mbr

This program uses a number of API command shells to examine a physical file's relations and determine how to perform a reasonably fast reorganization for it.

The program lists the database relations into a user space using the LUSDBFDBR (List to *usrspace Database File Database Relations) command. The *usrspc list is processed one element at time to check the attributes of the related logical files. The attributes determine how the access path for that LF will be handled during the actual RGZPFM.

Some LFs are left as they are during the running of this command. LFs built over multiple PFs (including join LFs), LFs that will be used as the RGZPFM keyfile and LFs that are actually SQL views or indexes are examples. Perhaps the processing was just too complex for the scope of this command or the actual file type didn't support what the command wants to do to them. For whatever reason, some LFs are simply left alone. Expand the processing for these any way you wish.

For many LFs, the attributes allow setting MAINT(*REBLD). If so, the program retrieves the LF's current settings, changes the necessary attributes, does the RGZPFM and returns the attributes to their original state at the end. For some LFs that won't allow this, e.g., UNIQUE-keyed LFs, the program does a RMVM before the RGZPFM and an ADDLFM after. All of the ADDLFMs are submitted to a batch job queue.

In order to remember original LF attribute settings, the program sends a data queue entry for each LF it changes. The entry contains the original attributes and is keyed by the LF library, file and member names. The entries are received after the RGZPFM and determine how to get all the LFs back to their original state.

By running through the *usrspc again at the end, the program knows which *dtaq entries to retrieve. The *usrspc also provides the information necessary to submit the ADDLFMs with the correct parameter values.

This has NOT been exhaustively tested. It works like a charm on files at my current work site, but I cannot possibly vouch for any given PF and its relations. If you use the CLP as provided and you do not have backups to recover from, you are taking on a significant risk.
A data area named RGZDBRPFM is required somewhere in the library list to provide a library name to RGZDBRPFM for temporary objects. This is a simple *CHAR(10) value and cannot contain <QTEMP> as its value. (Although QTEMP is perfectly valid technically, it's rather shortsighted. If the job running RGZDBRPFM fails and/or ends, e.g., it was run in batch, its QTEMP will disappear making the *RECOVER option pretty useless. QRPLOBJ is allowed, but I can't recommend it.)


CHGOBJINF -- Change Object Information (QLICOBJD):

The command definition object source for QLICOBJD. This command allows changing some attributes of an object. It is used here to change the 'User-defined Attribute'. This is used to mark the *usrspc so that a recovery situation can be detected.

LUSDBFDBR -- List Database Relations (QDBLDBR):

The command definition object source for QDBLDBR. This command accepts a DB file name, along with various qualifiers, and returns information about the files, members or record formats into a *usrspc list.

RGZDBRPFM -- Reorganize (by DBR) Physical File Mbr:

The command definition object source for RGZDBRPFM. This command accepts parameters similar to RGZPFM plus a run mode and a *jobq name.

RGZDBRPFM runs in two modes: *NORMAL and *RECOVER. *NORMAL is... well... normal; this is how it's intended to run. *RECOVER is used when a problem interrupts RGZDBRPFM in the middle of the embedded RGZPFM or while the cleanup is being done at the end, say, because of operator intervention or a power outage. *RECOVER lets RGZDBRPFM pick up where it left off and finish the job. (I added this option early on because numerous problems showed up while testing.)

A job queue can be named on the RGZDBRPFM command to submit ADDLFMs to. It defaults to QBATCH, but you should either change the default or consider naming a multi-threaded *jobq. By being a multi-threaded *jobq, multiple ADDLFMs can run concurrently. This can make a significant difference in the overall time and is one of the primary reasons for using this command.

RTVDBFDT -- Retrieve Database File Description (QDFRTVFD):

The command definition object source for QDFRTVFD. This command accepts parameters specifying a specific DB file and returns the basic portion of the File Definition Template as a data structure in a CL variable. Individual attributes can then be parsed out as needed.