Online Index DEFRAG Utility
Article ID: 55459
Updated On:
Products
Issue/Introduction
What should you do when you want to defrag or compress an Advantage CA-Datacom/DB index (IXX)?
Environment
Resolution
Prior to r11 SP2, the best way to accomplish this was to initialize the index and run a base level RETIX. But this has the obvious disadvantage that the base must be closed to all users during this process.
Starting with the r11 SP2 of CA-Datacom/DB, you can run a DBUTLTY index DEFRAG job. This utility performs its activities through the Multi User Facility (MUF) which allows the utility to run while leaving the base open and available for normal use.
To run this utility, execute DBUTLTY with the parameter statement "DEFRAG DBID= nnnn". This DBUTLTY connects to the MUF and defragments the DXX level (lower level) of the IXX. It does so by combining any two or more logically adjacent blocks that will fit in one block. It processes all KEYIDs for a database including special KEYIDs, such as the cluster and free space keys.
While reading through the IXX for the defragmentation process, the utilty also reclaims any index blocks that were logically deleted but had not yet been released back to the free block pool. During normal index processing, all entries in an index block may be deleted, however that index block can not be freed until all the data changes associated with the deleted index entries are committed. When the commit occurs, the index block is considered logically deleted. In normal processing, the next task passing through the index sees the logically deleted index block and releases it back to the free block pool. In certain cases, these logically deleted blocks may remain "in-use" on the index for a period of time. By running the index DEFRAG periodically, the amount of these logically deleted index blocks will be kept to an absolute minimum.
The DEFRAG utility produces a report that tells you how many blocks were combined and how many blocks were reclaimed as a result of the process and the percentage of blocks returned to the free block pool for each KEYID.
All this is done with minimal overhead to applications that are simultaneously processing against the same index. Because of this minimal overhead, it is easy for you to schedule the defrag to run periodically to keep in-use index space compressed. This can result in reduced index DASD requirements and the index blocks that are in-use will contain more index entries per block. Typically, the more index entries per block, the greater potential for index buffer reuse, which improves overall performance.
If you want to stop the DEFRAG process for any reason, simply issue an REQABORT command using the request sequence number found through a COMM STATUS request. Aborting a DEFRAG request is considered a non-destructive activity, because the work done by the DEFRAG up to that point will remain intact and the index will remain fully usable. The only drawback to aborting an index DEFRAG is that the space reclaimed report at the end of the task will not be produced.
A few advanced options and restrictions exist for the DEFRAG utility. You can find these in the Advantage CA-Datacom/DB Database DBUTLTY Reference Guide. But in general, running a simple execution of the DEFRAG utility is an effective way to keep your indexes at a minimal size with improved performance (buffer reuse).
Additional Information
Optional) Specify the X_DEFRAG_DELAY_QUEUE n during the Multi-User Facility Startup or through the console. Use this command to prevent index queue processing from possibly interfering with processing of normal transaction work....
More information:
https://docops.ca.com/ca-datacom/15-0/en/administrating/ca-datacom-db-database-and-system-administration/using-the-multi-user-facility/modifying-muf-startup-options/x_defrag_delay_queue
Feedback
