Midrange Stuff, OS/400 Edition

How does a growing company maintain a reasonable level of customer service when many of its business processes are rooted in manual procedures? In just 10 years of operation, SierraPine has become North America's third-largest producer of composite-panel building materials. (For you weekend fixer-uppers, that's lumber-speak for particle board and MDF, or medium density fiberboard). SierraPine manufactures building materials panels for many of the leading mills and lumber suppliers in the country, with distributors in nearly all 50 states.

Since its inception, SierraPine has worked to build a reputation as a customer-driven business. The success of SierraPine depends on repeat customers. For years, SierraPine maintained its high level of customer service through labor-intensive, hands-on order processing. With an enterprise dependence on the iSeries for virtually all of its business data, SierraPine had its hands full integrating the iSeries with virtually every aspect of the business.

To help bridge its iSeries with the order and manufacturing process and connect to a variety of devices, SierraPine turned to ASNA Visual RPG.

SierraPine had four primary requirements with its iSeries-AVR integration project. The project had to eliminate data entry redundancies. Too much time was spent key-punching data from a variety of source documents. Devices such as wireless barcode scanners and large truck scales needed to be used as input devices to the iSeries.

Data redundancies were mandatory. Because of geographical limitations, many of SierraPine's remote plants connect to the iSeries through a less-than-reliable frame relay connection. In the event that a plant became disconnected from the iSeries, the system had to be able to cache business data locally and then shoot it back to the iSeries later.

Before this project, SierraPine labored to offer customers exact details on the status of rail car shipments. A requirement of the new system was to integrate directly with railroad order status information. SierraPine's customers also work on low margins and high turnovers, and the most up-to-date shipping information is critical to these businesses.

The project was to be accomplished with in-house talent and on a deadline described as "ASAP." A consult-dependent system that its own programming team couldn't maintain and enhance was to be avoided. Tony Leach, SierraPine's senior programmer/analyst, was assigned the challenge of solving these problems. At the time, Tony was a long-time green-screen RPG programmer with virtually no experience with Windows or Web programming. Initially, Leach took a cursory look at Visual Basic, but quickly dispensed with the idea of using VB because he wasn't familiar with its language syntax and it's challenging to reliably connect it to the iSeries database.

"Coding outside my safe green screen was a huge challenge to me," Leach said. But his willingness to try something new paid off as he determined that AVR could help him solve problems without making the task too difficult. "I adapted quickly to AVR's Caviar/RPG syntax and was very productive, very quickly," he said.

To bootstrap himself, Leach took ASNA's five-day Windows development class, which taught him the basics of developing a project with AVR. Proving that a good student gains a big advantage, Leach admits he did his homework before attending the class. He also sat in the front row and asked a ton of questions. "I came back with the answers I needed," he said.

The first part of the application that Leach tackled was the capability to cache data locally. AVR uses ASNA's DataGate database technology to connect the same application, with no changes, to the iSeries, Microsoft's SQL Server, and ASNA's DataGate databases running on Windows servers and desktops. These "local" databases are DB2/400 "work alike" databases (supporting such iSeries database features as Open Query File, packed data types, and logical files and members).

Leach used AVR's local database support to build database failover in his applications. When the remote plants were connected to the iSeries, DB2/400 data was read and written to in real time. However, his application was smart enough to interrogate the status of the connection, and if it's down the application, transparent to the user, immediately switches over to the local database.

With the failover portion of his application in place, Leach moved on to integrating a variety of alternative input devices with his application. The remote plants already had some Windows PCs in place, so it was simple for Leach to integrate these PCs, using AVR, with a barcode system that SierraPine was using. "This stand-alone barcoding solution I developed runs on a local server. It collects data from the barcode scanners through AVR (and a readily available, third-party, ActiveX serial port control). Once collected, AVR is also responsible for getting this information to our iSeries," Leach said.

With the barcoding project under his belt, Leach had the confidence to integrate other devices throughout SierraPine's plants. With the serial port as the collection pipeline, he used AVR to integrate large truck scales and other hand-held barcode scanners.

"Our shop-floor guys aren't data entry experts," Leach said. So, by adding touch screens, "data entry errors are eliminated and I moved us one step closer to having true real-time data collection."

Leach's work started to pay huge dividends back at the home office by piping information back to the iSeries quickly, reliably, and without redundancy. "With our automated inputs," Leach said, "I was able to put timely information into the hands of our sales force." Previously, the sales force was hampered by slow, manual processes when determining order status and inventory.

With the data collection side of things under control, Leach turned his attention to integrating the Internet with SierraPine's iSeries. After another five-day class--this one about developing browser-based Web applications with AVR--Leach built his first Web app, a customer inquiry application. With the data now streaming directly to the iSeries, it made sense to open the data pipe to SierraPine's customers. "Our customers love the instant gratification we offer them. We can tell them everything they want to know about their order," he said.

Perhaps the most interesting thing Leach accomplished with his new Web application skills was to integrate railroad car status information with the SierraPine Web-based customer inquiry. "Tracing railroad car shipments is notoriously challenging," Leach said. "Before the Internet, the best we could do was give our customers a rough idea as to the status of their rail car shipments."

Leach worked with SierraPine railroad shippers to build a shipping-status system for railroad cars. This first-generation Web service lets the SierraPine Web site connect directly to a site that reports railroad car status, in real time, to customers.

Leach's use of point-of-origin data collection (through myriad input devices) reduced data input redundancy and, in most cases eliminated, the work required to handle data at both the plants and the home office. The speed with which this data hit the SierraPine iSeries opened the door for real-time customer Internet inquiries--including Leach's interface to the status of rail car shipments. As a bonus, the job was completed on time and under budget.

Roger Pence is the director of education and training at ASNA. He can be reached at rp@rogerpence.com.