Db2

View Current Viewing Revision #8 from 12/06/2018 8:02 p.m.

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.

History

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

Foreign Keys

Supported

Db2 allows users to define foreign keys. It is defined by FOREIGN KEY clause and the REFERENCES clause in ALTER TABLE and CREATE TABLE statements.

Checkpoints

Non-Blocking Blocking

IBM Db2 can take a backup using the BACKUP command. We can use the ONLINE parameter to control online or offline backup. The default option is offline, and we should enable logarchmeth1 if we want to use online backup.

Data Model

Relational Document / XML

In the beginning, Db2 is a relational database management system. In recent versions, the pureXML® feature allows customers to use XML data type.

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

Storage Architecture

Disk-oriented

Hardware Acceleration

Custom

Db2 achieves hardware compression for log archive files on AIX® and backup images using the nest accelerator NX842 of POWER 7+ and POWER 8 processors, which are developed by IBM.

Joins

Nested Loop Join Hash Join Sort-Merge Join

Db2 can support three different kinds of join algorithms, which are nested-loop join, merge join, and hash join.

Indexes

B+Tree

Db2 provides the index storage using B plus tree.

Concurrency Control

Multi-version Concurrency Control (MVCC)

Db2 uses multi-version concurrency control under cursor stability isolation level, which locks any row being accessed during a transaction while the cursor is on that row.

Views

Materialized Views

Db2 can create views by the CREATE VIEW command.

Query Compilation

JIT Compilation

Do not find enough information

Storage Model

N-ary Storage Model (Row/Record)

Query Interface

Stored Procedures

Db2 supports a wide range of SQL standards. It can also support stored procedure which is a set of SQL statements with a dedicated name.

Stored Procedures

Supported

Db2 supports three different kinds of Stored Procedures, which are external stored procedures, external SQL procedures, and native SQL procedures. The latter two are written entirely in SQL while the first one can be written in a host language.

Isolation Levels

Read Uncommitted Repeatable Read Cursor Stability

Db2 supports four different kinds of isolation level, which are Repeatable read (RR), Read stability (RS), Cursor stability (CS) and Uncommitted read (UR). The default level is cursor stability (CS).

Revision #8 | Updated 12/06/2018 8:02 p.m.