Click here to close now.


You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Maureen O'Gara, Nancy Y. Nee, Tad Anderson, Daniel Kaar

Related Topics: ColdFusion

ColdFusion: Article

Macromedia ColdFusion - MX to iSeries Demystified

A world-class database platform paired up with a world-class Web application server

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.

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

  • See iseries/toolbox/faq.htm#faqG and index.htm?info/rzahh/rzahnm04.htm for system requirements.
  • Use iSeries Operations Navigator to locate the Java Toolkit folder on the iSeries. You can find it in the QIBM/ProdData/HTTP/Public/jt400/ IFS folder. If it's not installed, have your system administrator install the licensed program 5722JC1 (V5Rx) or 5769JC1 (V4Rx).


  • Download the latest JTOpen from
  • Create a folder on your ColdFusion MX Server. To minimize the classpath length I put one in the root and called it C:JT400 or C:JTOpen.
  • Copy the contents of QIBM/ProdData/HTTP/Public/jt400/ or extract the JTOpen zip file into your new folder.
  • Using the ColdFusion administrator, navigate to Java and JVM Settings and put C:/JT400/lib/jt400.jar or C:/JTOpen/lib/ jt400.jar into the classpath box. If you have multiple classpaths, separate them with a comma (see Figure 2).

  • Stop and restart the ColdFusion MX Application Server service.
  • Under "Data & Services" on the left navigation bar, click Data Sources. In the "Add Data Source" area, give your data source a name, then choose the other driver type and click Add (see Figure 3).


  • CF Data Source Name = My_iSeries
  • JDBC URL = as jdbc:as400://[iSeries server name or address]/[Database Name];prompt=false; You can verify the name by entering the WRKRDBDIRE command on the iSeries. Don't forget the prompt=false. This prevents the iSeries from sending a profile/password dialog box to the ColdFusion server in the event of an invalid profile/password. Without it, the ColdFusion server could hang.
  • Driver Class =
  • Driver Name = Optional
  • Username = Optional. You can always append this information later in your application. I use a username and password during setup and remove it when I know it works.
  • Password=Optional
  • Description=Optional

    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

  • Open the JRun Management Console (JMC) from your browser. Click on the default server link in the "Welcome to Macromedia JRun 4" window.
  • Add a classpath to the JTOpen.jar file you installed into the root level of the hard drive. Click on Settings, then JVM Settings. In the Java VM Settings window, go to the Classpaths for Java VM area, and click the Browse button next to the New Classpath text field. Navigate to the /JTOpen4/lib/jt400.jar file and click Open. The classpath will appear in the "New Classpath" text field. Click Add, then Update at the bottom of the window.
  • Open the ColdFusion MX Administrator.
  • Under Data & Services on the left navigation bar, click Data Sources. In the Add Data Source area, give your data source a name, then choose the other driver type and click Add.
  • In the Add window, enter the JDBC URL as jdbc:as400://[iSeries server name or address]/[Database Name];prompt=false
  • The Driver Class is The Driver Name field is optional. Enter your AS400 username and password, and a brief description of the data source. Click Submit. You can verify that the data source is connected to the database in the Data Sources window by clicking the checkmark icon next to the data source name.
  • ColdFusion is now connected to the AS400.

    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.

  • Avoid using "Select *": ODBC and JDBC will perform unnecessary field-level processing if you use only a few fields.
  • Use connection pooling: The instantiation of a new connection will result in a noticeable performance hit even on the fastest of systems. New connections to a fast iSeries took an average of 350ms to establish without connection pooling turned on.
  • Install the latest patches from IBM and Macromedia: Not only do they fix problems, they add functionality and performance. Both companies have a good track record when it comes to fixes.
  • Take advantage of DB2's stored procedures: You can call them directly from SQL.

    <cfquery name="myQuery" datasource="my_iSeries">
    { library.program('parm') }

    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;

    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.

    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

  • Troubleshooting ODBC: index.htm?info/rzaii/rzaiiodbc22.HTM
  • Performance Tuning ODBC: rzaik/rzaikodbcperfconsd.htm
  • JDBC Properties: index.htm?info/rzahh/javadoc/JDBCProperties.html
  • Troubleshooting JDBC: troubleshooting.htm#RecordLevelAccess
  • Performance Tuning JDBC: info/rzaha/jdbcperf.htm
  • 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.

    Comments (14) View Comments

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

    Most Recent Comments
    Jeremy Lyon 04/13/06 02:33:25 AM EDT


    "No suitable driver" usually means that the JVM did not load the JTOpen jt400.jar file

    Is it in the class path?


    Jeremy Lyon 04/13/06 02:26:16 AM EDT


    If you use CF5 yes. You can do all the configuration in the cfquery tag. If you use CF7 read:

    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


    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
    The root cause was that: 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


    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


    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?


    Jeremy Lyon 12/11/03 11:25:50 PM EST


    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


    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
    at coldfusion.sql.QueryTable.populate(
    at coldfusion.sql.Executive.getRowSets(
    at coldfusion.sql.Executive.executeCall(
    at coldfusion.sql.Executive.executeCall(
    at coldfusion.sql.SqlImpl.executeCall(
    at coldfusion.tagext.sql.StoredProcTag.doEndTag(

    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?

    @ThingsExpo Stories
    Cloud computing delivers on-demand resources that provide businesses with flexibility and cost-savings. The challenge in moving workloads to the cloud has been the cost and complexity of ensuring the initial and ongoing security and regulatory (PCI, HIPAA, FFIEC) compliance across private and public clouds. Manual security compliance is slow, prone to human error, and represents over 50% of the cost of managing cloud applications. Determining how to automate cloud security compliance is critical to maintaining positive ROI. Raxak Protect is an automated security compliance SaaS platform and ma...
    The Internet of Things (IoT) is growing rapidly by extending current technologies, products and networks. By 2020, Cisco estimates there will be 50 billion connected devices. Gartner has forecast revenues of over $300 billion, just to IoT suppliers. Now is the time to figure out how you’ll make money – not just create innovative products. With hundreds of new products and companies jumping into the IoT fray every month, there’s no shortage of innovation. Despite this, McKinsey/VisionMobile data shows "less than 10 percent of IoT developers are making enough to support a reasonably sized team....
    Just over a week ago I received a long and loud sustained applause for a presentation I delivered at this year’s Cloud Expo in Santa Clara. I was extremely pleased with the turnout and had some very good conversations with many of the attendees. Over the next few days I had many more meaningful conversations and was not only happy with the results but also learned a few new things. Here is everything I learned in those three days distilled into three short points.
    With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York and Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound cha...
    DevOps is about increasing efficiency, but nothing is more inefficient than building the same application twice. However, this is a routine occurrence with enterprise applications that need both a rich desktop web interface and strong mobile support. With recent technological advances from Isomorphic Software and others, rich desktop and tuned mobile experiences can now be created with a single codebase – without compromising functionality, performance or usability. In his session at DevOps Summit, Charles Kendrick, CTO and Chief Architect at Isomorphic Software, demonstrated examples of com...
    As organizations realize the scope of the Internet of Things, gaining key insights from Big Data, through the use of advanced analytics, becomes crucial. However, IoT also creates the need for petabyte scale storage of data from millions of devices. A new type of Storage is required which seamlessly integrates robust data analytics with massive scale. These storage systems will act as “smart systems” provide in-place analytics that speed discovery and enable businesses to quickly derive meaningful and actionable insights. In his session at @ThingsExpo, Paul Turner, Chief Marketing Officer at...
    In his keynote at @ThingsExpo, Chris Matthieu, Director of IoT Engineering at Citrix and co-founder and CTO of Octoblu, focused on building an IoT platform and company. He provided a behind-the-scenes look at Octoblu’s platform, business, and pivots along the way (including the Citrix acquisition of Octoblu).
    In his General Session at 17th Cloud Expo, Bruce Swann, Senior Product Marketing Manager for Adobe Campaign, explored the key ingredients of cross-channel marketing in a digital world. Learn how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects).
    We all know that data growth is exploding and storage budgets are shrinking. Instead of showing you charts on about how much data there is, in his General Session at 17th Cloud Expo, Scott Cleland, Senior Director of Product Marketing at HGST, showed how to capture all of your data in one place. After you have your data under control, you can then analyze it in one place, saving time and resources.
    The Internet of Everything is re-shaping technology trends–moving away from “request/response” architecture to an “always-on” Streaming Web where data is in constant motion and secure, reliable communication is an absolute necessity. As more and more THINGS go online, the challenges that developers will need to address will only increase exponentially. In his session at @ThingsExpo, Todd Greene, Founder & CEO of PubNub, exploreed the current state of IoT connectivity and review key trends and technology requirements that will drive the Internet of Things from hype to reality.
    Two weeks ago (November 3-5), I attended the Cloud Expo Silicon Valley as a speaker, where I presented on the security and privacy due diligence requirements for cloud solutions. Cloud security is a topical issue for every CIO, CISO, and technology buyer. Decision-makers are always looking for insights on how to mitigate the security risks of implementing and using cloud solutions. Based on the presentation topics covered at the conference, as well as the general discussions heard between sessions, I wanted to share some of my observations on emerging trends. As cyber security serves as a fou...
    With all the incredible momentum behind the Internet of Things (IoT) industry, it is easy to forget that not a single CEO wakes up and wonders if “my IoT is broken.” What they wonder is if they are making the right decisions to do all they can to increase revenue, decrease costs, and improve customer experience – effectively the same challenges they have always had in growing their business. The exciting thing about the IoT industry is now these decisions can be better, faster, and smarter. Now all corporate assets – people, objects, and spaces – can share information about themselves and thei...
    Continuous processes around the development and deployment of applications are both impacted by -- and a benefit to -- the Internet of Things trend. To help better understand the relationship between DevOps and a plethora of new end-devices and data please welcome Gary Gruver, consultant, author and a former IT executive who has led many large-scale IT transformation projects, and John Jeremiah, Technology Evangelist at Hewlett Packard Enterprise (HPE), on Twitter at @j_jeremiah. The discussion is moderated by me, Dana Gardner, Principal Analyst at Interarbor Solutions.
    Too often with compelling new technologies market participants become overly enamored with that attractiveness of the technology and neglect underlying business drivers. This tendency, what some call the “newest shiny object syndrome” is understandable given that virtually all of us are heavily engaged in technology. But it is also mistaken. Without concrete business cases driving its deployment, IoT, like many other technologies before it, will fade into obscurity.
    Discussions of cloud computing have evolved in recent years from a focus on specific types of cloud, to a world of hybrid cloud, and to a world dominated by the APIs that make today's multi-cloud environments and hybrid clouds possible. In this Power Panel at 17th Cloud Expo, moderated by Conference Chair Roger Strukhoff, panelists addressed the importance of customers being able to use the specific technologies they need, through environments and ecosystems that expose their APIs to make true change and transformation possible.
    The Internet of Things is clearly many things: data collection and analytics, wearables, Smart Grids and Smart Cities, the Industrial Internet, and more. Cool platforms like Arduino, Raspberry Pi, Intel's Galileo and Edison, and a diverse world of sensors are making the IoT a great toy box for developers in all these areas. In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists discussed what things are the most important, which will have the most profound effect on the world, and what should we expect to see over the next couple of years.
    The cloud. Like a comic book superhero, there seems to be no problem it can’t fix or cost it can’t slash. Yet making the transition is not always easy and production environments are still largely on premise. Taking some practical and sensible steps to reduce risk can also help provide a basis for a successful cloud transition. A plethora of surveys from the likes of IDG and Gartner show that more than 70 percent of enterprises have deployed at least one or more cloud application or workload. Yet a closer inspection at the data reveals less than half of these cloud projects involve production...
    Microservices are a very exciting architectural approach that many organizations are looking to as a way to accelerate innovation. Microservices promise to allow teams to move away from monolithic "ball of mud" systems, but the reality is that, in the vast majority of organizations, different projects and technologies will continue to be developed at different speeds. How to handle the dependencies between these disparate systems with different iteration cycles? Consider the "canoncial problem" in this scenario: microservice A (releases daily) depends on a couple of additions to backend B (re...
    Container technology is shaping the future of DevOps and it’s also changing the way organizations think about application development. With the rise of mobile applications in the enterprise, businesses are abandoning year-long development cycles and embracing technologies that enable rapid development and continuous deployment of apps. In his session at DevOps Summit, Kurt Collins, Developer Evangelist at, examined how Docker has evolved into a highly effective tool for application delivery by allowing increasingly popular Mobile Backend-as-a-Service (mBaaS) platforms to quickly crea...
    Growth hacking is common for startups to make unheard-of progress in building their business. Career Hacks can help Geek Girls and those who support them (yes, that's you too, Dad!) to excel in this typically male-dominated world. Get ready to learn the facts: Is there a bias against women in the tech / developer communities? Why are women 50% of the workforce, but hold only 24% of the STEM or IT positions? Some beginnings of what to do about it! In her Day 2 Keynote at 17th Cloud Expo, Sandy Carter, IBM General Manager Cloud Ecosystem and Developers, and a Social Business Evangelist, wil...