You will be redirected in 30 seconds or close now.

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

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 www-1.ibm.com/servers/eserver/ iseries/toolbox/faq.htm#faqG and http://publib.boulder.ibm.com/iseries/v5r1/ic2924/ 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 www-1.ibm.com/servers/eserver/iseries/toolbox/downloads.htm.
  • 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 = com.ibm.as400.access.AS400JDBCDriver
  • 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 com.ibm.as400.access.AS400JDBCDriver. 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: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/ index.htm?info/rzaii/rzaiiodbc22.HTM
  • Performance Tuning ODBC: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/ rzaik/rzaikodbcperfconsd.htm
  • JDBC Properties: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/ index.htm?info/rzahh/javadoc/JDBCProperties.html
  • Troubleshooting JDBC: www-1.ibm.com/servers/eserver/iseries/toolbox/ troubleshooting.htm#RecordLevelAccess
  • Performance Tuning JDBC: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/ 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)

    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.

    @ThingsExpo Stories
    Donna Yasay, President of HomeGrid Forum, today discussed with a panel of technology peers how certification programs are at the forefront of interoperability, and the answer for vendors looking to keep up with today's growing industry for smart home innovation. "To ensure multi-vendor interoperability, accredited industry certification programs should be used for every product to provide credibility and quality assurance for retail and carrier based customers looking to add ever increasing num...
    In an era of historic innovation fueled by unprecedented access to data and technology, the low cost and risk of entering new markets has leveled the playing field for business. Today, any ambitious innovator can easily introduce a new application or product that can reinvent business models and transform the client experience. In their Day 2 Keynote at 19th Cloud Expo, Mercer Rowe, IBM Vice President of Strategic Alliances, and Raejeanne Skillern, Intel Vice President of Data Center Group and ...
    @ThingsExpo has been named the Top 5 Most Influential M2M Brand by Onalytica in the ‘Machine to Machine: Top 100 Influencers and Brands.' Onalytica analyzed the online debate on M2M by looking at over 85,000 tweets to provide the most influential individuals and brands that drive the discussion. According to Onalytica the "analysis showed a very engaged community with a lot of interactive tweets. The M2M discussion seems to be more fragmented and driven by some of the major brands present in the...
    Machine Learning helps make complex systems more efficient. By applying advanced Machine Learning techniques such as Cognitive Fingerprinting, wind project operators can utilize these tools to learn from collected data, detect regular patterns, and optimize their own operations. In his session at 18th Cloud Expo, Stuart Gillen, Director of Business Development at SparkCognition, discussed how research has demonstrated the value of Machine Learning in delivering next generation analytics to impr...
    Data is the fuel that drives the machine learning algorithmic engines and ultimately provides the business value. In his session at Cloud Expo, Ed Featherston, a director and senior enterprise architect at Collaborative Consulting, will discuss the key considerations around quality, volume, timeliness, and pedigree that must be dealt with in order to properly fuel that engine.
    What happens when the different parts of a vehicle become smarter than the vehicle itself? As we move toward the era of smart everything, hundreds of entities in a vehicle that communicate with each other, the vehicle and external systems create a need for identity orchestration so that all entities work as a conglomerate. Much like an orchestra without a conductor, without the ability to secure, control, and connect the link between a vehicle’s head unit, devices, and systems and to manage the ...
    Virgil consists of an open-source encryption library, which implements Cryptographic Message Syntax (CMS) and Elliptic Curve Integrated Encryption Scheme (ECIES) (including RSA schema), a Key Management API, and a cloud-based Key Management Service (Virgil Keys). The Virgil Keys Service consists of a public key service and a private key escrow service. 

    Web Real-Time Communication APIs have quickly revolutionized what browsers are capable of. In addition to video and audio streams, we can now bi-directionally send arbitrary data over WebRTC's PeerConnection Data Channels. With the advent of Progressive Web Apps and new hardware APIs such as WebBluetooh and WebUSB, we can finally enable users to stitch together the Internet of Things directly from their browsers while communicating privately and securely in a decentralized way.
    Amazon has gradually rolled out parts of its IoT offerings, but these are just the tip of the iceberg. In addition to optimizing their backend AWS offerings, Amazon is laying the ground work to be a major force in IoT - especially in the connected home and office. In his session at @ThingsExpo, Chris Kocher, founder and managing director of Grey Heron, explained how Amazon is extending its reach to become a major force in IoT by building on its dominant cloud IoT platform, its Dash Button strat...
    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 sessi...
    For basic one-to-one voice or video calling solutions, WebRTC has proven to be a very powerful technology. Although WebRTC’s core functionality is to provide secure, real-time p2p media streaming, leveraging native platform features and server-side components brings up new communication capabilities for web and native mobile applications, allowing for advanced multi-user use cases such as video broadcasting, conferencing, and media recording.
    Fifty billion connected devices and still no winning protocols standards. HTTP, WebSockets, MQTT, and CoAP seem to be leading in the IoT protocol race at the moment but many more protocols are getting introduced on a regular basis. Each protocol has its pros and cons depending on the nature of the communications. Does there really need to be only one protocol to rule them all? Of course not. In his session at @ThingsExpo, Chris Matthieu, co-founder and CTO of Octoblu, walk you through how Oct...
    Major trends and emerging technologies – from virtual reality and IoT, to Big Data and algorithms – are helping organizations innovate in the digital era. However, to create real business value, IT must think beyond the ‘what’ of digital transformation to the ‘how’ to harness emerging trends, innovation and disruption. Architecture is the key that underpins and ties all these efforts together. In the digital age, it’s important to invest in architecture, extend the enterprise footprint to the cl...
    Almost everyone sees the potential of Internet of Things but how can businesses truly unlock that potential. The key will be in the ability to discover business insight in the midst of an ocean of Big Data generated from billions of embedded devices via Systems of Discover. Businesses will also need to ensure that they can sustain that insight by leveraging the cloud for global reach, scale and elasticity.
    One of biggest questions about Big Data is “How do we harness all that information for business use quickly and effectively?” Geographic Information Systems (GIS) or spatial technology is about more than making maps, but adding critical context and meaning to data of all types, coming from all different channels – even sensors. In his session at @ThingsExpo, William (Bill) Meehan, director of utility solutions for Esri, will take a closer look at the current state of spatial technology and ar...
    A critical component of any IoT project is what to do with all the data being generated. This data needs to be captured, processed, structured, and stored in a way to facilitate different kinds of queries. Traditional data warehouse and analytical systems are mature technologies that can be used to handle certain kinds of queries, but they are not always well suited to many problems, particularly when there is a need for real-time insights.
    Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Arch...
    The IoT industry is now at a crossroads, between the fast-paced innovation of technologies and the pending mass adoption by global enterprises. The complexity of combining rapidly evolving technologies and the need to establish practices for market acceleration pose a strong challenge to global enterprises as well as IoT vendors. In his session at @ThingsExpo, Clark Smith, senior product manager for Numerex, will discuss how Numerex, as an experienced, established IoT provider, has embraced a ...
    Everyone knows that truly innovative companies learn as they go along, pushing boundaries in response to market changes and demands. What's more of a mystery is how to balance innovation on a fresh platform built from scratch with the legacy tech stack, product suite and customers that continue to serve as the business' foundation. In his General Session at 19th Cloud Expo, Michael Chambliss, Head of Engineering at ReadyTalk, will discuss why and how ReadyTalk diverted from healthy revenue an...
    SYS-CON Events announced today that Hitrons Solutions will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Hitrons Solutions Inc. is distributor in the North American market for unique products and services of small and medium-size businesses, including cloud services and solutions, SEO marketing platforms, and mobile applications.