Application and Data Integration Between Linux and OS/400

January 28, 2004 Erwin Earley and David Boutcher

Previous articles have introduced the basics of Linux in logical partitioning, as well as networking alternatives for Linux, on iSeries systems. These articles covered the advantages of using iSeries for Linux in virtualizing resources (such as disk and network) and explained how to manage workloads better through subcapacity processor allocation. This article explains how Linux on iSeries can integrate with OS/400 to access data resident in DB2 databases, as well as flat files resident in the OS/400 Integrated File System.

A number of solutions can be built upon the virtual LAN features of Linux on iSeries to integrate Linux with data resident on OS/400. These capabilities can be broken down into two types:

• Architecturally pure solutions: DB2 Connect, Java Database Connectivity (JDBC), and iSeries Open Database Connectivity (ODBC) Driver

• Architecturally impure solutions: File Transfer Protocol (FTP), Network File System (NFS), and iSeries NetServer

Architecturally pure solutions are those in which the data remains resident on the database server. Impure solutions, on the other hand, are those that make a copy of the data and transfer it to the server that the application is running on. Architecturally pure solutions provide real-time access to the data, while architecturally impure solutions are only as accurate (from a data perspective) as of the time the data was copied. Both of these types of data access are discussed in this article.

LINUX WEB-APPLICATION MODELS

As the number-one Web server on the Internet, and certainly the number-one Web server under Linux, Apache is a good vehicle for WebFacing into DB2 data resident in OS/400. The Apache Web server can be extended through a variety of scripting languages to provide additional capabilities. Two popular scripting languages, Java and JavaServer Pages (JSP) and PHP Hypertext Preprocessor (PHP) can be used to extend the capability of the Web server by providing access to data resident locally or in database servers. As a side note, you may notice that PHP is a recursive acronym; this is one of many such acronyms that you will discover in the open-source community.

The Apache Web server integrated with scripting languages

Both of these methods are capable of accessing DB2/400 data, as will be explained in this article.

Linux and OS/400 Integration can be broken down into two main areas: access to DB2/400 data sources and access to flat files resident in the OS/400 IFS.

Linux integration with OS/400-resident data

There are a number of integration options: IBM Toolbox for Java, including JDBC; the DB2/Connect product; Network File System (NFS); ODBC for DB2 UDB; and iSeries NetServer for OS/400 IFS.

PURE DB2 SOLUTIONS

Let’s look first at pure DB2 solutions for accessing DB2/400 data. A pure DB2/400 solution is characterized as a solution in which applications deployed in Linux access data that remains resident in the DB2/400 databases. This model of data access is often referred to as client/server.

The Java Database Connectivity (JDBC) driver allows access to DB2 UDB data sources from an iSeries Linux partition. JDBC can be used to access OS/400 V4R4, V4R5, V5R1, and V5R2. The access can be via a virtual or real (physical) TCP/IP LAN connection (previous articles in this publication provide information on virtual LAN capability in iSeries LPAR). The JDBC driver supports the standard Java Virtual Machines, including IBM Java Virtual Machine 1.3.1, which is included in the SuSE distribution of Linux for iSeries and can be downloaded for other iSeries distributions. JDBC is part of the Toolbox for Java and can be downloaded from IBM’s Web site.

A JDBC-enabled Java application running in a Linux partition and accessing DB2/400 data in a secondary OS/400 partition

Keep in mind that in the diagram above the access is across the virtual LAN provided in OS/400 and supported in iSeries logical partitioning, so the access speed between the Java application and the back-end data will be at 1 GB.

With the recent release of DB2 UDB for Linux on iSeries, Version 8.1, DB2 Connect can be used to provide a data connection between Linux and DB2/400. DB2/400 provides a connection between a client application and a DB2 database resident on any of a number of servers, including OS/390, VM/VSE, and DB2 UDB for iSeries.

DB2 connect for application connectivity to DB2 UDB data

A number of APIs (application program interfaces) are supported with DB2 Connect, including the following:

• Embedded SQL–Static SQL, Dynamic SQL, Java (SQLJ), COBOLl, C, C++, FORTRAN, REXX

• ODBC

• DB2 call level interface (DB2 CLI)

• JDBC (Java)–JDBC 2.0 compliant; both application and applet drivers are provided

A number of steps have to be performed when using DB2/Connect to access DB2/400 data:

