04
Mon, Nov
1 New Articles

Case Study: RJS RPG2SQL Integrator Fills the Bill

Case Studies
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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!

Or contact:
RJS Software Systems, Inc.
Web: www.rjssoft.com
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.
Tel: 888-RJSSOFT / 952-898-3038
Fax: 952-898-1781
14041 Burnhaven Drive
Suite 120
Burnsville, MN 55337
United States
Thomas Stockwell

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.

 

 

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: