Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON and XML. The brand name was originally styled as DB2[3][4][5] until 2017,[6] when it changed to its present form. (In the early days, it was sometimes wrongly styled as DB/2 - in a false derivation from the operating system OS/2 [7].)
Developer(s) | IBM |
---|---|
Initial release | 1983[1] |
Stable release(s) | |
11.5.9[2]
/ 15 November 2023 | |
Written in | C, C++, assembly, Java |
Operating system | Linux, Unix-like, Windows. Historical support for OS/2 |
Platform | x86-64, x86, SPARC, IBM Power microprocessors |
Size | 1.6 GB |
Available in | English, Spanish, French, German, Russian, Japanese |
Type | RDBMS |
License | Proprietary commercial software, Proprietary EULA |
Website | www |
Developer(s) | IBM |
---|---|
Initial release | 1983 |
Stable release | 13.1
|
Written in | PL/X, C, C++, assembly |
Operating system | z/OS |
Platform | z/Architecture |
Available in | English |
Type | RDBMS |
License | Proprietary EULA |
Website | ibm |
Unlike other database vendors, IBM previously produced a platform-specific Db2 product for each of its major operating systems. In the 1990s IBM began producing a more unified Db2 with a mostly common code base for Linux, Unix and Windows. DB2 for System z and DB2 for IBM i, however, remained independent implementations and as a result, they use different drivers.[8]
DB2 traces its roots back to the beginning of the 1970s, when Edgar F. Codd, a researcher working for IBM, described the theory of relational databases, and in June 1970, published the model for data manipulation.[9]
In 1974, the IBM San Jose Research Center developed a related Database Management System (DBMS) called System R, to implement Codd's concepts.[10] A key development of the System R project was the Structured Query Language (SQL). To apply the relational model, Codd needed a relational-database language he named DSL/Alpha.[11] At the time, IBM didn't believe in the potential of Codd's ideas, leaving the implementation to a group of programmers not under Codd's supervision. This led to an inexact interpretation of Codd's relational model that matched only part of the prescriptions of the theory; the result was Structured English QUEry Language or SEQUEL.
When IBM released its first relational-database product, they wanted to have a commercial-quality sublanguage as well, so they overhauled SEQUEL and renamed the revised language Structured Query Language (SQL) to differentiate it from SEQUEL, and also because the acronym "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.[12]
IBM bought Metaphor Computer Systems to utilize their GUI interface and encapsulating SQL platform that had already been in use since the mid-80s.
In parallel with the development of SQL, IBM also developed Query by Example (QBE), the first graphical query language.
IBM's first commercial relational-database product, SQL/DS, was released for the DOS/VSE and VM/CMS operating systems in 1981. In 1976, IBM released Query by Example for the VM platform where the table-oriented front-end produced a linear-syntax language that drove transactions to its relational database.[13] Later, the QMF feature of DB2 produced real SQL, and brought the same "QBE" look and feel to DB2. The inspiration for the mainframe version of DB2's architecture came in part from IBM IMS, a hierarchical database, and its dedicated database-manipulation language, IBM DL/I.
The name DB2 (IBM Database 2), was first given to the Database Management System or DBMS in 1983 when IBM released DB2 on its MVS mainframe platform.[14]
For some years DB2, as a full-function DBMS, was exclusively available on IBM mainframes. Later, IBM brought DB2 to other platforms, including OS/2, UNIX, and MS Windows servers, and then Linux (including Linux on IBM Z) and PDAs. This process occurred through the 1990s. An implementation of DB2 is also available for z/VSE and z/VM. An earlier version of the code that would become DB2 LUW (Linux, Unix, Windows) was part of an Extended Edition component of OS/2 called Database Manager.
IBM extended the functionality of Database Manager a number of times, including the addition of distributed database functionality by means of Distributed Relational Database Architecture (DRDA) that allowed shared access to a database in a remote location on a LAN. (Note that DRDA is based on objects and protocols defined by Distributed Data Management Architecture (DDM).)
Eventually, IBM decided to rewrite the software completely. The new version of Database Manager were called DB2/2 and DB2/6000 respectively. Other versions of DB2, with different code bases, followed the same '/' naming convention and became DB2/400 (for the AS/400), DB2/VSE (for the DOS/VSE environment), and DB2/VM (for the VM operating system). IBM lawyers stopped this handy naming convention from being used and decided that all products needed to be called "product FOR platform" (for example, DB2 for OS/390). The next iteration of the mainframe and the server-based products was named DB2 Universal Database (or DB2 UDB).
In the mid-1990s, IBM released a clustered DB2 implementation called DB2 Parallel Edition, which initially ran on AIX. This edition allowed scalability by providing a shared-nothing architecture, in which a single large database is partitioned across multiple DB2 servers that communicate over a high-speed interconnect. This DB2 edition was eventually ported to all Linux, UNIX, and Windows (LUW) platforms, and was renamed to DB2 Extended Enterprise Edition (EEE). IBM now refers to this product as the Database Partitioning Feature (DPF) and bundles it with their flagship DB2 Enterprise product.
When Informix Corporation acquired Illustra and made their database engine an object-SQL DBMS by introducing their Universal Server, both Oracle Corporation and IBM followed suit by changing their database engines to be capable of object–relational extensions. In 2001, IBM bought Informix Software, and in the following years incorporated Informix technology into the DB2 product suite. DB2 can technically be considered to be an object–SQL DBMS.
In mid-2006, IBM announced "Viper", the codename for DB2 9 on both distributed platforms and z/OS. DB2 9 for z/OS was announced in early 2007. IBM claimed that the new DB2 was the first relational database to store XML "natively". Other enhancements include OLTP-related improvements for distributed platforms, business intelligence/data warehousing-related improvements for z/OS, more self-tuning and self-managing features, additional 64-bit exploitation (especially for virtual storage on z/OS), stored procedure performance enhancements for z/OS, and continued convergence of the SQL vocabularies between z/OS and distributed platforms.
In October 2007, IBM announced "Viper 2", the codename for DB2 9.5 on the distributed platforms. There were three key themes for the release, Simplified Management, Business Critical Reliability, and Agile XML development.
In June 2009, IBM announced "Cobra", the codename for DB2 9.7 for LUW.[15] DB2 9.7 added data compression for database indexes, temporary tables, and large objects. DB2 9.7 also supported native XML data in hash partitioning (database partitioning), range partitioning (table partitioning), and multi-dimensional clustering. These native XML features allow users to directly work with XML in data warehouse environments. DB2 9.7 also added several features that make it easier for Oracle Database users to work with DB2. These include support for the most commonly used SQL syntax, PL/SQL syntax, scripting syntax, and data types from Oracle Database. DB2 9.7 also enhanced its concurrency model to exhibit behavior that is familiar to users of Oracle Database and Microsoft SQL Server.
In October 2009, IBM introduced its second major release of the year when it announced DB2 pureScale. DB2 pureScale is a cluster database for non-mainframe platforms, suitable for online transaction processing (OLTP) workloads. IBM based the design of DB2 pureScale on the Parallel Sysplex implementation of DB2 data sharing on the mainframe. DB2 pureScale provides a fault-tolerant architecture and shared-disk storage. A DB2 pureScale system can grow to 128 database servers, and provides continuous availability and automatic load balancing.
In 2009, it was announced that DB2 can be an engine in MySQL. This allows users on the IBM i platform and users on other platforms to access these files through the MySQL interface. On IBM i and its predecessor OS/400, DB2 is tightly integrated into the operating system, and comes as part of the operating system. It provides journaling, triggers and other features.
In early 2012, IBM announced the next version of DB2, DB2 10.1 (code name Galileo) for Linux, UNIX, and Windows. DB2 10.1 contained a number of new data management capabilities including row and column access control which enables 'fine-grained' control of the database and multi-temperature data management that moves data to cost effective storage based on how "hot" or "cold" (how frequently the data is accessed) the data is. IBM also introduced "adaptive compression" capability in DB2 10.1, a new approach to compressing data tables.
In June 2013, IBM released DB2 10.5 (code name "Kepler").
On 12 April 2016, IBM announced DB2 LUW 11.1, and in June 2016, it was released.
In mid-2017, IBM re-branded its DB2 and dashDB product offerings and amended their names to "Db2".
On June 27, 2019, IBM released Db2 11.5, the AI Database. It added AI functionality to improve query performance as well as capabilities to facilitate AI application development.[16][17][18]
Db2 (now short for the former "Db2 for LUW") is a relational database that delivers advanced data management and analytics capabilities for transactional workloads. This operational database is designed to deliver high performance, actionable insights, data availability and reliability, and it is supported across Linux, Unix and Windows operating systems.
The Db2 database software includes advanced features such as in-memory technology (IBM BLU Acceleration), advanced management and development tools, storage optimization, workload management, actionable compression and continuous data availability (IBM pureScale).
In 1995, GA (general availability) of V4. It introduced "data sharing": several DB2 engines access the same data. Advantages: performance and availability (if one DB2 engine fails or is migrated to the next version).
In 1997, GA of V5. It added, e.g., online reorganization of tablespaces.
In 1999, GA of V6. It added object-relational support. "Objects" here mean data items longer than 32K (up to then the maximal length of a table row, more precisely a table record), such as images, videos, or text. DB2 could now store and handle such objects. Furthermore, it added trigger support.
In 2001, GA of V7. It added, e.g., dynamic allocation of data sets (~files on z/OS), and the ability to let utilities run on lists of tablespaces. Furthermore real-time statistics, scrollable cursors, and initial Unicode support.
In 2004, GA of V8. It added, e.g., 64-bit support. New index types (notably DPSI), recursive SQL. Internal catalog is converted to Unicode.
In 2007, GA of V9. It added, e.g., Trusted Context (a security feature), and "native XML" support.
In 2010, GA of V10. It added, e.g., Temporal Tables (e.g., row history), security features like separation of system and security administrators, and RCAC (row column access control).
In 2013, GA of V11. It added, e.g., JSON support.
In 2016, GA of V12. It added, e.g., RESTful services; and usage of AI to optimize the selection of the access path to the data, thus enhancing performance.
On May 31, 2022, IBM released Db2 13 for z/OS.[19]
"Data warehousing" was first mentioned in a 1988 IBM Systems Journal article entitled, "An Architecture for Business Information Systems."[20] This article illustrated the first use-case for data warehousing in a business setting as well as the results of its application.
Traditional transaction processing databases were not able to provide the insight business leaders needed to make data-informed decisions. A new approach was needed to aggregate and analyze data from multiple transactional sources to deliver new insights, uncover patterns, and find hidden relationships among the data. Db2 Warehouse, with its capabilities to normalize data from multiple sources, performs sophisticated analytic and statistical modeling, provides businesses these features at speed and scale.
Increases in computational power resulted in an explosion of data inside businesses generally and data warehouses specifically. Warehouses grew from being measured in GBs to TBs and PBs. As both the volume and variety of data grew, Db2 Warehouse adapted as well. Initially purposed for star and snowflake schemas, Db2 Warehouse now includes support for the following data types and analytical models, among others:
Db2 Warehouse uses Docker containers to run in multiple environments: on-premise, private cloud and a variety of public clouds, both managed and unmanaged. Db2 Warehouse can be deployed as software only, as an appliance and in Intel x86, Linux and mainframe platforms. Built upon IBM's Common SQL engine, Db2 Warehouse queries data from multiple sources—Oracle, Microsoft SQL Server, Teradata, open source, Netezza and others. Users write a query once and data returns from multiple sources quickly and efficiently.
Db2 on Cloud: Formerly named "dashDB for Transactions", Db2 on Cloud is a fully managed, cloud SQL database with a high-availability option featuring a 99.99 percent uptime SLA. Db2 on Cloud offers independent scaling of storage and compute, and rolling security updates.
Db2 on Cloud is deployable on both IBM Cloud and Amazon Web Services (AWS).
Key features include:
Db2 Hosted: Formally named "DB2 on Cloud", Db2 Hosted is an unmanaged, hosted version of Db2 on Cloud's transactional, SQL cloud database.
Key features:
Formerly named "dashDB for Analytics", Db2 Warehouse on Cloud is a fully managed, elastic, cloud data warehouse built for high-performance analytics and machine learning workloads.
Key features include:
In 2018, the IBM SQL product was renamed and is now known as IBM Db2 Big SQL (Big SQL). Big SQL is an enterprise-grade, hybrid ANSI-compliant SQL on the Hadoop engine delivering massively parallel processing (MPP) and advanced data query. Additional benefits include low latency, high performance, security, SQL compatibility and federation capabilities.
Big SQL offers a single database connection or query for disparate sources such as HDFS, RDMS, NoSQL databases, object stores and WebHDFS. Exploit Hive, Or to exploit Hbase and Spark and whether on the cloud, on premises or both, access data across Hadoop and relational data bases.
Users (data scientists and analysts) can run smarter ad hoc and complex queries supporting more concurrent users with less hardware compared to other SQL options for Hadoop.[citation needed] Big SQL provides an ANSI-compliant SQL parser to run queries from unstructured streaming data using new APIs.
Through the integration with the IBM Common SQL Engine, Big SQL was designed to work with all the Db2 family of offerings, as well as with the IBM Integrated Analytics System. Big SQL is a part of the IBM Hybrid Data Management Platform, a comprehensive IBM strategy for flexibility and portability, strong data integration and flexible licensing.
Db2 Event Store targets the needs of the Internet of things (IOT), industrial, telecommunications, financial services, online retail and other industries needing to perform real-time analytics on streamed high volume, high velocity data. It became publicly available in June 2017. It can store and analyze 250 billion events in a day with just 3 server nodes with its high speed data capture and analytics capabilities. The need to support AI and machine learning was envisioned from the start by including IBM Watson Studio into the product, and integrating Jupyter notebooks for collaborative app and model development. Typically combined with streaming tools, it provides persistent data by writing the data out to object storage in an open data format (Apache Parquet). Built on Spark, Db2 Event Store is compatible with Spark Machine Learning, Spark SQL, other open technologies, as well as the Db2 family Common SQL Engine and all languages supported – including Python, GO, JDBC, ODBC, and more.
In 1994, IBM renamed the integrated relational database of the OS/400 to DB2/400 to indicate comparable functionality to DB2 on other platforms.[23] Despite this name, it is not based on Db2 code, but instead it evolved from the IBM System/38 integrated database. The product is currently named IBM Db2 for i.[24]
Db2 for z/OS is available in its traditional product packaging,[29] or in the Value Unit Edition, which allows customers to instead pay a one-time charge.
Db2 also powers IBM InfoSphere Warehouse, which offers data warehouse capabilities. InfoSphere Warehouse is available for z/OS. It includes several BI features such as ETL, data mining, OLAP acceleration, and in-line analytics.
Db2 11.5 for Linux, UNIX and Windows, contains all of the functionality and tools offered in the prior generation of DB2 and InfoSphere Warehouse on Linux, UNIX and Windows.
IBM offers three editions: Db2 Community Edition, Standard Server Edition, and Advanced Server Edition.
IBM Db2 Community Edition is a free-to-download, free-to-use edition of the IBM Db2 database, which has both XML database and relational database management system features. It provides all core capabilities of Db2 but is limited to 4 virtual processor cores, 16 GB of instance memory, has no enterprise-level support, and no fix packs. There is no limit on the database size.[30]
On June 27, 2019, IBM released Db2 V11.5, a Db2 update designed to deliver enhancements to help automate data management, eliminate ETL, and support artificial intelligence data workloads. Along with the update, IBM unveiled streamlined offerings. The Db2 Community Edition replaces the Express-C and the Developer Community editions.
IBM Db2 Community edition, as of version 11.5.1, has no limit on the database size.[31] Some previous version 11.5 point releases imposed a limit of 100 GB on the database size. The database engine does not limit the number of concurrent user connections. A prior free Db2 version, the IBM DB2 Express-C, supported up to 16 GB RAM and two CPU cores.
On June 27, 2019, IBM announced a special free version of Db2 Database called Db2 Community edition. The Db2 Community edition was created for the 11.5 release of IBM Db2. The Db2 Community Edition replaced the previously free version of IBM Db2 known as DB2 Express-C.
On January 30, 2006, IBM announced a special free version of DB2 Express edition called DB2 Express-C. The DB2 Express-C edition was created for the 8.2 release of IBM Db2. After this Db2 Express-C was created for all new DB2 versions: 9.1 (codenamed "Viper"), 9.5 (codename "Viper 2"), 9.7 (codename "Cobra"), 10.01 (codename "Galileo"), 10.5 (codename "Kepler") and 11.1.
The Db2 Standard Edition is available as a perpetual software license for production and non-production use for up to 16 processor cores and 128 GB RAM with IBM support. For production use, Db2 Standard Edition can be licensed based on a Virtual Processor Core metric, wherein it is licensed by the total count of processor cores in a non-partitioned physical server, or virtual cores assigned to a virtual server. For non-production use, Db2 Standard Edition can be licensed based on the total count of authorized users.
The Db2 Advanced Edition is available only as a component of the IBM Hybrid Data Management Platform (HDMP). Within HDMP, Db2 is available both as a perpetual software license AND a monthly subscription for unrestricted production and non-production use with premium IBM support. For both HDMP perpetual license and subscription offerings, FlexPoints need to be bought. FlexPoints are generic licensing credits that can be used to deploy any Db2-family software product or cloud service offering.
Db2 Advanced Edition offers these benefits:
IBM has developed many Db2 versions under a code name, and documentation can be related to that name
Db2
Db2 for z/OS
Db2 can be administered from either the command-line or a GUI. The command-line interface requires more knowledge of the product but can be more easily scripted and automated. The GUI is a multi-platform Java client that contains a variety of wizards suitable for novice users. Db2 supports both SQL and XQuery. DB2 has a native implementation of XML data storage, where XML data is stored as XML (not as relational data or CLOB data) for faster access using XQuery.
Db2 has APIs for Rexx, PL/I, COBOL, RPG, Fortran, C++, C, Delphi, .NET CLI, Java, Python, Perl, PHP, Ruby, and many other programming languages. Db2 also supports integration into the Eclipse and Visual Studio integrated development environments.
pureQuery is IBM's data access platform focused on applications that access data. pureQuery supports both Java and .NET. pureQuery provides access to data in databases and in-memory Java objects via its tools, APIs, and runtime environment as delivered in IBM Data Studio Developer and IBM Data Studio pureQuery Runtime.[32]
An important feature of Db2 computer programs is error handling. The SQL communications area (SQLCA) structure was once used exclusively within a Db2 program to return error information to the application program after every SQL statement was executed. The primary, but not singularly useful, error diagnostic is held in the field SQLCODE within the SQLCA block.
The SQL return code values are:
+100
, which means no rows found.-911
, which means a lock timeout (or deadlock) has occurred, triggering a rollback.Later versions of Db2 added functionality and complexity to the execution of SQL. Multiple errors or warnings could be returned by the execution of an SQL statement; it may, for example, have initiated a database trigger and other SQL statements. Instead of the original SQLCA, error information should now be retrieved by successive executions of a GET DIAGNOSTICS statement.
See SQL return codes for a more comprehensive list of common SQLCODEs.
DB2 for MVS (later OS/390) in 1983
Since the introduction of DB2 in 1983, IBM ...[unreliable source?]
relational data base management ... I.B.M.'s DB2
Since the 1970s, when IBM Research invented the Relational Model and the Structured Query Language (SQL), IBM has developed a complete family of RDBMS software. Development started on mainframe platforms such as Virtual Machine (VM), Virtual Storage Extended (VSE), and Multiple Virtual Storage (MVS). In 1983, DB2 for MVS Version 1 was born. "DB2" was used to indicate a shift from hierarchical databases, like the Information Management System (IMS) popular at the time, to the new relational databases.
Db2 for z/OS is a relational database that you can use to maintain large volumes of data with a high level of availability, security, and performance.
IBM z/VSE V6.2 is capable as of December 1, 2017, when used in .... Db2 Server for VSE and VM and Db2 Server for VSE Client Edition