• Make sure that the DDM server is started (STRTTCPSVR *DDM).

• Install DB2 Connect on the client.

• Run the following commands from within DB2 Connect on the client. (The text in italics will be substituted with entries specific to your environment.)

catalog tcpip node s400a remote s400a.rchland.ibm.com server 446

terminate
catalog database s400a at node s400a authentication dcs
terminate
catalog dcs database s400a as s400a
terminate
connect s400a user dave using mypwd
select * from mylib/myfilep

The above commands will connect to the DB2/400 database engine, via DB2 Connect; build a database catalog on the “client”; and perform a simple query on one of the tables defined in the database catalog.

BUILDING INTEGRATED APPLICATIONS

A number of scripting languages, including PHP and Perl, can be used to build integrated applications in Linux that access data in DB2/400 via DB2 connect. PHP is a general-purpose scripting language especially suited for Web development, as it can be easily embedded into HTML (as you will see later in this article). PHP can be used to perform a number of functions, such as collecting data for Web forms, generating dynamic page content, and sending and receiving of cookies.

To integrate Apache with DB2/400 data, we will use both Apache and PHP to build a Web page with dynamic content from the DB2/400 databases. To use DB2/400, both Apache and PHP have to be recompiled to have DB2 support. In Linux, this is accomplished with the “enable” flag to the compile statement, as follows:

--enable-ibm-db=/usr/IBMdb2/v7.1

Once Apache has been compiled (and installed) with PHP support, it is necessary to add the DB2 environment variables to the Apache startup script. This can be done by executing the startup script of the db2instance:

./usr/lib/db2/db2inst1/sqlib/db2profile

There is a good “how to” guide on the Web that discusses building Apache and PHP DB2 connections in Linux, called “AS400 DB2 Linux PHP Connection howto.”

Finally, data from DB2/400 can be included by embedding PHP into HTML, as in the following example:

<html>
<body>
<h1 align=center>Dave's iSeries Performance! </h1>
<table border=1 bgcolor='#7F8F9F' align=center>
<tr>
 <th>Interval</th>
 <th>CPU Seconds</th>
 <th>Interactive seconds</th>
</tr>
<?
$dbname="rchcst14";
$dbuser="dave";
$dbpwd="mypwd";

$db=odbc_connect($dbname,$dbuser,$dbpwd);

$sql="select intnum, scpu01, scifus from qpfrdata.qapmsyscpu";
$result=odbc_exec($db,$sql);

While (odbc_fetch_row($result)) {
   printf("<tr><td>%s</td><td>%s</td><td>%s</td> </tr>",
   odbc_result($result,1),odbc_result($result,2),odbc_result($result,3));
   }
odbc_close($db);
?>
</table>
</body>
</html>

The text in italics above is the PHP script. The script establishes three variables (dbname, dbuser, and dbpwd) to identify the name of the database to establish a connection to, and the user name and password used to authenticate the access. An additional variable (db) is setup, which contains the odbc_connect statement, which performs the actual connection to the database; and the variable sql contains the query that retrieves a set of records from the database. The final variable setup by the script is the result variable that contains the odbc_exec statement, which, as you will see in a minute, performs the function of connecting to the database and executing an SQL statement.

Finally, the while loop performs an “odbc_fetch_row” request that will use the result variable (which contains the database connection information and SQL query statement) to retrieve a set of records from the database and to loop through the records returned. Within the “while” loop, the fields from the records are printed (printf) to the HTML form. The fields returned are represented by the odbc_result parameters in the printf statement.

The odbc_close statement terminates the connection to the DB2/400 database that was established with the odbc_connect statement. The $> construct closes the PHP script within the HTML script.

The above script results in a Web page.

Web page results of using PHP integrated with ODBC

Remember that with the use of one of the pure solutions, such as DB2 connect, JDBC, or ODBC, you can build Web pages (or other applications) that provide dynamic access to the DB2/400 data.

DB2/400 INTEGRATION WITH ODBC

The ODBC driver, which conforms to the ODBC 3.5 specification, can also be used to provide access to DB2/400 data from Linux-based applications. The driver is included with some of the Linux on iSeries distributions. The driver can be downloaded from IBM’s Web site. The communication between Linux and DB2/400 uses the existing OS/400 host servers.

