fhg
Volume 10, Number 4 -- January 27, 2010

Admin Alert: Speeding Up i5/OS Access Path Rebuilds

Published: 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?

--Rob


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.

  1. Open two 5250 green-screen sessions. In one session, open the EDTRBDAP list. On the other session, bring up a command line.
  2. In the EDTRBDAP list, change the priority for any access paths that you want to rebuild immediately to *HLD (Hold) to ensure another job isn't locking the path. Then change the rebuild priority to *OPN to tell the system to rebuild the path the next time someone tries to access it.
  3. For all *OPN priority access paths, use the following Submit Job (SBMJOB) command to rebuild the access path immediately.
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.

HTH

--Joe




                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot


Sponsored By
HALCYON SOFTWARE

Do More With Less Resources

Discover how to use the pre-defined template features of System Operations Suite to monitor critical applications for ERP systems on the i OS platform, including Lawson M3, Misys' financial applications, various Infor products, SAP's Business Suite, Oracle's JD Edwards products, and popular i OS-based high availability solutions.

Learn more


Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

Help/Systems:  Event-driven job scheduling for UNIX, Linux, Windows & IBM i servers
LANSA:  Transport your apps to a new dimension with RAMP. FREE Webinar!
COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida


 

IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
The iSeries Express Web Implementer's Guide: List Price, $49.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
Can the AS/400 Survive IBM?: List Price, $49.00
Chip Wars: List Price, $29.95


 
The Four Hundred
IBM Preps Power7 Launch For February

Looks Like i 7.1 Is Coming In April

The IBM Profit Engine Keeps A-Rolling in Q4

As I See It: What Did You Do At Work Today, Daddy?

The System iWant, 2010 Edition: Midrange Boxes

Four Hundred Stuff
Tripwire Rides Log Management Gig into SIEM Business

LANSA Likes Its Chances as Wal-Mart Ramps Up GDSN Mandate

CCSS Adds MIMIX Monitoring to i/OS Systems Management Suite

JobQGenie Now Protects Job Queue Contents Through IPLs

LTO 5 Speed, Capacity Lower Than Expected

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
January 23, 2010: Volume 12, Number 04

January 16, 2010: Volume 12, Number 03

January 9, 2010: Volume 12, Number 02

January 2, 2010: Volume 12, Number 01

December 26, 2009: Volume 11, Number 52

December 19, 2009: Volume 11, Number 51

TPM at The Register
Voltaire pairs InfiniBand and Ethernet

Red Hat sponsors open source religion

VMware profits pinched in Q4

IT spending to rise in 2010, says Gartner

Platform and Calypso serve risk analysis to traders

Fujitsu puts systems chief at helm

Schwartz puts comforting arm around stricken Sun

Oracle: Mine is bigger and, um, more integrated

Liquid Computing evaporates staff

IBM buys spook-riddled DC services expert

IBM's Power7 servers imminent

China picks MIPS for super-duper super

THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
Halcyon Software


Printer Friendly Version


TABLE OF CONTENTS
Creating Yes/No Fields in SQL Queries

A Few Excel Export to CSV Tips

Admin Alert: Speeding Up i5/OS Access Path Rebuilds

Four Hundred Guru

BACK ISSUES




 
Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2010 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement