Database Organizer for IMS for OS/390

Document ID : KB000055935
Last Modified Date : 14/02/2018
Show Technical Document Details

Ya' Gotta Read the Reports!

Help! My database reorg is taking too long! What can I do to optimize the performance of DBO and reduce my offline time?

Technical Support probably hears this question about the IMS ToolKit for OS/390 more than any other. Data Base Organizer (DBO) is a powerful tool that when used skillfully, can provide excellent results.

But how can I get these results? The key to using DBO skillfully is to read the DBORPTS section of your SYSOUT. In this section you'll find comprehensive information about your database including DBD specifications, dataset characteristics, database record statistics, details on segment pointers, length, distribution and frequency, and most importantly, database I/O statistics.

When tuning DBO's performance, your first stop should always be:

DATA BASE I/O STATISTICS.

This vital section of the reports informs you on how your unload or reload is doing in terms of physical and buffered access to the database over initial and random access threads. If you're seeing a high amount of physical activity falling under the random header, then performance is being degraded.

What is a high amount of activity? There is no standard answer to this question, but the example below is representative. Performance is impacted by a combination of factors.

DATA BASE I/O STATISTICS
SEQUENTIAL ACCESS    INITIAL      RANDOM   
-----------------    -------      ------   
      BUFFER HITS     39,343      72,504   
     PHYSICAL I/O        519       7,704   
            READS        515       7,704   
           WRITES          0           0   
           CHECKS          0       7,704   
        POSITIONS          0         642   
          ENDREQS          0           0   
       BH BUFFERS        180         120   
  DBO A/M BUFFERS        180           0   
ACTUAL A/M BUFFERS       180           0   

Some random activity is expected in any execution of DBO. In this example, with random I/O much greater than initial I/O, we have a clear indicator that performance is not optimal.

How do I make it better? Simply stated, the goal is to move as much activity as possible from the random to the initial column.

While there are many control statements available in DBO to tweak performance, three are key to processing all types of databases with two more specifically for HDAM and DEDB.

First however, some words of warning. Any tool can be abused as well as used skillfully. Caution is advised in using these parameters, and testing recommended before implementing changes in production. Over or under specifying buffers can make performance worse than running with the defaults.

These control statements impact BH Buffering on Sequential Access for all DB types:


BUF4SEQINIT	Unload/Reload
Sets values in Initial column
BUF4RANDSEQ	Unload/Reload
Sets values in Random column
RANDOMTRACKIO	Unload only
Sets random read by block/track

Commonly, adjustments need to be made on unload. DBO's default random scan of the database is by track. Sometimes, when segments are widely scattered, the default proves ineffective and a high amount of physical activity occurs. In those cases it's often best to set RANDOMTRACKIO to NO in order to set the scan to blocks, not tracks. This allows you to adjust the scan window to an exact number of blocks or CIs, using the BUF4RANDSEQ control statement. Optimal settings can vary depending on DBD and database type. A value equal to two cylinders is a good starting point. Additional testing will help you find optimal settings, and must be performed for each database.

The previous unload, when run adding RANDOMTRACKIO=NO and BUF4RANDSEQ=360 (two cylinders), dropped the EXCP count to 13 percent of its initial value and produced the following results:

SEQUENTIAL ACCESS     INITIAL      RANDOM   
  -----------------   -------      ------   
      BUFFER HITS      39,343      72,804   
     PHYSICAL I/O         518         342   
            READS         515         342   
           WRITES           0           0   
           CHECKS           0         342   
        POSITIONS           0           0   
          ENDREQS           0           0   
       BH BUFFERS         180         360   
  DBO A/M BUFFERS         180           0   
ACTUAL A/M BUFFERS        180           0   

The amount of physical I/O, reads, checks and positions decreased dramatically. However, increasing the buffers beyond 360 didn't improve on these numbers. In fact, it increased the number of EXCPs expended.

On reload all BH buffers are set by an exact block or CI count, not track, so RANDOMTRACKIO is not used.

BUF4SEQINIT resets the BH Buffers in the initial column, and is often very effective at moving buffer hits from random into initial. A starting point for changing this value is to use the scan factor on HDAM databases, or a value equal to whole cylinders if HIDAM. The exact number specified is used.

With this control statement, it's often possible to apply very high values. On occasion, as much as 10 percent of the allocated space for the database has been buffered! Again, testing is necessary to find optimal settings. Storage limitations will play a role, and S80A abends, or even CSA shortages seen if excessive values are used. The performance gained by altering this setting can be well worth the trouble of testing. In one instance, a customer reduced the unload from almost 102 hours to 36 hours using a value for BUF4SEQINIT equal to 10 cylinders, combined with changes to random buffering.

There is however a value which this setting mustn't fall below. It's found in this section of the reports:

DATA BASE RECORD STATISTICS
___________________________
  # OF DB RECORDS       :   6,118,808
  # OF SEGMENTS         :  68,221,230
  AVG # SEGMENTS/RECORD :        11.1
  AVG # BYTES/RECORD    :        51.6
  LARGEST RECORD (BYTES):      28,122
  SUGGESTED SEQ BUFFERS :          14

Using anything less than the Suggested Seq Buffers can seriously degrade performance, and on OSAM databases even cause reload to fail. For OSAM the recommended minimum is this value plus one.

These control statements are for BH Buffering on Overflow Access for HDAM or DEDB reload only:


BUF4OVRINIT
Sets values in Initial column
BUF4RANDOVR
Sets values in Random column

BUF4OVRINIT and BUF4RANDOVR control buffering on the overflow file for HDAM databases, are only used on reload and only necessary if dataspace is not being invoked. Use is as described earlier for setting initial and random buffering.

A significantly better alternative to using a DD or dynamically allocated, DASD based overflow file is to use dataspace, a product capability since V267. Up to 2GB of storage can be allocated for overflow, depending on installation settings. To access dataspace remove all overflow DDs from your reload JCL. Successful allocation will be indicated with the following message:

DBO7405I "x" DATA SPACE CREATED. SIZE = XX,XXX,XXX,XXX.

Additional detail can be found in the Performance Tuning and Considerations sections of your DBO reference manual.