North America |  Worldwide

Hauling a Huge Stored Procedure No Problem for PENNDOT and XPEDITER

Transporting oversized or overweight vehicles on highways requires up-front route planning before permits are issued. The state of Pennsylvania's Department of Transportation has automated much of its permit processing with a web-enabled application. And helping to keep the application's DB2 Stored Procedures running smoothly is Compuware XPEDITER/TSO DB2 Stored Procedure support.

If you drive on American highways, they're a common sight: A huge truck carrying a manufactured home or building girders or a massive piece of machinery, followed by a small pickup with a sign reading "OVERSIZE LOAD." But before that truck moved out to the highway earlier in the day, the hauling company went through a permitting process with one or more state transportation departments to assure that the highway route can handle the weight, width and height of the cargo.

In Pennsylvania, haulers have quick access to the world's first automated, web-based permit system: the Automated Permit Routing Analysis System, or APRAS, provided by the Pennsylvania Department of Transportation (PENNDOT). First implemented in 1998, APRAS automates the processing of Special Hauling Permits for state highways--and it does it very quickly. Registered applicants simply log on to the application via the web and apply for a permit. Using APRAS, 80 percent of applicants get a permit in about one minute.

Considering all the information that has to be accessed and updated on a constant basis to automate the permit process, the fact that applicants can get a Special Hauling Permit from PENNDOT in one minute is impressive. PENNDOT is in charge of approximately 40,000 miles of roadway and 26,000 bridges. When a hauler applies for a permit, APRAS must automatically analyze the entire route on state-maintained roads using several databases, including its Bridge Management System (BMS) and its Roadway Management System (RMS). Other databases handle temporary road and bridge restrictions.

Because the databases are updated frequently (some are updated every night) and are accessed by a variety of programs, developers at PENNDOT use many Stored Procedures to control access to the data, preserve data integrity and improve productivity. And with more than 90 Stored Procedures to test on a regular basis, PENNDOT's developers needed a tool to save testing time.

And that's where XPEDITER/TSO and its DB2 Stored Procedures option comes in.

"One of our DB2 Stored Procedures is so large that most of our programmers used to shy away from it. Two of us were brave enough to try and test it, but it used to take us a whole day just to set it up. Now, with XPEDITER/TSO and the DB2 Stored Procedure support option, I can set up a couple of tests within a couple of hours. That's a big change." —David Canfield, Senior Analyst/Programmer, Ajilon

One Heavy Stored Procedure

"We already had XPEDITER/TSO, so we were familiar with how well that worked," says David Canfield, senior analyst/programmer from Ajilon, a managed services, consulting and specialty staffing firm working with PENNDOT. "Having to go in and test the dozens of Stored Procedures we have for APRAS without a tool like XPEDITER's option for DB2 Stored Procedures was making less sense as the procedures got bigger and more numerous."

The largest, most glaring example of why a tool like XPEDITER/TSO and its DB2 Stored Procedure support was needed came down to a single procedure: 14,600 lines of code; 34 DB2 tables; and approximately 150 selects, 33 updates and 37 inserts. "It's not something we want to mess around with frequently," says Canfield. "It's so large that most of our programmers used to shy away from it. Two of us were brave enough to try and test it, but it used to take us a whole day just to set it up. Now with XPEDITER and the Stored Procedure option, I can set up a couple of tests within a couple of hours. That's a big change."

XPEDITER/TSO's DB2 Stored Procedure support reduces debugging time by intercepting Stored Procedures from IBM OS/390 or z/OZ environments—no matter whether the procedure is called from a mainframe, distributed or web-based application—enabling developers to trap and step through procedures, trigger and user-defined functions.

Expedited Shipments, Thanks to XPEDITER

The ability to test Stored Procedures in a timely fashion has particular significance for the success of APRAS. "Without XPEDITER it would take us much longer to debug, which could inadvertently allow a truck load to go where it shouldn't," says Canfield. "Only around 20 percent of the permit requests we receive through the APRAS web site have to be handled manually. Eighty percent are issued automatically once the carrier's route is verified through APRAS within a minute or so. XPEDITER helps us maintain that high percentage."

Canfield continued on what he appreciated about using XPEDITER/TSO's DB2 Stored Procedure support. "If we're getting results in testing that we expect, that's OK. But when we're getting results we're not expecting, we use XPEDITER. I'm interested in when particular values change and jump to a specific point in the code. XPEDITER can run until a field contains a specific value, at which point it stops and I can take over my testing from there. I can also have XPEDITER track a Stored Procedure several times until I get the situation I'm testing for—such as data for a particular bridge, for example.

"Being able to quickly test Stored Procedures in APRAS has really helped us," says Canfield. "And being able to test in the native environment that a Stored Procedure runs in gives us more confidence that when we find a problem, that's exactly the problem we're looking for."

Privacy | Contact Us | Accessibility | Legal
© 2004 Compuware Corporation — All Rights Reserved