Comparing ODBC vs. Db2 for IBM i: Benefits and Use Cases

The principal use case for PHP is to provision data on the web safely and securely. For most IBM i shops, that means Db2 Data, Db2 Services, and possibly program responses generated through the heavy introspection of RPG and COBOL business logic. There are a few ways to access Db2 data from a PHP application. 

In this article I’ll discuss three of these drivers and some of the pros, cons, and reasons to consider changing course. These include the traditional ibm_db2 extension, ODBC and PDO/ODBC.

Before We Start: A Comparison Disclaimer

Let me start by saying that I originally began this process by doing some informal testing for performance and, after some semi-exhaustive research, I cannot say that any solution performs better or worse than the others. All of these drivers deliver amazing response times, so we’re eliminating performance from the discussion right away. 

That’s not to say there may be some aspect or corner case that yields better results, but this article is focusing on the developer aspect and where the future lies.   

What is Db2?

Db2, or Database 2, is a set of databases created to provide a single engine for database administrators, developers, and enterprise architects to build critical applications - including PHP applications - across any cloud while simplifying development and enabling faster decisions.

What Is ibm_db2?

Ibm_db2 is what we call a PECL extension and is maintained largely by the community. The extension is well used, tested, and mature so you know it can be trusted.

This extension is the first I came to know and understand when building PHP applications running on IBM i; it’s also  also where many of the Zend customers appear to be, as well.  For applications that will run on IBM i, this is a great way to go.

What is ODBC?

ODBC, or Open Database Connectivity, is an application programming interface that accesses database management system. A standard developed by Microsoft in 1992, Open Database Connectivity (ODBC) became a popular way to connect applications to databases and ushered in the era of horizontal scaling.

Decoupling the application from the database at the hardware level was a significant game changer for many shops looking to distribute workload. Like any new technology it became both a blessing through its amazing flexibility and a curse and there were now more things to keep track of.  

How Does ODBC Work on IBM i?

On IBM i, we tend to not worry about these things much, as we typically have everything we need on a single box. For some time, folks were looking at ODBC as a mechanism to connect to off platform databases, and reaching out to remote systems can come in handy at times. But IBM has implemented ODBC in the PASE environment so that all open source languages can communicate with Db2 for IBM i. IBM’s Mark Irish wrote a nice article about the implementation of ODBC in PASE.

ODBC in PASE makes a lot of sense from a strategic standpoint. As IBM continues to bring new technologies to IBM i the existing ODBC driver set can provide immediate database capabilities. This saves IBM the extra work of creating a customer ibm_db2 driver for other technologies. Check out IBM’s documentation for a great reference in getting ODBC connected to your IBM i.

What Are PDO/ODBC?

PDO/ODBC builds upon the flexibility of ODBC. This technology leverages the abstraction of ODBC and goes one better to make virtually all ODBC connections ubiquitous in an object-oriented model. 

This level of abstraction really helps applications that need to connect to disparate data sources or for applications that can be deployed using different databases.  Both ODBC variants are cross platform solutions, which is very helpful when PHP developers prefer local development.  

Get Started With the PDO_ODBC PHP Extension on IBM i

Zend is providing the PDO_ODBC PHP driver for our customers who want to move to the new unixODBC driver. Use this article for tips and directions to get started

View Documentation

ODBC vs. Db2 for IBM i

To the best of my knowledge, IBM has not come up with a laptop implementation of IBM i. This means that unless you are using SEU or Vim/nano, you are probably doing development on a Windows PC, Mac or Linux distro. Using the ibm_db2 extension would require the developer to push the code to the server just to refresh the page or make a significant investment in IBM’s Db2_connect product to distribute the DRDA protocol to the workstation. 

Local development and testing of a web application can accelerate development time and provide a more comfortable coding experience. Using one of the ODBC variants can means a local PC could connect to Db2 and the same code that would run on the server could run on the PC. The only difference would be the connection string which is easily tweaked with an IF test to see where it is running. Installations leveraging version control like git facilitate the migration of code locally and pushing it to the server.

Deciding Which Db2 for IBM i Driver to Use

Ultimately which Db2 driver to use depends on your goals. I’m offering the ODBC approach as local development is quite attractive with bringing on new developers from the greater PHP community to IBM i. 

While the ibm_db2 driver works well, it lacks the cross-platform ubiquity provided through the ODBC connectivity options. Once we’ve sorted local vs. remote development, then we need to consider the frameworks in use. Many PHP frameworks support ODBC or PDO/ODBC connectivity and will run quite well with these.  Finding the ibm_db2 framework drivers is sometimes a challenge.

Final Thoughts: Take a Systemic Approach

Regardless of the driver, a systematic approach to using the database is essential for scaling workload.  Recently our services team helped a customer who was using 4 different drivers to access Db2 resources.  This solution created conflicts and performance issues that held the PHP application back from scaling properly.  After a few days of aligning under a single driver, the performance came back in line. 

But there is a fringe benefit that, in my opinion, is far more valuable: consistency. By using a single database approach, the application developers and the maintenance programmers were able to leverage skills that improved development time.  

As they worked on an application, they no longer needed to figure which database access they had to use for this application or that.  Also, confidence in the execution of the database grew and the “@” signs suppressing database errors began to fall away in the remainder of the application set.

Ultimately the choice is yours, but I am betting on the ODBC connection as IBM’s North Star for Db2 access for the foreseeable future.

Ready to Modernize Your IBM i App?

Zend experts can help. With a full range of consultative and hands-on professional services, Zend can help you achieve your vision on time and on budget.

Professional Services  About IBM i ModernizatioN

Additional Resources

• Case Study - AgVantage Software Modernizes Front-End System With ZendPHP on IBM i
• Datasheet - PHP IBM i Modernization Services
• Blog - IBM i 7.5 for PHP: Exploring Db2 Services
• Blog - IBM i Modernization: Modernizing Smartly and On a Budget
• Blog - Using ZendPHP, PHP-FPM, and Nginx on IBM i
• Blog - Exploring ZendHQ for IBM i