Ad hoc queries and reports are the bane of many IT departments. The need to churn out frequent, one-off, "must have" data queries for users can be a significant drain on developers' time, time that is desperately needed for other projects. Users can't be blamed for the numerous impromptu requests. Customer service, continuous improvement, and the maintenance of competitive advantage demands that business people regularly look at data in innovative ways to discover new insights buried within it and to fulfill emerging business requirements that, for whatever reason, cannot be anticipated. Meeting this need without putting an undue burden on the IT department was a challenge that Victaulic Company was determined to overcome, which led it to evaluate and then buy Surveyor/400 from Linoma Software.
Founded in New York City in 1925, Victaulic operates from a world headquarters in Easton, Pennsylvania, as well as from offices and manufacturing facilities in various locations across the United States and in Canada, China, Europe, Korea, and Poland. Its 2,500 employees have helped to make Victaulic the world leader in grooved and plain-end mechanical pipe joining systems.
Victaulic runs its business with the help of three iSeries servers. It uses a Model 520 server with two LPARs for development. A second Model 520 server runs the company's data warehouse. A four-processor Model 825 server, which is due to be upgraded soon, runs the company's production applications, including the BPCS ERP suite and a number of applications that were developed in-house. The DB2 databases attached to the production server hold approximately 850 gigabytes of data, including information critical to Victaulic's business, such as parts inventory and customer data.
As is the case at all companies, timely access to information in a form that is easily usable by people throughout the company is critical to Victaulic's success. Consequently, Victaulic's IT department was spending considerable time creating ad hoc queries and downloading iSeries data for users who had neither the expertise nor the tools necessary to do those tasks themselves. Already a major drain on IT resources, the problem promised to only get worse because the company was growing rapidly—the combination of more business and more employees was driving a need for more access to data.
The growing ad hoc query demands meant that the IT department had less time to develop and maintain the company's vital systems. The result was that the backlog of ad hoc queries and regular application development and maintenance began to lengthen, to the frustration of both users and IT. Clearly, this was a trend that Victaulic could not allow to continue. To solve the problem, it wanted a tool that would allow users to download information themselves, without jeopardizing the security and integrity of the data.
The workload concerns were particularly acute because Victaulic's IT department runs a tight ship. The six people in the department are responsible for supporting about 1,000 users. These six people have sole responsibility for doing all of the necessary RPG, Visual Basic, and Web server programming, including all of the enterprise programming on the more than 30 servers—both iSeries and other platforms—that the company has.
Finding a Solution
Brian Due, IT enterprise systems manager at Victaulic, began his search for a solution with IBM's Client Access and Operations Navigator tools. These products met Victaulic's minimum requirements—they allowed users to execute downloads from iSeries—but both the end users and the IT department at Victaulic felt that, with these products, the jobs of modifying existing queries and creating new ones would be more difficult than they should be. Consequently, in many cases, the IT developers would still have to devote considerable time to doing these tasks themselves, which was exactly what Victaulic was trying to avoid.
A number of other vendors' products were also evaluated and eliminated from consideration because they were either too expensive or they lacked the flexibility needed to fulfill end users' requirements.
Due then turned his attention to Linoma Software. Victaulic was already using other Linoma products such as RPG Toolbox. His satisfactory experience with those products and with the service and support he received from Linoma led Due to feel comfortable with the company as a potential supplier of a solution to meet his query and reporting needs.
When Due studied Linoma's offerings, he found that its Surveyor/400 software would fit the bill. In addition, he discovered that there was more to the product than just the features needed to address the company's ad hoc query requirements, such as facilities that offer support for building SQL queries, including both a freeform SQL editor and an SQL Wizard that generates SQL statements without the need to know SQL syntax. Among the other product functions that provide value to Victaulic are the following:
- Integrated File System (IFS) access functions allow for easy drill-downs into IFS folders, subfolders, and files.
- A spool file manager provides facilities for viewing, exporting, deleting, holding, releasing, and moving spool files.
- An SQL generator reverse engineers DDS files to generate the necessary SQL statements to create tables, describe fields, and create indexes and foreign keys.
- An export wizard assists in copying data to the IFS or to users' workstations in Excel, XML, fixed width, custom delimited, HTML, CSV, or TAB-delimited format.
- Object filters return objects based on a set of criteria.
- A graphical FTP function transports objects over the Internet.
- A file editor provides easy editing capabilities for files and other objects, such as data areas and database files.
- "Layouts" define file views to be used in Surveyor/400's File Editor. Layouts can be saved, distributed to users, and then reused by them as needed. Each layout specifies what fields are to be captured in the views, the names that are to be assigned to those fields, and the order in which the fields are to appear. The layout also defines data filter conditions and sort criteria.
After looking at its capabilities, Due felt that Surveyor/400 would fulfill the immediate requirements he needed to address, but he wanted to test it first to be sure, so he took advantage of a free trial offer from Linoma. Due reported that installation of Surveyor/400 was fast and easy. "I downloaded it off the Web and had it running in about five minutes," said Due.
Ironically, despite the fact that Victaulic was considering Surveyor/400 primarily because of its ability to simplify and accelerate the users' query generation process, that wasn't the feature that Due first tested. At the time, Victaulic was close to buying new disk drives because the existing ones were nearing capacity. Therefore, Due first used Surveyor/400 to drill down into the libraries on the iSeries server and examine the objects stored there. He found a number of files that the company no longer required and, by deleting them, was able to free up enough space to avoid the need to buy new disks. Those savings alone were enough to more than cover the cost of the software, which made it very easy for Due to sell his boss on the idea of buying Surveyor/400.
After acquiring Surveyor/400, Victaulic's IT department began applying it to the task of tackling the ad hoc query backlog. Four or five "super users," who have significant query and reporting needs and strong computer skills, use Surveyor/400 to create queries for their own use. For other users, a developer in the IT department employs Surveyor/400 to quickly generate an SQL procedure that is emailed to the user. In the email, the IT person tells the user how to run the query and also highlights those SQL items, such as the customer number in a SELECT clause, that the user can change on his or her own to create derivative queries. The user then runs the query and can easily download the results into an Excel spreadsheet, where the user can further analyze the data, slicing and dicing to meet specific requirements, without the need for further assistance from IT.
The capabilities of Surveyor/400 have also allowed Victaulic to better manage its business. For example, the company does considerable auditing of its files. Audit logging over about 100 critical files captures transaction data, timestamps, and IDs of the users who make changes. Victaulic uses the Surveyor/400 layout feature, which provides a customized editor view to look at the audit files. Using this feature, users can, for instance, look at all the changes that have been made to an order. Then if, for example, the order includes an item that was sold for a non-standard price, a manager can easily see who changed the price on the order and when that change was made.
The efficiencies afforded by Surveyor/400 have made Victaulic's IT department more productive. Victaulic's programmers now tackle requests for new queries either by using time available between projects or by taking about 15 minutes to do those tasks before they go home at the end of the day. As a result, the department is now able to work through an average of between 40 and 50 help tickets a week, three-quarters of which are SQL or ad hoc report requests. Last year, Due alone was able to generate 133 queries for users and another programmer completed 200, while still being able to do their other work. And, as Due noted, that number does not include all of the ad hoc queries that were created. Only those queries of a more permanent nature were counted. Furthermore, "super users" create an untold number of queries on their own, removing the burden that would otherwise have fallen on the IT department.
"I know that if I didn't have Surveyor/400, I'd have to hire one person to do nothing but ad hoc programming for queries," stated Due. "In fact, before Surveyor/400 we did have one person dedicated to those tasks. We've since trained that person and brought them into the regular development staff."
Victaulic is using Surveyor/400 to serve purposes beyond the original ad hoc query requirements. For example, it uses the spool file management features to turn print files into PDF or text files that can be emailed to others in the company or to customers, thereby reducing printing costs and labor requirements. The IT department is also using Surveyor/400 as a quick way to access, edit, and move IFS objects. Due also appreciates that Surveyor/400 allows him to view all the information on a file with just a couple clicks of the mouse. Instead of typing several commands, he can quickly see the file properties, fields, database relations, members, and more just by clicking on the appropriate tab.
In addition to using Surveyor/400 for query support, Victaulic has eliminated all of its other file editors and, instead, now uses only the facilities in Surveyor/400 to serve those purposes. The company has also given this capability to users, but doing so without the necessary controls could have jeopardized the integrity of the company's data. To avoid this problem, Victaulic uses the security features in Surveyor/400 to provide developers with editing access but restrict users to view access only. Likewise, developers are authorized to use Surveyor/400's data and file management functions, such as its mass file update capabilities and file property interrogation features, but Surveyor/400's security features are used to deny access to use those functions for anyone who shouldn't have it.
Surveyor/400 also protects Victaulic's confidential information by limiting what users can see. For example, users can be given an editor that allows them to look at, for instance, only four out of 50 fields. Filters can also be used to limit not just which columns, but also which rows users are allowed to see.
Upgrading the Surveyor/400 software at Victaulic is a trivial exercise. "When Linoma comes out with new releases or point releases, it's extremely easy for me because you can set it up so that Surveyor/400 looks at the iSeries Integrated File System, where it keeps a copy of the code, and then the first time you log on in the day, it looks for an update and, if it finds one, automatically pushes any upgrades down," explained Due. "What that means for me is that I can download an upgrade off the Web, put the update on a PC, update the iSeries development server, and then test it all out. The first time I did this, I notified all of the users and showed them what needed to be set to ensure that their copy would upgrade automatically. A couple of days later, I upgraded the production iSeries server. The first time users signed on the next day, the upgrade was automatically pushed out.
"That helps to lower the cost of ownership because I have the software loaded on 250 PCs scattered around the globe. The automatic update process makes it extremely easy to get everybody in synch."
Due has found the support that he receives from Linoma to be another benefit of the solution. "They're extremely responsive to their user group," said Due. "If you have a problem, these people work with you. I had one the other day. Linoma emailed me a fix the same day, and we were immediately up and going. I can't recall a time when I haven't had very quick turnaround. In fact, I've suggested cosmetic things and, on the very next point release, I saw that my suggestion had been included."
Victaulic has been using Surveyor/400 for more than four years now and is, according to Due, a very satisfied customer. "It's not very often a tool can be considered mission-critical to a business, but we've come to rely on Surveyor/400 for so many things that we would be lost if we didn't have it available to us. In fact, it's hard for me to quantify the benefits we receive because it's become part of our daily life. For example, what used to require 20 green-screens we now do with just one click. How do you quantify that? But I know that we'd have to hire more people without it."
Joel Klebanoff is a consultant, a writer, and president of Klebanoff Associates, Inc., a Toronto, Canada-based marketing communications firm. Joel has 25 years experience working in IT, first as a programmer/analyst and then as a marketer. He holds a Bachelor of Science in computer science and an MBA, both from the University of Toronto. Contact Joel at
1409 Silver Street
Ashland, NE 68003
Tel: 800.949.4696 or 402.944.4242