Admin Alert: Speeding Up i5/OS Access Path Rebuilds
January 27, 2010 Hey, Joe:
Our i5/OS HTTP server jobs crash after we reorganize large production files because the servers can’t open the access paths the system rebuilds after the reorganization ends. The servers can’t run until re-indexing is finished, and some of these physical files have 60 logical files attached to them. How can I speed up access path recreation so I don’t hold up processing?
Access path rebuilds can be a problem after regular file reorganizations. They can also be a problem with abnormal IPLs, where many access paths can become invalid and need to be rebuilt before they can be used again. Here’s how you can determine which access paths need rebuilding combined with a technique for speeding up your rebuilds.
Determining Which Access Paths Need Rebuilding
Use the Edit Rebuild of Access Paths (EDTRBDAP) command to view and manipulate the list of access paths your system is currently rebuilding. This list shows you the order in which the paths will be rebuilt, and it allows you to re-sequence that order so that the most critical paths are rebuilt first.
When opening EDTRBDAP, you may see that some access paths are designated with a rebuild priority of *OPN. The first time a job tries to open an access path with an *OPN priority, the system rebuilds that access path before returning data to the job. For other priorities, the system rebuilds invalid access paths according to the priority each path has in the list (1-99). If all paths have the same priority, the system rebuilds paths starting at the top of the list and working its way down to the bottom of the list.
You can re-sequence system access path rebuilds by designating a priority for each rebuild from 1-99. Access paths with the lowest numbers will be rebuilt first and paths with higher numbers will be rebuilt later. The EDTRBDAP screen displays an estimated time needed to rebuild each path as well as an elapsed time for each path that is currently being rebuilt.
Unfortunately, there are two problems with the way the system performs access path rebuilds. The system rebuilds paths at a rate not greater than one or two rebuilds per processor. If you have three processors activated on your system, for example, the system will not rebuild any more than six access paths at a time. The system rebuilds access paths inside the QDBSRVNN jobs, where NN is a number based on how many processors are active on your system. The second problem is that QDBSRVNN jobs run at a priority of 52, lower than the priority of most jobs running on the system. So the rebuilds can take longer because they run behind other production jobs.
Speeding Up the Rebuilds
Given this scenario, here’s a technique for increasing the number of access paths that can be simultaneously rebuilt. I tried this when we ran into a similar situation and it reduced a potential five hours of access path rebuilds down to two hours.
SBMJOB CMD (OPNDBF FILE (LIB_NAME/FILE_NAME) OPTION (*INP)) JOB (FILE_NAME) JOBQ (QSYS/QSYSNOMAX)
For each submitted job, the Open Database File (OPNDBF) command will attempt to open the invalid access path. Since the path’s EDTRBDAP entry is set to *OPN, the system will immediately rebuild the path at the run priority of the submitted job (which will be higher than 52). By submitting these jobs to the QSYSNOMAX job queue, several access paths can run simultaneously in the QSYSWRK subsystem, which has no maximum on the number of jobs it can simultaneously run.
By batching access path rebuilds this way, you should be able to shave time off your rebuilds and return to processing HTTP requests quicker. For your situation, where you know which access paths need rebuilding, you may be able to automate this technique by setting up a CL program to automatically submit the OPNDBF commands after the reorganization ends.