Db2

What is Db2?

Db2 is a family of database management system (DBMS) products from IBM that serve several different operating system (OS) platforms. Used by organizations of all sizes, Db2 provides a data platform for both transactional and analytical operations, as well as continuous availability of data to keep transactional workflows and analytics operating efficiently.

In addition to being a relational DBMS, Db2 also offers integrated support for several NoSQL capabilities, including XML, graph store and JavaScript Object Notation, or JSON.

What is a Db2 database and how does it work?

A Db2 database is a group of data treated collectively as a unit. A database is a large structured set of persistent data, and its purpose is to store, retrieve and manipulate related information.

A database administrator (DBA) uses Db2, which is DBMS or database server, to create and use Db2 databases. The Db2 DBMS operates as the server to manage data in databases across a multiuser environment, enabling many concurrent users to access the same data simultaneously. The Db2 DBMS also prevents unauthorized access, provides utilities for backing up and recovering data and offers performance tools and data management capabilities.

Db2 databases have logical structures and physical structures which the DBMS manages separately. The physical storage of data can be managed without affecting the access to logical storage structures. Db2 databases are created using Data Definition Language, or DDL, commands and are composed of tablespaces, tables with rows and columns, views, indexes, stored procedures and other supporting structures.

Once created, a DBA or developer can use a Db2 database and its underlying structures to create, read, update and delete data to support an organization's business requirements.

What are the advantages of Db2?

Db2 offers many advanced features for improving data and database management, including the following:

• Actionable compression can deliver storage space savings without sacrificing performance. Many query predicates can be evaluated without having to decompress the data.
• Hybrid transaction analytical processing, or HTAP, performance is accelerated by the Db2 BLU column store with single instruction, multiple data, or SIMD, exploitation and data-skipping technology on LUW platforms and by the IBM Db2 Analytics Accelerator on z/OS.
• A DBA can build a temporal database using Db2 to enable system and business changes to be captured, maintained and queried. This enables organizations to store information relating to past, present and future time, as well as use time-travel queries to view past and future data states.
• AI and machine learning (ML) capabilities, including the augmented data explorer, deliver natural language query capabilities, a ML query optimizer, and a hybrid data management platform to enable seamless sharing of structured, unstructured and semistructured data.
• Choice of deployment model includes on-premises, hosted cloud deployment or managed cloud deployment.
• Db2 on LUW platforms offer an Oracle SQL compatibility option.
• Db2 for z/OS enables organizations to run mixed workloads with exceptional scalability, high performance and round-the-clock availability.

What are the disadvantages of Db2?

Being relational, the disadvantages common to the RDBMS type apply to Db2 as much as any other RDBMS. These include the following:

• An impedance mismatch between object-oriented (OO) and relational means that object relational mapping is required for OO application programs, such as Java, to access the data.
• The relational model requires a rigid schema that does not necessarily fit with some types of modern development. NoSQL database systems cover the niche areas for which the RDBMS is not well-suited.
• Db2 does not scale as well horizontally as NoSQL because of the consistency model it uses, but it can scale vertically by adding CPU and memory.
• Because there are more users of database systems like Oracle and Microsoft SQL Server, it can be difficult to find talented and knowledgeable Db2 professionals.

Db2 supported platforms

In addition to its offerings for the mainframe, z/OS and VM OSes and its midrange i Series systems -- formerly AS/400 -- IBM offers Db2 products for a cross-platform spectrum that includes Unix-based IBM AIX and Linux and Microsoft Windows 10 and earlier systems.

Db2 is available on the following primary platforms:

• workstation, with Db2 for Linux, Unix and Windows (LUW);
• midrange, with Db2 for iSeries; and
• mainframe, with Db2 for z/OS and VM/VSE.

Each of these platforms is based on a different code base, meaning there will be differences in the way a DBA uses and manages Db2 based on the primary platform being used. Although the Db2 database engine does not rely on a common code base across the three primary platforms, it does boast remarkable SQL compatibility among the platforms. This means that developers can write SQL code that works on one platform and it is likely to work on the others with little or no changes.

Developers can access Db2 databases from application programs using embedded SQL statements in C, C++, COBOL, Fortran and REXX. They can also use the Db2 Call Level Interface, Microsoft's Open Database Connectivity, IBM Data Server Provider for Microsoft ADO.NET applications, SQLJ and Java Database Connectivity, or JDBC API for Java applications. They can develop web applications using Python, PHP scripting language and Ruby on Rails. A developer can also create applications that use REST APIs to access, interact with and exchange data with Db2.

Db2 editions and features

The mainframe and midrange versions of Db2 are not sold in editions. Although earlier versions of Db2 for LUW offered as many as six different editions, the latest version, 11.5.5, offers only two editions. This makes things easier because the editions are not restricted by features of the DBMS, but by the amount of hardware it can support:

• Db2 Standard Edition is a full-featured version of Db2 that is restricted to 16 virtual processor cores and 128 GB of memory. This is similar to what IBM used to call the Advanced Workgroup Server Edition.
• Db2 Advanced Edition is a full-featured version of Db2 with no hardware restrictions.

