IBM Db2 Database: A Guide to its Features and Deadlock Management

This guide provides an overview of IBM Db2, a robust relational database management system (RDBMS). We will explore its features, focusing on its deadlock management capabilities, including the internal resource lock manager (IRLM) and different lock classifications. Ideal for database administrators and developers working with IBM Db2.

DB2 Interview Questions and Answers

What is DB2?

Question 1: What is DB2?

DB2 (IBM Db2) is a relational database management system (RDBMS) from IBM. It's used to store, manage, and retrieve data efficiently. It supports SQL (Structured Query Language) for data manipulation.

Deadlock Management in DB2

Question 2: Deadlock Management in DB2

DB2 uses its internal resource lock manager (IRLM) to handle concurrency and deadlocks. The IRLM manages locks and detects and resolves deadlocks when they occur.

Classifying Locks in DB2

Question 3: Classifying Locks in DB2

Locks can be classified by:

  • Granularity: Table space, table, or page level.
  • Mode: Exclusive, shared, update.
  • Duration: Short-term or long-term.

Levels of Locking in DB2

Question 4: Levels of Locking in DB2

Locks can be placed at the table space, table, or page level. The granularity of locking affects both concurrency and performance; finer-grained locking (page level) provides better concurrency but might be more resource-intensive.

Page Locks

Question 5: Types of Page Locks

Three types of page locks in DB2:

  • Exclusive: Only one process can access the page.
  • Shared: Multiple processes can read the page.
  • Update: A process can read and later update the page.

`COMMIT` in DB2

Question 6: `COMMIT` in DB2

The `COMMIT` command in DB2 permanently saves changes made during a transaction.

Data Types in DB2

Question 7: Data Types in DB2

Common DB2 data types:

  • SMALLINT
  • INTEGER
  • FLOAT
  • DECIMAL
  • CHAR
  • VARCHAR
  • DATE
  • TIME
  • TIMESTAMP

Null Indicator Variable

Question 8: Picture Clause for Null Indicator

S9(4) COMP is often used as the picture clause for a null indicator variable in DB2.

DB2 Optimizer

Question 9: DB2 Optimizer

The DB2 optimizer selects the most efficient way to execute SQL statements. It analyzes queries and chooses appropriate access paths, improving performance.

SQL Execution Component

Question 10: Component for SQL Execution

The Database Services component in DB2 executes SQL statements.

System Services Component

Question 11: DB2 Startup and Shutdown

The System Services component handles DB2 startup and shutdown operations.

SQLCA (SQL Communication Area)

Question 12: SQLCA (SQL Communication Area)

SQLCA is a structure that contains information about the status of the last SQL statement executed. It is used in embedded SQL programs.

SQLCA Size

Question 13: Maximum Length of SQLCA

The maximum length of the SQLCA is 136 bytes.

SQLCA Fields

Question 14: SQLCA Fields

Important SQLCA fields:

  • SQLCODE: Return code indicating success or error.
  • SQLERRM: Error message text.
  • SQLERRD: Array containing diagnostic information.

CHECK Constraint

Question 15: CHECK Constraint

A CHECK constraint in DB2 enforces data integrity by restricting the values allowed in a column. It helps to prevent invalid data from being stored.

DB2 Bind

Question 16: DB2 Bind

DB2 bind creates and stores access plans for SQL statements, improving query performance.

DBRM (Database Request Module)

Question 17: DBRM (Database Request Module)

A DBRM is a pre-compiled module containing SQL statements. It is used in the DB2 bind process.

Buffer Pool

Question 18: Buffer Pool

The buffer pool is a portion of memory used by DB2 to cache data from disk, improving performance by reducing the need for disk I/O operations.

Data Manager

Question 19: Data Manager

The data manager component in DB2 handles physical storage, logging, and locking.

Storage Groups

Question 20: Storage Groups (STOGROUP)

A storage group defines a set of disk paths used to store DB2 data. Table spaces are assigned to storage groups.

Predicates

Question 21: Predicates

Predicates (conditions in WHERE clauses) improve query performance by reducing the amount of data that needs to be processed.

Data Type Storage Lengths

Question 22-24: Data Type Storage Lengths

Storage lengths (bytes):

  • TIME: 3
  • DATE: 4
  • TIMESTAMP: 10

DCLGEN (Declaration Generator)

Question 25: DCLGEN (Declaration Generator)

DCLGEN generates host language declarations (e.g., COBOL, C) for DB2 tables and views, simplifying the process of interacting with the database.

Default Buffer Pool Page Size

Question 26: Default Buffer Pool Page Size

The default page size for DB2 buffer pools is 4KB.

DB2 Optimizer (Continued)

Question 27: DB2 Optimizer (Continued)

The DB2 optimizer is crucial for database performance. It chooses efficient access paths and execution plans for SQL statements.

Concurrency in DB2

Question 28: Concurrency in DB2

Concurrency refers to the ability of multiple applications or users to access and modify the same database simultaneously.

Isolation Levels

Question 29 & 30: Isolation Levels and Concurrency/Data Integrity

Isolation levels control how transactions interact. Uncommitted read provides maximum concurrency; repeatable read provides the highest data integrity.

RCT (Resource Control Table)

Question 31: RCT (Resource Control Table)

The RCT (Resource Control Table) in CICS (Customer Information Control System) controls DB2 access.

Transaction Rollback

Question 32: Transaction Rollback on Program Abort

DB2 automatically performs a rollback if a program aborts during a transaction, ensuring data integrity.

Cursor Declaration

Question 33: Cursor Declaration in COBOL-DB2

Cursors in COBOL-DB2 programs can be declared in the Working Storage Section or the Procedure Division.

Counting Rows

Question 34: Counting Rows in a Table

SQL Query

SELECT COUNT(*) FROM TAB;

Maximum Length of CHAR and VARCHAR

Question 35 & 36: Maximum Length of CHAR and VARCHAR

Maximum lengths in DB2:

  • CHAR: 254 bytes
  • VARCHAR: 4046 bytes

SPUFI (SQL Processor Using File Input)

Question 37: SPUFI

SPUFI is a tool for executing SQL statements interactively.

Roles in DB2

Question 38: Roles in DB2

DB2 roles group privileges together. They are assigned to users and groups using the `GRANT` statement.

Cursor Stability

Question 39: Cursor Stability

Cursor stability prevents reading rows modified by another transaction before those changes are committed.

Avoiding `SELECT *`

Question 40: Avoiding `SELECT *`

Reasons to avoid `SELECT *` in embedded SQL:

  • Reduces code maintainability.
  • Avoids unnecessary data retrieval.
  • Improves query optimization.

`OPEN CURSOR` Command

Question 41: `OPEN CURSOR` Command

The `OPEN CURSOR` command makes a cursor available for fetching rows. If used with `ORDER BY`, it sorts the rows before fetching.

COBOL Picture Clauses for DB2 Data Types

Question 42: COBOL Picture Clauses

Picture clauses for DB2 data types in COBOL:

  • DATE: PIC X(10)
  • TIME: PIC X(8)
  • TIMESTAMP: PIC X(26)

DCLGEN

Question 43 & 45: DCLGEN (Declaration Generator)

DCLGEN generates host language declarations for DB2 tables and views. Advantages include improved code organization and maintainability.

Contents of a DCLGEN

Question 44: Contents of a DCLGEN

A DCLGEN typically contains:

  • An `EXEC SQL DECLARE TABLE` statement.
  • Host variable declarations.