
By Jeremy Lyon Lyon | Article Rating: |
|
April 12, 2006 12:15 PM EDT | Reads: |
30,496 |
In this article, I'll show you how to connect ColdFusion MX to an IBM iSeries (formerly AS/400) DB2 database. I'll describe your connectivity options and give you configuration examples.
While attending an iSeries conference a couple of years ago, I had the opportunity to talk with an IBM DB2 engineer. I still remember the blank look on his face when I explained that I use ColdFusion to access his database. In his defense, I've enlightened more than a few Macromedians as to what an iSeries is and what it does. Thank heaven for standards and thank the heavens again for Macromedia's and IBM's adherence to them.
There are several ways to get to DB2 data on the iSeries. We'll concentrate on ODBC and JDBC. Platform examples will include ColdFusion MX 6.1 on Windows 2000 Server and JRun/ColdFusion MX 6.1 on Apple OSX 10.1.5.
Windows 2000 Server
ODBC Configuration Step by Step Installation
To make ODBC work, you need Client Access for iSeries installed on the ColdFusion Server. Client Access for iSeries is a licensed program and is available on the media distributed with the operating system. ODBC is an option provided with the Client Access tools suite. For security reasons it's best to perform a selective setup and choose only the options you will need (see Figure 1).

In the iSeries world, a software patch is known as a PTF or Program Temporary Fix. I cannot stress enough how important these are. Make sure that you download and apply the latest iSeries Client Access PTFs.
Creating a System DSN for ODBC
Create a system DSN with the Windows ODBC Administrator using the iSeries Access ODBC driver. I won't cover all the settings, but I'll point out a few that will make it work better with MX Studio and Server.
Server Tab
Naming convention differences: It's best to use SQL naming instead of system or *SYS. SQL naming uses dotted notation when separating libraries and files. *SYS uses the less-common forward slash mark.
Library list: This is where you set the libraries that this ODBC connection will use to find data. While explicit paths will work in your SQL statements, the libraries will not show up in the Studio MX available libraries list for the data source.
Performance Tab
Use blocking with fetch of one row: Use this option when your application iterates through small blocks of sequential records.
Enable lazy close support: This option prevents unnecessary opening and closing of connections.
Create a ColdFusion Data Source
Open your ColdFusion server administration screen and create a new data source. The type will be ODBC Socket and the name will be the name you gave to the System DSN. Supply a profile and password, verify the data source, and you should be ready to go.
Diagnostics/Troubleshooting
This is where ODBC has bragging rights over JDBC on the iSeries. The ODBC facilities for troubleshooting application performance and connectivity are as comprehensive as they are easy to use. You can access these tools from the Windows 2000 ODBC configuration panel.
Windows 2000 Server
JDBC Configuration Step by Step
IBM provides three JDBC drivers for the iSeries: the Native JDBC driver, the JT400 or IBM Java Toolbox driver, and JTOpen, the open source driver. The native driver, while faster than the others, will only run on the iSeries JVM.
What is the IBM Toolbox for Java? What is JTOpen? What are the differences? The answer is very little. The IBM Toolbox for Java is the polished, refined older brother of JTOpen.
JTOpen is the bleeding-edge open source version of the toolbox. It includes contributions from the open source community along with the newest fixes and enhancements from IBM. JTOpen is covered by the IBM Public License. Perhaps the most significant difference between the two is how the products are supported. JTOpen's support is provided through an online forum whereas the Toolbox is supported through traditional IBM software support channels.
The Toolbox formally known as the "IBM Toolbox for Java" is the licensed version of JTOpen. It's shipped with the iSeries as part of the base operating system and can be referenced by its licensed program code 5722JC1 or 5769JC1 for OS/400 versions prior to V5R1.
IBM Toolbox for Java and JTOpen Installation
or

ColdFusion is now connected to the iSeries (see Figure 4).

