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. Users can't be blamed for the numerous impromptu requests. Customer service, continuous improvement, and the maintenance of competitive advantage demand that business people regularly look at data in innovative ways to discover new insights and fulfill emerging business requirements.
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 buy Surveyor/400 from Fortra. Founded in New York City in 1925, Victaulic is headquartered in Easton, Pennsylvania. With over 2,500 employees, they have offices and manufacturing facilities across the United States and in Canada, China, Europe, Korea, and Poland.
The Requirement
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. 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 spent 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 drove 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 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 couldn’t allow to continue. To solve the problem, they 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. 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 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 the jobs of modifying existing queries and creating new ones would be more difficult than they should be. Consequently, the IT developers would still have to devote considerable time to doing these tasks themselves, which was exactly what Victaulic wanted to avoid. A number of other vendors' products were also evaluated and eliminated from consideration; they were either too expensive or lacked the flexibility needed to fulfill end users' requirements.
Due then turned his attention to Fortra. Victaulic was already using other Fortra products, such as RPG Toolbox. His satisfactory experience with those products and with the service and support he received from Fortra 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 their products, he found Surveyor/400 would fit the bill.
In addition, he discovered there was more to the product than just the features needed to address the the company's ad hoc query requirements. It included facilities that offer support for building SQL queries, including both a freeform SQL editor and an SQL Wizard that generates SQL statements. Other product functions that provide value to Victaulic include:
- Integrated File System (IFS) access functions for easy drill-downs into IFS folders, subfolders, and files
- A spool file manager that allows them to view, export, delete, hold, release, and move spool files
- An SQL generator that reverse engineers DDS files to generate SQL statements needed to create tables, indexes, and foreign keys
- An export wizard that assists in copying data to the IFS or to users' workstations in Excel, XML, CSV, and fixed-width format
- Object filters that return objects based on a set of criteria
- A graphical FTP function that transports objects over the Internet
- A file editor that provides easy editing capabilities for files and other objects, such as data areas and database files
After looking at its capabilities, Due felt that Surveyor/400 would fulfill the immediate requirements he needed to address. He wanted to test it first to be sure, so he took advantage of a free trial offer. 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," Due said.
I downloaded it off the Web and had it running in about five minutes.
Brian Due, IT Enterprise Systems Manager, Victaulic
Cost Justification
Despite the fact that Victaulic was considering Surveyor/400 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 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, 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 look at all the changes that have been made to an order. Then if 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.
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.
Brian Due, IT Enterprise Systems Manager, Victaulic
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," Due said. "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."
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.
Security
In addition to using Surveyor/400 for query support, Victaulic has eliminated all of its other file editors and now uses only the facilities in Surveyor/400 to serve those purposes. The company has 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 only four out of 50 fields. Filters can be used to limit not just which columns but also which rows users are allowed to see.
Easy Updates
Upgrading the Surveyor/400 software at Victaulic is a trivial exercise. "When Linoma [now Fortra] 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," Due said.
"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."
Support
Due has found the support that he receives from Fortra 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 [now Fortra] 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."
Try Surveyor/400 for Free
You, too, could optimize ad hoc query tasks. Learn more about how Surveyor/400 can help.