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.
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.
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.
Cost Justification
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.
Security
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.
Easy Updates
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."
Support
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
Linoma Software
1409 Silver Street
Ashland, NE 68003
USA
Web: www.linomasoftware.com
Email:
Tel: 800.949.4696 or 402.944.4242
Fax: 402.944.4243
Joel Klebanoff is a consultant, writer, and formerly president of Klebanoff Associates, Inc., a Toronto-based marketing communications firm. He has 30 years' experience in various IT capacities and now specializes in writing articles, white papers, and case studies for IT vendors and publications across North America. Joel is also the author of BYTE-ing Satire, a compilation of a year's worth of his columns. He holds a BS in computer science and an MBA, both from the University of Toronto.
MC Press books written by Joel Klebanoff available now on the MC Press Bookstore.
 |
||
 |
|
BYTE-ing Satire Find out the hilarious answer to the eternal question: "Is technology more hindrance than help?" List Price $14.95 Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online