ProData’s Remote Database Access Tool Gains Automation

October 16, 2007 Dan Burger

There are several ways to access remote databases from the System i. They range from complex and expensive to simple and inexpensive. One of the least expensive and easiest to use methods has been developed by ProData Computer Services. Its Database Utility (DBU) is used by thousands of System i shops to access relational database systems, and earlier this year DBU Remote Database plug-in (DBU RDB) opened up cross-platform database access. This week ProData is releasing Remote Database Connect (RDB Connect), a new access avenue for RPG, COBOL, and CL programs.

The difference between RDB Connect and DBU Remote plug-in is a higher level of automation with RDB Connect. DBU Remote, which became available in May, opened the door to accessing DB2/400, Microsoft SQL Server, Oracle, MySQL, and other databases and allowed users to examine those databases and edit them through ProData’s DBU, database utility interface. However, the process of attaining that data for real-time integration into current applications was not available. This new product gives developers full SQL access to remote databases from RPG, COBOL, or CL programs running on the System i. Using APIs to execute SQL queries, RDB Connect allows record-level I/O as well as the import/export of remote data and can be used in batch or interactive programs.

An implementation of RDB Connect can contain one or more applications, a core RDB Connect library, and one or more database drivers. The library, which is independent of the applications and the remote database, acts as an “interpreter” between the applications and the database drivers, whereas the database drivers contain the remote database-specific details. Because of this design feature, programmers can write applications that use standard types and features without concern for the specifics of each remote database that the applications may encounter. Likewise, the database driver implementers only attach to the core library, which removes complexity as well.

“RDB Connect drives our DBU Remote Database plug-in and many of our customers wanted that same remote database access integrated within their business applications,” said ProData owner and CEO, Allen Hartley. “RDB Connect allows our customers to easily share data across platforms within their programs, which can eliminate data transfers or duplicate data entry in some cases.”

Wilson Jones, an IT consultant working for a 150-store pharmacy chain located in the Midwest, is using ProData’s DBU Remote tool to talk with four servers running SQL Server. Two of those servers are located in the same data center as the iSeries that is running DBU Remote, and two other servers are located in a data center approximately 35 miles away. The SQL Server databases are populated by pulling data from the retail stores in flat files.

“We needed the tool to see what’s in the SQL databases,” Jones said. “The ‘400 still does not have a very good way to talk with remote SQL Servers. SQL works great on the ‘400 native, but when the ‘400 acts as a client, the process is not very good. There are tools available on the Windows side that do this sort of thing, but what I like about this tool [DBU Remote] is that it creates physical files on the ‘400 that hold the SQL table.”

IBM‘s solution to this problem is a product called Generation Server. It’s a combination of hardware and software. It includes a blade server that the System i uses to connect with other databases.

Jones describes DBU Remote as a manual interactive tool that lets you look at the SQL databases, analyze them, figure out what tables and views are available, and what’s in those tables and views. He says it allows him to select statements that pull all data or selected data. After deciding on a table or view, Jones and two other staffers use a combination of Java and RPG programs to automate the flow of selected data.

“We access the SQL Server with DBU and connect to the server using an IP address, passwords, schema name, catalog server name, etc. Then DBU handles the connection. It displays the table and view. Then we pick what we want. It can use fields from those views. It can create files on the ‘400,” Jones said.

This work has been going on since May. “We have a lot of stuff in production, but we are still using DBU every day to add new things to our applications,” Jones said.

With ProData’s release of RDB Connect this week, users like Jones will have an automation tool that eliminates the need to code a program for every piece of information that’s necessary.

“We are not changing any information on the SQL machines,” Jones said. “The SQL servers are used as a repository for the information. We pull it down on the ‘400. Some of it we keep in detail. Most of it we summarize and then the applications on the ‘400 present the data to the end users, who are the company executives. The information mostly relates to distribution analysis, payroll, and accounting.”

The applications, which are running on the System i, show up on user screens after Jones and other IT staff create the predefined files. The end users import spreadsheets for their use. Jones says many of the users know how to pull data from the OS/400 files that is compatible with Acess, Excel, and other programs.

ProData has set the price for RDB Connect at $2,495. It requires DBU 7.0, i5/OS V4R5 or greater, and an IP connection to the remote server. For more information, visit www.dodbu.com.