When to Reorganize Databases

There are no fixed rules about when to reorganize. There are two approaches to deciding when to reorganize, reactive and proactive, and you will probably do a mixture of both.

When you initially install the application and set up the databases, a lot of the reorganization will be done reactively, as performance and space problems manifest themselves. As you develop a history of the behavior of the application and the databases, the scheduling of reorganization should become more proactive.

Reactive Reorganizing

Reactive scheduling of reorganization is normally a result of perceived problems with the performance of the application, or problems with shortage of free space in the database.

Where there are perceived application performance problems, closely monitor what the application is doing. The initial items to examine are the average and maximum online response times and the batch run times. Are they excessive for the amount of work the application is doing? The IBM Redbook IMS Version 7 Performance Monitoring and Tuning Update describes monitoring and investigating performance of IMS applications, and subsystems in great detail.

Only after you have gone through the procedures detailed in the IBM Redbook IMS Version 7 Performance Monitoring and Tuning Update and identified potential problems with the databases should you start to look at reorganizing the database. Do not look only at the total time that the application program takes for database processing, but also look at the amount of database calls the application is processing. For example, if an online application is taking 10 seconds for database processing, but is reading 100150 database segments, then there might be little room for database tuning. However, you might want to look more closely at why, and whether, the application really needs to read all these segments. The solution to performance problems is normally an interactive process involving the database administrator, application developers, and the operating system support organization, because all three control areas that affect performance.

When you encounter problems due to shortage of space in database data sets, there is little you can do but schedule a database reorganization to increase the database size. However, you should then acquire an understanding of the data growth rate. Consult with the application developers to gain this understanding. Also, a history of the volume of the application data stored in the database over time will help you determine the potential growth rate of the data. Questions to ask are whether growth will continue at the current rate or at a different rate, and whether this data all needs to be online. Remember that there are finite architectural limits to the size of the databases that vary depending on the IMS and operating system access methods.

Proactive Reorganization

The proactive approach to scheduling database reorganization relies on regular monitoring of the databases. For more information, see "Monitoring the Database" on page 126. In addition to monitoring the databases, you should maintain a history of the monitoring information you collect, so that you can analyze this information for trends and schedule database reorganization and restructuring before any problems occur.

When you decide to make a change to a database, implement only one change at a time, if possible, and then monitor application performance before and after the change so that you can see what effect this one change had.

The main things you do when you look at the monitoring data are to try to minimize the physical I/O for each database access and optimize the free space available in the database so it is not excessive, but is sufficient for normal update access on the databases.

The physical I/O from the disk storage into the buffers in the IMS subsystem is the major component of the elapsed time for database access. You want to minimize this elapsed time by:

• Making the best use of buffers in the IMS subsystem; the more requests for database access you satisfy from the buffers, the fewer physical I/Os are necessary. This is covered in the IBM Redbook IMS Version 7 Performance Monitoring and Tuning Update.

• Minimizing the number of physical I/Os when a segment must be retrieved from disk. For example, try to place as many dependents as possible in the same block or CI as the parent, ensuring HDAM root segments are in the same block or CI as the RAP. Moving segments is where database reorganization and restructuring is used.

Monitoring the Database

Monitor your databases in order to determine when they need to be reorganized.

Database monitoring falls into two categories: monitoring program and subsystem access to the databases, and monitoring the structure, space usage, and pointer chains in the actual database data sets.

The principle tools provided by IMS that are used to monitor database access are:

• The IMS Monitor (an integral part of the control program in the DB/DC environment), used to gather details of buffer usage and database calls over a specified time period in an IMS subsystem. It gathers information for all dispatch events and places the information (in the form of IMS Monitor records) on a sequential data set. Use the IMSMON DD statement in the IMS control region JCL to specify the IMS Monitor data set. IMS adds data to this data set when you activate the IMS Monitor using the /trACE command. The IMS MTO can start and stop the Monitor to obtain snapshots of the system at any time.

• The //DFSSTAT DD statement, used in batch JCL to provide a summary of buffer usage and database calls. Because very little overhead is involved in including this statement (the details printed to the DD at region termination are accumulated by the IMS region controller whether they are output or not), it is normally worthwhile putting the //DFSSTAT DD statement in all batch jobs.

• The DB Monitor (run on a batch job), used to collect performance data during the execution of an IMS DB batch system. The DB Monitor can be active during the entire execution of an IMS batch job, or you can stop and restart it from the system console. Because overhead is involved in running the DB Monitor, you would normally turn it on only when you are investigating specific problems.

Related Reading: There are a number of products available to let you monitor the databases and the data sets in which they are stored. For more information about these products, see Chapter 26, "IBM IMS Tools," on page 443. For information about monitoring program access to the database, see the IBM Redbook IMS Version 7 Performance Monitoring and Tuning Update.

Sample Reorganization Guidelines

Although there are no fixed guidelines for when to reorganize an IMS database, the following guidelines were used successfully with a medium-sized commercial application using IMS HD databases stored in VSAM files. You might want to use these guidelines as a starting point for scheduling database reorganization and, when you have monitored the effects of the reorganization, adjust these parameters accordingly.

HD Databases in General

• Database performance has deteriorated. This can happen either because segments in a database record are stored across too many CIs or blocks, or because you are running out of free space in your database.

• There is too much physical I/O to DASD.

• The database structure has changed. For example, you should reorganize a HALDB partition after changing its boundaries or high key.

• The HDAM or PHDAM randomizer has changed.

• The HALDB Partitions Selection exit routine has changed.

• Fewer than 50% of database records have all segments making up the record (root and dependents) in the same block or CI.

• Limit your free space to less than 20%. You might want to increase this limit if you have volatile data or infrequent windows of time for reorganization.

HDAM Databases Only

• Reorganize the database if calculation of the root addressable area showed it needed to be larger, then restructure at same time. Put fewer than 75% of root segments in the root addressable area. Recalculate the root addressable area (as described in Chapter 8, "Implementing the IMS Hierarchical Database Model," on page 83).

• Fewer than 50% of root anchor points (RAPs) point to root segments in the same block or CI. That is, the RAP points to a root that has been placed in another block or CI because there is no room in this block or CI. This placement causes two I/Os, one to the RAP block, and one to the block that the root is in, instead of one I/O.

VSAM or OSAM File

Allocate the database with secondary extents. This will allow the data sets to expand as the volume of data increases.

VSAM KSDS

• When your VSAM KSDS (index) has control area (CA) splits or more than 15 CI splits.

• When your VSAM KSDS (index) has less than 20% free space (because IMS manages free space in VSAM ESDS, this applies only to a KSDS).

DEDBs

Reorganize DEDBs when there are lots of database segments in the independent overflow (IOVF) portion of the DEDB area.