Views

Materialized Views

Db2 can create views by the CREATE VIEW command.

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.

System Architecture

Shared-Disk

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

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.

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.

Query Execution

Tuple-at-a-Time Model

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.

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

Storage Model

N-ary Storage Model (Row/Record)

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.

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.

Storage Organization

Heaps

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.

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.

Query Compilation

JIT Compilation

Do not find enough information

Indexes

B+Tree

Db2 provides the index storage using B plus tree.

Storage Architecture

Disk-oriented