Pathix took Navixa and ran, creating other management solutions and providing technology support services to Vector Aerospace, its parent company, but continuing its focus on Navixa and the aviation industry.

Originally written in ILE RPG, running on an iSeries server and relying on the iSeries' integrated DB2 database, Navixa had two main issues developers wanted to address in the next version. First, the ILE RPG code resulted in green screens that were awkward for aircraft repair people, parts inventory workers, and other end users needing to enter data into the software tool. Second, the application couldn't be integrated with other databases.

Enter SQL. Using SQL to call stored procedures for accessing information in DB2 gave Pathix the ability to create a user-friendly, Java-based graphical user interface running on client PCs and allowed access to other databases for off-platform integration with the iSeries. Pathix turned to SQL Procedural Language (SPL) to implement all of the stored procedures, which can run on the iSeries or other database management systems (DBMS).

"SQL is a powerful, flexible language that takes us a step in the right direction when it comes to code portability and not being tied to a particular database," says Dean Clowe, a Pathix senior software architect who specializes in application modernization, integration, and new technologies. A SQL engine is integrated with DB2 for iSeries so that developers can make use of it right out of the box.

Pathix uses two iSeries boxes to run the versions of its Navixa software that have been enhanced with SQL stored procedures, a model 270 with 1 GB of memory and 280 GB of disk space for development, and a model 810 with 3 GB of memory and 282 GB of disk space for production. Both systems run single processors and have OS/400 V5R3 installed. Initially, Pathix's production system was running V5R2, but developers encountered difficulties compiling some stored procedures on this version. The company's research showed that SQL support had been dramatically improved in V5R3, so the company is upgrading all of its production systems to the most recent version of OS/400.

Besides Navixa, the company's production iSeries runs various third-party tools for application modernization, data warehousing, fax, and other tasks.

Pathix initially explored using SQL to query DB2 in its operations-management software for the aviation industry because its Swing-based, Java user interface runs on a client PC rather than on the OS/400 JVM, so Pathix couldn't use the native JDBC driver to access DB2. In short, the company wanted to offer Navixa customers rich user interfaces rather than green screens, and that required having client applications access DB2 data via a client JDBC driver, which increases performance overhead for each JDBC call. By using stored procedures, some of the work could be shifted to the server. Stored procedures also simplified client-code programming.

Seagull Software's JWalk, a Web and GUI-facing tool, provides a GUI front end that users see when they look at the Navixa application. This front end interacts with Java client code running on the end users' PCs, which calls stored procedures written in SQL to query and update the DB2 database on the back end. Most of Pathix's SQL queries access a DB2 database on the iSeries, but some portions of the Navixa application access other databases for off-platform integration with the iSeries. Much of this is handled by calling a server-side Java program directly from an ILE RPG program and having this Java program access another database via JDBC.

Although the performance of some stored procedures that implement complex queries is not yet ideal, Clowe is certain that once performance issues are resolved and developers gain experience in developing SQL, the advantages of using stored procedures for database access will outweigh the tough times of a new code implementation. The Pathix team is trying to determine whether the performance issues are being caused by the SQL engine, configuration issues, or inefficiently written queries.

A unique aspect of Pathix's SQL implementation in which Clowe evinces great pride is the automation of CRUD (Create, Read, Update, and Delete) stored procedures that access the four essential database functions. Pathix's CRUD procedures are created by an iSeries-based code generator that Pathix developed in-house. The generator reads the database structure and outputs the SPL source code to implement CRUD routines for a given database file. The generator supports custom code sections and code-regeneration, keeping custom code intact. "We have found this to be a particularly useful way to access the database while keeping the number of procedures [we have to write] at a minimum," Clowe says. "It's a real time saver and reduces the chance of error compared to hand-coding stored procedures."

As far as tips for other companies venturing into SQL and stored procedures on the iSeries, Clowe suggests keeping on top of database Program Temporary Fixes (PTFs). For example, Pathix encountered a host-side memory leak that consumed large amounts of JDBC job memory when running stored procedures, but it was cured with an update to the latest PTFs.

Clowe also recommends iSeries Navigator's SQL Performance Monitor and Visual Explain as "an absolute essential set of tools," which "have been valuable to me in diagnosing poorly performing SQL statements and in helping me resolve the issue," he says. WebSphere Development Studio Client (WDSc) for iSeries also has made his life easier when editing and debugging SQL.

"It has been a great learning experience for our team," Clowe says, "enriching our development skills and giving us the opportunity to take our application to a new level.

Contact: Lynn Fillier