Db2

IBM Db2 is a relational database management system that is focused on transactional and warehousing workloads.

IBM Db2 was originally designed for mainframe environments (z/OS). The original code was later ported to Db2 for Linux, UNIX, and Windows (LUW). The main difference between IBM Db2 z/OS and LUW is the database administration, but basic operations are similar. Nowadays, it can support many other platforms including i, VSE, and VM.

History

IBM Db2 came after System R and improved the SEQUEL in System R to SQL. In 2017, IBM Db2 was announced which is the successor name of DB2 (introduced in the 1980s).

IBM Db2 has four different code bases. The main reason is that in the early development, IBM had to meet the requirements of different customers and support different hardware and operating systems.

Checkpoints

Fuzzy

IBM Db2 uses Algorithms for Recovery and Isolation Exploiting Semantics (ARIES), which adopts fuzzy checkpoint.

Compression

Naïve (Record-Level)

IBM Db2 supports Row compression and Value compression. Row compression, also known as deep compression, compress data by using shorter symbol strings to remove patterns of values that duplicate across rows. Value compression compress data by storing only one copy of the same value.

Concurrency Control

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. IBM Db2 supports OCC since version 9.5, and the history of 2PL, which was first invented by IBM, can be dated back to 1970s.

Data Model

Relational Document / XML

IBM Db2 is a relational database since it was first developed. It added the support of XML documents using pureXML® feature in the IBM Db2 9. To use pureXML, the column should be defined as the XML data type. The XML column can be queried and updated using XQuery and SQL statements.

Foreign Keys

Supported

IBM Db2 can define foreign key constraints during creating and altering table. With the definition of foreign key constraints, the database manager will maintain integrities automatically when one object refers another one.

Indexes

B+Tree

IBM Db2 uses B+tree as the underlying index data structures. There are three different kinds of nodes, root node, intermediate node, and leaf node in B+ tree. When finding a specific key value pair, IBM Db2 will follow the path from root to leaf.

Isolation Levels

Read Uncommitted Repeatable Read Cursor Stability

IBM 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.

Joins

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 interact with disks too often. To make the hash join works better, it is recommended to estimate and then set the memory large enough but without exceeding the memory limit.

Logging

Physiological Logging

IBM Db2 use Physiological logging which can be dated back to the famous paper ARIES in 1992, which was developed at IBM Research. ARIES uses Write-Ahead Logging with STEAL + NO FORCE policy.

Query Execution

Tuple-at-a-Time Model

IBM Db2 uses Iterator Model. There is a next function in every query plan operator. Every time the next function being called, it will either return a single tuple or null when all the tuples have been processed.

Query Interface

SQL Stored Procedures

IBM Db2 supports the majority of SQL standards. Also, it can support stored procedure which is a set of SQL statements with a dedicated name. Stored procedure can be invoked from command line and application program.

Storage Architecture

Disk-oriented

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.

Storage Model

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.

Stored Procedures

Supported

IBM 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.

System Architecture

Shared-Everything Shared-Disk

IBM Db2 pureScale environment provides IBM Db2 members the ability to access data stored on a shared disk.

Views

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.

Revision #16  | 

Compatible Systems

Derivative Systems

People Also Viewed

Db2 Logo
Website

http://ibm.com/software/data/db2/

Tech Docs

http://www-01.ibm.com/support/docview.wss?uid=swg27009474

Developer

IBM

Country of Origin

US

Start Year

1983

Project Type

Commercial

Written in

C, C++

Operating Systems

Linux, OS X, Windows, z/OS

Licenses

Proprietary

Wikipedia

https://en.wikipedia.org/wiki/IBM_Db2

Revision #16  | 

Compatible Systems

Derivative Systems

People Also Viewed