At The Pantry, Incorporated MIS Director Mike Burke was looking for a better way to integrate the company's SQL Server databases with the IBM iSeries. The Pantry is a large, independently owned convenience chain that operates over 1,300 stores in ten states operating under Kangaroo Express and other banners. Some of The Pantry's mission-critical applications reside on their IBM iSeries computer, but the day-to-day accumulation of information and data from The Pantry's many stores is managed on a UNIX-based system. In addition, a SQL Database is used to maintain the individual store information for the company Intranet. Users rely on iSeries reports to populate spreadsheets for various purposes. The combination of iSeries data, SQL Server databases and user-created spreadsheets create a highly functional information system.

However, Mike’s task was to find a better method of integrating the workflow so that data could move transparently between the iSeries and the Microsoft SQL Server environments without tedious file-transfers, ad hoc queries, or manual intervention. For The Pantry's purpose, this workflow needed to be quick, yet rigorously and securely controlled.

Genesis of the Data Integration Problem

Microsoft SQL Server is a very common platform from which data is transferred between the IBM iSeries and Microsoft databases and spreadsheets. Normally, to transfer data to the SQL Server, the user creates a query written in SQL that selects and extracts the data through an ODBC driver on a Personal Computer into one of several Microsoft Windows applications. Other traditional ways of transferring data include file import/export processes, terminal emulation, and file translation utilities.

The primary problem with all of these mechanisms is that the user must initiate the transfer processes through scripts, custom interfaces, or ad hoc queries that are maintained separately from the iSeries database. Why is this a problem? Too often the defining elements of these access methods are out of the sight and control of the iSeries database administrator. Often these access definitions are so highly customized that they require special knowledge or training to effectively use. Sometimes, a change in the mission critical application itself can have an adverse ripple effect on the entire information system, skewing report results without the user's knowledge. Finally, transfers initiated by users through the Microsoft SQL Server can seriously impact the performance of the iSeries as SQL creates new access paths over the DB2 database to select and extract the data that the user needs.

All of these traditional obstacles of the Microsoft SQL Server environment were elements that were causing Mike Burke to look for a better solution. What he found – and what The Pantry, Inc. is using today – is a unique product from RJS Software Systems called RPG2SQL Integrator.

RPG2SQL Integrator's Unique Solution

After investigating several products by different third party vendors, Mike downloaded the free trial of RJS Software's RPG2SQL Integrator.

RPG2SQL Integrator is different than other file transfer and integration mechanisms in the market. First of all, RPG2SQL isn't a utility program, but a native iSeries API (Application Program Interface) that allows iSeries ILE RPG or ILE COBOL or OS/400 CL applications to directly create and access any Microsoft compliant Active Data Object (ADO).

In other words, using RPG2SQL Integrator, The Pantry’s development staff could tailor existing RPG reports and programs on the iSeries to create spreadsheet workbooks for The Pantry's users. Instead of re-keying data from existing reports, the RPG2SQL API allowed Mike’s team, consisting of Bill Barnes and Carol Rouse, to directly control when and how data was going to be written to user spreadsheets.

For the first time, The Pantry had the ability to develop data integration processes that didn't require manual intervention, were easy to implement within the iSeries, and were centrally controlled. In addition, the RPG2SQL API allowed two-way interaction, so that data that was transformed by users in their spreadsheets could be tested and uploaded into the iSeries database using data verification programs written on the iSeries.

Versatility -- Beyond the Basics

The Pantry began using RPG2SQL Integrator to eliminate file downloads and Microsoft Query extractions for weekly sales reports from the 1,300 stores in company's chain of operations. Soon, however, they were expanding the use of RPG2SQL to develop gasoline pricing analysis data in a 52-week rolling report. They also used RPG2SQL to automate the transfer of payroll data between the iSeries and Personnel Department spreadsheets. And they began experimenting with creating Microsoft Access databases as subsets of the iSeries DB2 database. The more they used RPG2SQL Integrator, the more they realized that its versatility in their environment was one of its greatest strengths.

For instance, The Pantry maintains its master vendor file on its Unix-based system, for a separate Store Reporting System. However, because The Pantry's property management system is maintained on the iSeries, it too needed the master vendor information. Consequently, both file systems had to be maintained separately. Through the use of RPG2SQL and ODBC The Pantry's staff was able to eliminate double keying.

In fact, RPG2SQL allows ILE/RPG, ILE/Cobol and CL programmers to directly access any network attached database such as Oracle, Microsoft SQL Server, MS Access, dBase, FoxPro, Excel, CSV, or MySQL from within an RPG or Cobol program itself. Since the RPG2SQL API uses ADO (Microsoft Active Data Objects) for database connectivity, any database type that can be opened via ADO or ODBC can be accessed directly from an RPG program.

RJS Software Systems Support

The more The Pantry expanded their use of RPG2SQL Integrator, the more things they found that they wanted to integrate with their iSeries. Here, the manuals for RPG2SQL Integrator proved to be extraordinarily helpful. The documentation included a complete index of the service procedures used by the API, with sample code demonstrating how each service procedure was implemented in RPG, COBOL, and DDS. The documentation even included directions on how to natively integrate Microsoft Excel Spreadsheets using RPG2SQL Integrator.

And when questions arose that were not covered in the documentation, RJS Software Support was available to provide a quick solution. According to The Pantry’s developers, "Ninety-five percent of the time when we asked for a way to do something with RPG2SQL Integrator, RJS Software was able to help us. Their support was very, very good."

Building Upon Success

Mike Burke’s search for a better way to integrate Microsoft's SQL Server with the iSeries led him to RJS Software's RPG2SQL Integrator.

Not only has RPG2SQL Integrator helped The Pantry to streamline the processes of data transfer and integration, but it has allowed a small team of developers to better serve the diverse needs of a growing organization. The resulting information system has been greatly enhanced by the RPG2SQL Integrator API. Now – with fully integrated Microsoft SQL Server and iSeries environments – RPG2SQL is removing obstacles and increasing the organization's return on investment. It's providing a solution in a controlled, iSeries-centric manner that removes complexity, increases speed, and reduces manpower requirements.

For more information about RJS Software's RPG2SQL Integrator, click here!

Thomas M. Stockwell is an independent IT analyst and writer. He is the former Editor in Chief of MC Press Online and Midrange Computing magazine and has over 20 years of experience as a programmer, systems engineer, IT director, industry analyst, author, speaker, consultant, and editor.  

 

Tom works from his home in the Napa Valley in California. He can be reached at ITincendiary.com.

 

 

Also Read

Case Study: Hörmann Success Story

Case Study: Positioning for Growth with Barcode400

Case Study: Hot Doughnuts en Masse Require 100 Percent System i Reliability at Iconic Krispy Kreme