REORG looping performance UT003026

Products

• IDMS
• IDMS - Database

Issue/Introduction

Running IDMS REORG appears to be looping in RELOAD or running very slow, stays on same percentage message:

UT003026 Current Task-25 RELOAD5 SLICE-1 is 40% COMPLETE

Area contains user owned indexes. How can the performance of REORG be improved?

Environment

Release : 19.0

Resolution

To improve REORG performance on the RELOAD:

Add PAGE RESERVE to the area in the DMCL

When building user owned index the defined page reserve will be honored when storing owner records, and turned off when storing SR8 records.
This way we wont fill up the page with owner records and leave room for SR8s to be stored in the same page as the owner.
As to a suggested Reserve size, it depends on how many SR8's per owner record and how many members records per owner there are.  
The reserve should be less than 30% or it will affect the algorithm DBMS uses for updating SMP pages.  
This also depends on the size of the page and the size of the owner record, and other records in this area.  
If the page is large and record is relatively small compared to the page, maybe make it 25%, but if the record is larger compared the size of the page, you need to consider the record size when choosing a reserve percentage,

Enable the use of PREFETCH

Use a batch DMCL which has more local mode pages for each buffer, this will enable PREFETCH.

Reduce the number of keys in an SR8

The size of the SR8 also depends on the number of keys per SR8 or members per set.   Reorg, Reload, and Maintain index build SR8's at full size.  When rebuilding the size should be as close as possible to the average number of keys per set, or even less.   After the rebuild, the size can be increased to allow the SR8's to grow.  For example if there are on average 20 keys per owner, but it could be 15 - 25, defining the SR8 to have 10 keys may be better than 20 or 30, as we would waste more space with the larger size.   SR8's also have a fixed header of 32 bytes, so you don't want it to be too small or you will create too many SR8's per set.

The calculations for a system owned index may apply to a user owned index if the number of members is very large, but with user owned indexes you need to have an idea of how may members per index on average, and work from there.   

In a simple case if you had one SR8 per owner, you would want to have enough reserve space the SR8 sized so that on the same page you would store one SR8 per owner.   If the number of members per owner is too large for this, you would still want some reserved space for SR8's on each page.  Try to minimize overflow.