Apple OSX
JDBC Configuration Step by Step
The most intriguing capability for Web developers may be OS X's built-in support for Java. OS X ships with Java2 Standard Edition (J2SE), allowing developers a native environment to deploy their Java applications.
Connecting to the AS400 from JRun and ColdFusion
Performance Tips & Additional Settings
Now that you have a basic connection to the iSeries, it's time to do some performance tuning. While researching this article I performed countless query tests against various driver and server settings. I quickly realized that comprehensive performance tuning was well beyond the scope of this article. I've included links to IBM documentation at the end of the article.
<cfquery name="myQuery" datasource="my_iSeries">
{ library.program('parm') }
</cfquery>
I'm not a DBA and I don't know all that much about packaging in DB2, but if you do and want to use it here's how to set it up:
jdbc:as400://[iSeries server name or address]/[Database
Name];prompt=false;libraries=*libl,mylib;extended dynamic=true;
package=mypackage;package library=mylib;package cache=true;
Modifying the block size and enabling data compression may also improve performance depending on your workload.
as jdbc:as400://[iSeries server name or address]/[Database
Name];prompt=false;libraries=*libl,mylib;block size=512;
data compression=true;
Conclusion
The Java Toolkit comes with literally hundreds of Java classes that you can access using the <cfobject> tag. The simple call in Listing 1 gets disk space information.
What a combination! A world-class database platform paired up with a world-class Web application server. Thanks to standards, ODBC and JDBC are just the tip of the iceberg when it comes to integrating ColdFusion with the iSeries.
Acknowledgment
I'd like to thank my coworker and Apple guru Robert Haddan. He configured, tested, and documented the configuration process on OS X.
Additional iSeries Resources
Published April 12, 2006 Reads 30,496
Copyright © 2006 SYS-CON Media, Inc. — All Rights Reserved.
Syndicated stories and blog feeds, all rights reserved by the author.
More Stories By Jeremy Lyon Lyon
Jeremy Lyon is the eGovernment Coordinator for the Oregon Department of Revenue. Jeremy has over 15 years of administration and programming experience utilizing systems ranging from the Timex Sinclair 1000 to the Cray XMP supercomputer.
![]() |
Jeremy Lyon 04/13/06 02:33:25 AM EDT | |||
Daron, "No suitable driver" usually means that the JVM did not load the JTOpen jt400.jar file Is it in the class path? Jeremy |
![]() |
Jeremy Lyon 04/13/06 02:26:16 AM EDT | |||
geebee, If you use CF5 yes. You can do all the configuration in the cfquery tag. If you use CF7 read: http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=... It's for Microsoft sql server but I don't see any reason why it would not work with the iSeries. |
![]() |
SYS-CON Brazil News Desk 04/12/06 01:38:27 PM EDT | |||
In this article, I'll show you how to connect ColdFusion MX to an IBM iSeries (formerly AS/400) DB2 database. I'll describe your connectivity options and give you configuration examples. |
![]() |
geebee 04/12/06 01:23:47 PM EDT | |||
Jeremy, Very good article. impressive. We have an AS400 system here from which we retrieve data using Shocase and then put the data into Excel, csv files and MS Access. Very cumbersom though. What we need is an integrated solution from which users can look at data from the AS400 legacy system using some sort of web application, preferrably something built using coldfusion since I am relatively familiar with that. However, I do not have the CF administrator on my machine, due to LAN. It will be probably tough to getr them to load it for me. So, what is a workaround using notepad to link CF and AS400. Any other ideas? Thanks in advance, |
![]() |
Daron 03/03/06 05:13:52 PM EST | |||
I've tried the instructions for connecting to the 400. I get the following error: java.sql.SQLException: No suitable driver available for PSEAN, please check the driver setting in resources file, error: null |
![]() |
SYS-CON Germany News Desk 10/15/05 02:48:56 PM EDT | |||
Macromedia ColdFusion - MX to iSeries Demystified. In this article, I'll show you how to connect ColdFusion MX to an IBM iSeries (formerly AS/400) DB2 database. I'll describe your connectivity options and give you configuration examples. |
![]() |
ColdFusion Developer's Journal News Desk 10/15/05 01:50:18 PM EDT | |||
MX to iSeries Demystified. In this article, I'll show you how to connect ColdFusion MX to an IBM iSeries (formerly AS/400) DB2 database. I'll describe your connectivity options and give you configuration examples. |
![]() |
Tom McHugh 10/15/05 01:22:04 PM EDT | |||
Jeremy: Will the steps outlined in your article "MX to iSeries Demystified" be the same under Windows 2003 Server? Thanks! |
![]() |
Uwe Raddatz 01/15/04 10:05:07 AM EST | |||
Hi, I am using the JTOpen 4.1 JDBC driver to access an iSeries server. Sometimes it happens, a stored procedure call hangs in reason of problems during execution on the database server. The JDBC driver does not throw an exception, so the page request will not be answered. I set the "Timeout Requests after ..." setting to 60 seconds but this only works for pure ColdFusion tags such as . It does not work for stored procedure calls. Also there's no timeout parameter for the JDBC driver. :( So these non-responding requests accumulate up to the number defined in the "Maximum number of simultaneous requests" setting in ColdFusion Administrator. Further requests will be queued - finally no request will be answered until ColdFusion server will be restarted. It's a dissatisfying situation. ;) Any suggestions to find a solution? Bye. |
![]() |
Jeremy Lyon 12/11/03 11:25:50 PM EST | |||
Hi, DB2/400 CHAR pads the field to the field length. VARCHAR does not. To strip the spaces I would: Trim it at the SQL level i.e., SELECT trim(fieldname) as field OR create a db2 view of the file i.e., Create view qgpl.table_view as Select Trim(charfield) trimed_char From qgpl.table Jeremy |
![]() |
Uwe Raddatz 12/11/03 06:49:01 AM EST | |||
Can anyone tell me how to prevent that query result fields will be filled up with blanks up to the maximum length of the column? And what could be the reason of sporadic and unreproducible exceptions when calling queries or stored procedures (see stack trace below)? java.lang.NumberFormatException: 266 Thanx for help. |
![]() |
Jeremy Lyon 12/10/03 11:09:11 PM EST | |||
Other than setting up the JTOpen toolbox, the OSX setup was run of the mill. I had the JRun stuff in the draft but it really didn't add anything to Macromedia's instructions so I cut it. If you have specific questions I would be happy to help. |
![]() |
Rob Brooks-Bilson 12/10/03 04:45:04 PM EST | |||
If you are running CF MX for J2EE, you can drop the jt400.jar file in {jrun.home}/servers/lib/. This keeps you from having to specify it's location in you Java CLASSPATH. |
![]() |
Andy Karas 12/09/03 05:08:01 PM EST | |||
I couldn't find any reference to JRun/ColdFusion MX 6.1 on Apple OSX 10.1.5 in this article. Is there a similar article which outlines how to use ColdFusion on Mac OSX? |
![]() Apr. 22, 2018 05:15 AM EDT Reads: 3,764 |
By Elizabeth White ![]() Apr. 22, 2018 04:00 AM EDT Reads: 17,263 |
By Liz McMillan ![]() Apr. 22, 2018 03:00 AM EDT Reads: 3,817 |
By Yeshim Deniz ![]() Apr. 22, 2018 02:15 AM EDT Reads: 1,945 |
By Liz McMillan ![]() Apr. 22, 2018 01:00 AM EDT Reads: 3,762 |
By Liz McMillan Apr. 22, 2018 12:15 AM EDT Reads: 2,590 |
By Pat Romanski Apr. 21, 2018 11:45 PM EDT Reads: 2,684 |
By Liz McMillan ![]() Apr. 21, 2018 11:15 PM EDT Reads: 17,161 |
By Elizabeth White ![]() Apr. 21, 2018 11:00 PM EDT Reads: 6,322 |
By Pat Romanski ![]() Apr. 21, 2018 11:00 PM EDT Reads: 6,866 |
By Liz McMillan ![]() Apr. 21, 2018 10:15 PM EDT Reads: 22,540 |
By Yeshim Deniz ![]() Apr. 21, 2018 09:30 PM EDT Reads: 6,807 |
By Elizabeth White ![]() Apr. 21, 2018 09:30 PM EDT Reads: 3,846 |
By Maria C. Horton ![]() Apr. 21, 2018 09:15 PM EDT Reads: 13,608 |
By Yeshim Deniz Apr. 21, 2018 07:00 PM EDT Reads: 2,755 |
By Pat Romanski ![]() Apr. 21, 2018 03:45 PM EDT Reads: 6,849 |
By Pat Romanski Apr. 21, 2018 02:30 PM EDT Reads: 2,295 |
By Pat Romanski Apr. 21, 2018 02:15 PM EDT Reads: 1,345 |
By Liz McMillan Apr. 21, 2018 01:45 PM EDT Reads: 1,676 |
By Elizabeth White ![]() Apr. 21, 2018 12:45 PM EDT Reads: 6,347 |