There is also a free Community Edition of Db2 that admins and DBAs can use for both production and nonproduction work. The Community Edition is limited to four cores, 16 GB of memory and 100 GB of storage per database.

IBM also offers Db2 on Cloud, which is a fully managed SQL cloud database with a dedicated operations team, point-in-time recovery, high-availability disaster recovery technology with multizone region support and independent scaling to protect enterprise applications. It provides an enterprise-ready cloud RDBMS for mission-critical workloads using a pay-as-you-go model for scalable storage and compute.

IBM offers a free cloud instance of Db2 that includes most Db2 features via its Lite Plan. Anyone can use this version simply by creating an IBM ID and signing up. The only limit is a maximum of 200 MB of data.

For analytical requirements, IBM Db2 Warehouse on Cloud is a fully managed, elastic cloud data warehouse that delivers independent scaling of storage and compute. The Db2 Warehouse is a columnar-organized, in-memory data warehouse designed for complex analytics and extreme concurrency with advanced automation features that focus on delivery instead of administration. In addition, it enables DBAs to train and run machine learning models directly in the warehouse engine, using Structured Query Language (SQL), Python or R.

Db2 is also embedded in IBM's PureData appliances for analytics, as well as on the IBM Cloud Pak for Data platform, either as an add-on or an included data source service.

The history of Db2

Db2 goes all the way back to 1974 and the System R database system research project at IBM's San Jose Research Laboratory, which is notable as the first implementation of SQL.

Relational databases have made profound changes to the computing industry over the years. Oracle released its first commercial RDBMS in 1979 and IBM followed in 1981 with SQL/DS, which was later renamed DB2 for VM/VSE.

DB2 Version 1. The 1980s were a busy time for IBM, as it provided several release updates. DB2 Version 1 Release 1 for MVS became generally available (GA) in April 1985 and DB2 Version 1 Release 2 became GA in March 1986 -- only a month after it was announced. The following April, IBM released the OS/2 operating system and DB2 Version 1 Release 3 became GA in June 1987. This release introduced date data types.

DB2 Version 2. IBM delivered DB2 Version 2 Release 1 (V2R1) in September 1988. This was a significant release in the history of DB2 for MVS, as it could support mission-critical, transaction processing workloads. V2R1 also introduced declarative Referential Integrity (RI) constraints.

IBM announced DB2 Version 2 Release 2 on October 4, 1988, and it became GA the following September. This new version added distributed database support.

In 1989, IBM released OS/2 Extended Edition 1.0, which included a database engine simply called Database Manager. This later became DB2 for Common Server, and then Db2 for Linux, Unix and Windows (LUW).

DB2 Version 2 Release 3 became generally available on October 25, 1991. The OS/2 DBM was released as DB2/2 in 1993 and the DB2/6000 was released for AIX shortly after the same year.

DB2 Version 3. Between 1993 and 1999, IBM released three new versions of DB2, starting with Version 3, which became GA in December 1993. This version improved buffer pool options and parallel processing. In 1995, IBM released DB2 for Common Server. This version originally supported OS/2 Warp and AIX and eventually Windows NT and several other Unix variants.

DB2 Version 4. Released in November 1995, this new version delivered improved indexing, data sharing, stored procedures, and improved parallelism and performance.

DB2 Version 5. In June 1997, DB2 Version 5 became GA. It was the first DB2 version to be referred to as DB2 for OS/390 rather than DB2 for MVS. This version boasted statement caching, reoptimization and improved online administration.

DB2 Version 6. In June 1999, IBM released DB2 Version 6 and introduced the Universal Database term to the DB2 moniker. The official name of the mainframe product is now DB2 Universal Database for OS/390. In the late 90s, IBM also rebranded SQL/DS as DB2 for VM and VSE.

DB2 Version 7. This new version, which was released in March 2001, offered Unicode support, improved utilities and new SQL functionality such as scrollable cursors, limited FETCH and row expressions.

DB2 Version 8. Released in March 2004, this was the most significant and feature-laden version of DB2 ever. It had more new lines of code than DB2 V1R1 had total lines of code and delivered features like long SQL names, online schema changes, row-level security and many SQL improvements.

DB2 9 for z/OS. Released in March 2007, IBM dropped the "V" from its name. This release delivered improved online management and administration capabilities, XML and binary data types, and new and improved SQL functionality.

DB2 10. Released November 2010, this update provided temporal database support and many improvements to SQL, performance and security.

DB2 11. Released in November 2013, DB2 11 delivered performance, new administrative capabilities, and programmer productivity improvements. In 2017, IBM rebranded DB2 as Db2. Db2 11.5.5 released in November 2020 is the current version of the LUW offering.

Db2 12. Released in October 2016, this is the current version of the mainframe offering. It introduced continuous delivery of new functionality to Db2.

Learn how graph databases differ from relational databases and when to use each database.