Differences Between Informix and DB2: An In-Depth Comparison

In the vast ocean of database management systems (DBMS), IBM's Informix and DB2 stand out. Both are powerful RDBMS solutions, but they diverge in several key areas. This article explores the differences between these two prominent systems, offering insights for database administrators and developers as they consider their options.

Overview and Platforms

Informix is a robust, high-performance relational database management system. It runs on a variety of platforms, including Windows and Unix/Linux operating systems on different processors. Despite the diversity of platforms, the core engine code remains consistent across all environments. The threading libray, however, varies slightly between Windows and Unix/Linux systems.

DB2, on the other hand, is a more complex ecosystem of different products based on independent code bases, each tailored to specific platforms. For instance, DB2 LUW (Linux, Unix, Windows) shares a single code base, though its threading library aligns with its platform's implementation. Meanwhile, the version of DB2 running on mainframe platforms like z/OS has a separate and slightly different code base, with some features tailored to the mainframe environment.

Excellence in Performance and Features

Both Informix and DB2 are renowned for their industry-leading performance and features. They offer similar levels of SQL support, though there are a few notable differences. Each has its own stored procedure language, with DB2 providing extensions to facilitate the migration of Oracle PL/SQL procedures to DB2. Additionally, Informix supports the creation of UDRs (User Defined Routines) in both C and Java.

DB2 Features

Temporal SQL Extensions: DB2 supports Temporal SQL extensions, which store and manage historical data. Informix, in contrast, does not support this feature. XML Data Support: DB2 has strong XML data support, with excellent integration with relational data. JSON Data Handling: DB2 offers some JSON data support. It is worth noting that Informix, while also supporting JSON, has more extensive and integrated support for BSON data. Blue Warehouse: DB2 Blue, also known as DB2 Warehouse, is a partially in-memory columnar query accelerator capable of processing complex queries up to 75 times faster than the base DB2 engine.

Informix Features

JSON/BSON Data Integration: Informix supports JSON and BSON data with full integration into relational tables. BSON can be used to create MongoDB-like collections or stored in relational tables. Informix is more efficient with BSON when processing. Indexing and Referential Constraints: BSON fields in Informix can be indexed and used as the source or target for referential constraints. Table Distribution and Sharding: Informix supports distributed tables across multiple server instances with full sharding support, including automatic data placement in the correct shard. MongoDB Protocol Support: Informix supports the MongoDB Wire Listener Protocol and client API, allowing seamless integration with MongoDB-compatible queries and statements. Geospatial and Timeseries Data: Informix handles geospatial and timeseries data with optimized storage methods, reducing storage requirements significantly compared to relational solutions. Timeseries elements in Informix can contain both BSON and geospatial data. Extendable ORDBMS: Informix is highly extensible, with many “built-in” data types implemented as user-defined types (UDTs). Function overloading is also supported, making it highly flexible. Disaster Survival: Informix offers a variety of disaster recovery strategies, including HDR (High Availability Data Replication), SDS (Shard Disk Secondary), RS (Remote Secondary), and ER (Enterprise Replication). Uninterruptable Transactions: Transactions initiated by clients connected to writable secondary servers will continue without interruption, even if the primary server fails and another server takes over.

Conclusion

Both Informix and DB2 excel in their niches, offering distinct advantages based on specific requirements. DB2, with its strong integration with XML and JSON data, and the Blue Warehouse capability, is well-suited for complex data warehouse scenarios. Informix, on the other hand, provides unparalleled flexibility with its support for geospatial, timeseries, and extensible data types, making it a preferred choice for environments where such features are crucial.

Key Features

DB2 Features:

Temporal SQL Extensions Strong XML Data Integration JSON Data Support Blue Warehouse (DB2 Warehouse)

Informix Features:

JSON/BSON Integration BSON Indexing and Constraints Table Distribution and Sharding MongoDB Protocol Support Geospatial and Timeseries Data Extended ORDBMS Capabilities Disaster Recovery Options: HDR, SDS, RS, ER Uninterruptable Transactions