Database Organizer for IMS performance optimization
Article ID: 55935
Updated On:
Products
Issue/Introduction
What can be done to optimize the performance of Database Organizer (DBO) and reduce my offline time?
Resolution
The key to using DBO skillfully is to read the DBORPTS section of your SYSOUT. In this section you will 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.
Feedback
