MUPIP REORG is a tool used to improve performance by defragmenting and compacting database files. REORG runs concurrently with other database activity, including updates. Competing activity generally increases the time required to perform a REORG, as well as that of the competing operations. If you use REORG concurrently with normal database access and you notice an impact on the performance of normal operation, you can lower the priority of the process performing the REORG.

Note that while REORG optimizes the GDS structure of database files, it does not handle native file system file fragmentation. In our experience, fragmentation at the native file system level is more likely than fragmentation at the GDS structure level. Always create files with appropriate allocations and extensions, on disks with large amounts of contiguous free space. Use native utilities and, depending on your procedures, MUPIP utilities to eliminate file fragmentation when database files have been extended more than a dozen times.

MUPIP REORG does not change the logical contents of the database, and can run on either the primary or secondary of a logical dual site application. In such cases, resuming REORGs in process should be part of the batch restart. For more information about running REORG on a dual site application, refer to the "Database Replication" chapter.

The format of the REORG command is:

      REO[RG] [/qualifier...] specification
      
         

The optional qualifiers are:

Entering <CTRL-C> twice in quick succession terminates REORG. A REORG terminated abnormally by operator action or error is incomplete but does not adversely affect the database structure.

/E[XCLUDE]=global-name-list

Restricts REORG from swapping blocks used by the specified globals. For example, a single MUPIP command might organize a subset of the globals in a database or region. If a second MUPIP REORG command were to SELECT the remaining globals, it would tend to disrupt the results of the first REORG by de-optimizing the previously organized blocks because no information is passed from the previous MUPIP REORG command. The EXCLUDE qualifier allows users to list the name of the previously REORGed globals, so that the MUPIP REORG bypasses the GDS blocks containing these globals.

If you enter the REORG /EXCLUDE=global-name-list command and the specified globals do not exist, REORG issues a message to the screen and continues to process any specified globals that exist. If REORG is unable to process any globals, it terminates with an error.

Arguments for this qualifier may be an individual global name, a range of global names, or a list of names and prefixes followed by the wildcard symbol. Enclose wildcards in double-quotes ("") to prevent inappropriate expansion by the shell. The caret symbol (^) in the specification of the global is optional.

The global name can be:

In the first case, REORG only excludes global ^ACN. In the second case, REORG excludes all global names in the collating sequence A7 to B7. For the third and fourth case, REORG excludes A, B, and C. In the last case, REORG excludes all globals prefixed with TMP.

By default, REORG does not EXCLUDE any globals.

In case any global appears in the argument lists of both /SELECT and /EXCLUDE, REORG terminates with an error.

/F[ILL_FACTOR]=percent-qualifier

Specifies how full you want each database block to be. This is a target number. Individual blocks may be more or less full than the fill factor.

If you have database performance issues or a high rate of database updates, you should examine the defined FILL_FACTORs. Unless the application uses entirely uniform records, which is not typical for most applications, FILL_FACTORs do not work precisely.

The FILL_FACTOR for data that is relatively static, or grows by the addition of new nodes that collate before or after pre-existing nodes, should be 100 percent. The FILL_FACTOR for data that is growing by additions to existing nodes may be chosen to leave room in the typical node for the forecast growth for some period of time. Generally, this is the time between the LOAD and first REORG, or between two REORGs. This is also true for additions of nodes that are internal to the existing collating sequence.

For example, if a global is expected to grow by five percent per month from relatively uniformly distributed updates, and REORGs are scheduled every quarter, the FILL_FACTOR for both the original LOAD and subsequent REORGs might be 20 percent ((3 months + 1 month "safety" margin) * five percent per month).

The arguments for the FILL_FACTOR qualifier must be integers from 30 to 100. These integers represent the percentage of the data block that REORG can fill. By default, the FILL_FACTOR value is 100 for maximum data density. For that case, REORG never performs a block split.

/I[NDEX_FILL_FACTOR]=percent-qualifier

Directs REORG to leave free space within index blocks for future updates. Arguments to this qualifier must be integers from 30 to 100 that represent the percentage of the index block that REORG can fill. REORG uses this number to decide whether to place more information in an index block, or create space by moving data to another block.

Under certain conditions, especially with large database block sizes, it may be possible to achieve faster throughput by using a smaller fill factor for index blocks than for data blocks. By default the INDEX_FILL_FACTOR is the value of FILL_FACTOR regardless of whether that value is explicitly specified or implicitly obtained by default.

/R[ESUME]

If you interrupt REORG, the RESUME qualifier allows you to restart the REORG operation from the point where the operation stopped. REORG stores the last key value in the database file header. If you enter the RESUME qualifier, the program retrieves the last key value, from the database file header, and restarts operations from that key.

/S[ELECT]=global-name-list

Restricts REORG operation to a subset of specified globals. By default, REORG operates on all globals in all database files identified by the current Global Directory for the process executing the MUPIP command. One of the functions performed by REORG is to logically order the globals on which it operates within the file. Unless the EXCLUDE qualifier is properly used, repeating the command with different selections in the same file wastes work and leaves only the last selection well organized.

If you enter the REORG /SELECT=global-name-list command and the specified globals do not exist, REORG issues a message to the screen and continues to process any specified globals that exist. If REORG is unable to process any globals, it terminates with an error.

Arguments for this qualifier may be an individual global name, a range of global names, or a list of names and prefixes followed by the wildcard symbol. The caret symbol (^) in the specification of the global is optional.

The global name can be:

In the first case, REORG only excludes global ^ACN. In the second case, REORG excludes all global names in the collating sequence A7 to B7. For the third and fourth case, REORG excludes A, B, and C. In the last case, REORG excludes all globals prefixed with TMP.

By default, REORG selects all globals.