DB2 Interview Questions

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

There are four Buffer pools in DB2.

• BP0
• BP1
• BP2
• BP32

• It ensures that you do not have to bring a large collection of DBRM members together for a particular plan.
• In case of making changes in a program that will eventually lead to errors, then the decrease in fallback complexities can be made with the help of Package.
• It is advisable to go for a small collection instead of a large one. Abstain from the cost of large collection of a bind.
• In the course of automatic binding or rebinding of a specific plan, ensure that the total transactions attached to the process are unavailable.

DB2 is a huge relational database management system with many components in it. The four major components of DB2 are:

• System services component: It supports system operations, other communication, logging, and similar functions. It handles startup and shutdowns.
• Locking services component: It provides the necessary controls for managing concurrent access to data. This component is known as Internal Resource Lock Manager (IRLM) and it manages the concurrency issues and deadlocks.
• Database services component: It supports the definition, retrieval, and update of user and system data. It is responsible for the execution of SQL statements and it also manages the buffer pool.
• Distributed data facility component: It provides DB2s, and distributed database support.

• It is good for accessing read-only tables and read-only queries.
• No record Locking is done.

CS- Cursor Stability:

• It is a default isolation level.
• It locks or unlocks each row at a time.
• It guarantees to return only data which was committed at the time of reading.

RS- Read Stability:

• It releases Locks on rows that do not satisfy the query predicates.
• It is used for result set stability or when the future actions on returned rows may be taken.

RR- Repeatable Read:

• It allows the application to retrieve and operate on rows as many times as needed.
• It locks the entire table.

• S Lock (Share): This mode allows the Lock owner and any concurrent process to read but not change the locked page or row.
• U Lock (Update): This mode allows the Lock owner to read but not change the locked page. This lock mode reduces the chance of deadlocks.
• X Lock (Exclusive): This mode allows the Lock owner to read or change the Locked page or row.

• Suspension: This situation is encountered by an application when it requests the lock that is already held by another application and cannot be shared. This suspended process temporarily stops running and resumes when all the processes that hold the conflicting lock release them.
• Timeout: This situation is encountered by an application process when it terminates because of a suspension that exceeds the preset level. DB2 then terminates the process, issues message and returns the error codes.
• Deadlock: This situation occurs when two or more application processes each hold locks on resources that others need and without which they cannot proceed.