ODBC access to DB2/400 databases from within Linux can be provided to a number of applications, including OpenOffice, Apache/PHP, Perl, and isql (for interactive access to DB2/400 database tables).

The PHP that ships with the Linux on iSeries distributions has not been built with the necessary options to enable ODBC support. Additionally, Apache generally needs to be built with dynamic shared object DSO support. The DSO support is a method of linking modules into apache. DSO is the mechanism that is used to build and link the PHP ODBC module into Apache.

IMPURE DB2 SOLUTIONS

Impure DB2 solutions are those solutions that work with DB2/400 data by snapshotting a copy of the data in OS/400 and then transferring the resulting file to Linux for use by Linux applications. Solutions of this type have an advantage, in that no additional support (such as ODBC) needs to be installed in Linux and the Linux applications do not need to be rebuilt for that support. The disadvantage of using impure solutions is that the resulting data used by Linux is static and only reflects the state of the database at the time the data was collected, not when the application was executed.

As an example, Linux applications have support for comma-separated files (CSVs). The following OS/400 command can be used to make a copy of a database into a comma separated file:

CPYTOIMPF FROMFILE(QPFRDATA/QAPMSYSCPU 
TOSTMF('/home/dave.data')
STMFCODPAG(*PCASCII) RCDDLM(*CR)

Once the file has been generated, it can be transferred to Linux through any of the supported network applications, such as FTP, Network File System (NFS), or iSeries NetServer/Samba. Once the file is available to Linux, it can be used by Linux applications, such as HTML with embedded SQL:

<html>
<body>
<h1 align=center>Dave's iSeries Performance! (CSV) </h1>
<table border=1 bgcolor='#FF8F7F' align=center>
<tr>
 <th>Interval</th>
 <th>CPU Seconds</th>
 <th>Interactive seconds</th>
</tr>
<?
$row = 1;
$fp = fopen("dave.data","r");
while ($data = fgetcsv($fp,1000,",")) {
   printf("<tr><td>%s</td><td>%s</td><td>%s</td></tr>",
          $data[0], $data[6], $data[38]);
   }
fclose($fp);
?>
</table>
</body>
</html>

This script is similar to the HTML/PHP script shown earlier in this article. The difference is that this script goes against the flat file, rather than dynamic content resident in the DB2/400 database. In this example, the PHP code (shown in italics) opens a file (dave.data) and loops through the records, outputting the fields within each record ($data[0], $data[6], and $data[38]) to the Web form being built by the encapsulating HTML script. The result is similar to the Web page below.

A Web page results using PHP against static (CSV) data

Again, the data being displayed is only current to the point in time when the data file was built in OS/400. For many applications, however, this type of access will be sufficient. OS/400 facilities could be used to automatically generate the data files on a periodic basis and to place the resulting files in a network-accessible directory, which the Linux based application could then access as needed. In this way, Linux-based applications could be built without additional configuration of the application or installation of additional drivers that would provide interfaces to DB2/400 content.

FLAT-FILE ACCESS

Linux on iSeries supports the same network protocols and applications that Linux on any other platform supports, including the TCP/IP protocol and network services such as FTP, NFS, and Samba. These services can be used to integrate with OS/400 to provide file services to OS/400 or, more likely, to retrieve file resources from OS/400. Remember that a virtual LAN can be configured inside the system that provides 1 GB access speeds for intrapartition communication, so access to files between Linux and OS/400 will be quite speedy.

EXTENDING ACCESS TO OS/400 DATA

There are a lot of Linux skills available in the marketplace, as well as Apache, PHP, and MySQL skills. These skills can be used to help build Linux solutions that leverage OS/400 data either dynamically (through use of the database connection mechanisms discussed earlier in this article) or statically through the use of OS/400 facilities and network access to OS/400 from Linux.

Erwin Earley is an advisory software engineer at IBM and has worked at the Rochester, Minnesota, development lab since 1996. At the iSeries Technology Center, he provides education and enablement services for Linux on iSeries. Erwin has worked in the IT industry for 24 years and has experience with several Unix variants, as well as Linux and OS/400. He also provides Linux education classes at a local university. E-mail: earleye@us.ibm.com

Dave Boutcher is a developer with the IBM Linux Technology Center, working in the Linux kernel. Before working on Linux, he was involved in the development of NetServer, FSIOP, and communication products for OS/400. E-mail: boutcher@us.ibm.com