IBM DB2 is a relational database focus on transactional and warehousing workloads. It is built on top of many Engine dispatchable units (EDUs), which control the activity inside the database. In 2017, IBM Db2 was announced which is the successor name of DB2 (introduced in the 1980s). Recent years, Db2 started to support non-relational structures like JSON and XML. It can support many platforms including Linux, UNIX, Windows, z/OS, i, VSE, and VM.
DB2 dates back to early of the 1970s. At that time, IBM researcher Edgar F. Codd described relational databases theory and published the famous paper “A Relational Model of Data for Large Shared Data Banks.”
In 1974, System R, which implemented Codd’s concepts, was developed by a team in IBM. SQL was the critical development of System R.
In the mid-1990s, DB2 Parallel Edition was released which provided scalability by shared-nothing architecture.
In the mid-2006 and October 2007, “Viper” and “Viper 2” were announced, which is the codename for DB2 9 and DB2 9.5.
In June 2009, “Cobra” DB2 9.7 was released. “Cobra” added many exciting features including temporary tables, large objects and data compression for database indexes. In October of the same year, DB2 pureScale was announced, which was a database cluster solution suitable for Online transaction processing (OLTP) workloads. Again in the same year, IBM said that DB2 could be an engine in MySQL.
In early 2012, “Galileo” DB2 10.1 was announced, which can support Linux, UNIX, and Windows. In mid-2017, “Db2” replaces the name of “DB2”.
Virtual Views Materialized Views
IBM Db2 supports both views and materialized views. For materialized views, it can be specified maintaining by system clause or user clause.
Nested Loop Join Hash Join Sort-Merge Join
IBM Db2 can support three different kinds of join algorithms, which are nested-loop join, merge join, and hash join. Hash join has the best performance among the three algorithms if memory is large enough to ensure hash loops will not overflow to disk. To tune the hash join performance, it is better estimating and setting the memory large enough but without exceeding the memory limit.
IBM Db2 use disk as the primary storage location of data. Therefore, it is a disk-oriented DBMS. It is worth to mention that IBM Db2 can support multiple buffer pools, which is the connection between volatile and non-volatile storage. Multiple buffer pools have many advantages. For example, it can accelerate queries that need temporary storage by storing temporary data into separate buffer pools. Also, the data of some seldom-used applications can be stored separately to avoid flushing some hot data.
Read Uncommitted Repeatable Read Cursor Stability
Db2 supports four different kinds of isolation level, which are Repeatable read, Read stability, Cursor stability and Uncommitted read. The default level is cursor stability.
Db2 supports three different kinds of Stored Procedures, which are external stored procedures, external SQL procedures (execute like external stored procedures), and native SQL procedures (create by a single SQL statement). The latter two are written entirely in SQL while the first one can be written in a host language.
Decomposition Storage Model (Columnar)
IBM Db2 stores the values of a single attribute continuously in disk, i.e. DECOMPOSITION STORAGE MODEL (DSM). DSM is suitable for OLAP workloads as queries usually perform large scan over a subset of the attributes.
Two-Phase Locking (Deadlock Prevention) Optimistic Concurrency Control (OCC)
IBM Db2 supports OPTIMISTIC CONCURRENCY CONTROL (OCC) and Two-Phase Locking (2PL). OCC believes that values are unlikely to change before the update or deletion operations but will incur more retry logic in the application. Db2 supports OCC since version 9.5, and the history of 2PL, which was first invented by IBM, can be dated back to 1970s.
http://ibm.com/software/data/db2/
http://www-01.ibm.com/support/docview.wss?uid=swg27009474
IBM
1983