DB2 Interview Questions and Answers (21 - 30)
21. What are the various types of Locks?
DB2 utilizes different lock types to control concurrent data access:
- SHARE: Allows multiple transactions to read data simultaneously but prevents modifications.
- EXCLUSIVE: Restricts both reading and writing of data by other transactions, ensuring sole access.
- UPDATE: Permits reading but restricts other transactions from acquiring exclusive or update locks, commonly used during data modifications.
22. On which levels can we apply locks?
In DB2, locks can be applied at multiple levels to control data concurrency and integrity:
- Page Level: Locks a specific page (a fixed-length block of storage) in the database.
- Table Level: Locks an entire table, preventing other transactions from accessing it concurrently.
- Tablespace Level: Locks an entire tablespace, which is a logical storage unit containing one or more tables.
23. If a transaction takes an Update lock on some data, then other transactions can get what type of lock?
When a transaction acquires an Update lock on a piece of data, other transactions can still obtain a Shared lock on that data. This allows them to read the data but not modify it until the Update lock is released.
24. What are the Isolation levels does DB2 support?
DB2 supports the following isolation levels to control data concurrency and consistency:
- RR (Repeatable Read): Highest level; prevents dirty, non-repeatable, and phantom reads.
- RS (Read Stability): Prevents non-repeatable reads, but allows phantom reads.
- CS (Cursor Stability): Default level; prevents dirty reads, allows non-repeatable and phantom reads.
- UR (Uncommitted Read): Allows dirty reads; improves performance but risks reading uncommitted data.
25. What is the difference between CS and RR isolation levels?
- Repeatable Read (RR): Retains all locks until the transaction concludes, preventing other transactions from altering the data during this period, thus ensuring consistent reads.
- Cursor Stability (CS): Releases locks on data pages immediately after they're accessed, allowing other transactions to modify the data sooner.
26. In which step do we specify the isolation level?
The isolation level is specified during the BIND process. The BIND process associates application programs with DB2 packages or plans, and setting the isolation level determines how the program will interact with concurrently running transactions, balancing data consistency and system performance.
27. What is the maximum length of SQLCA?
The maximum length of SQLCA is 136.
28. What is the default size of a buffer pool page?
The default size of a DB2 buffer pool page is 4 KB (kilobytes). DB2 also supports other page sizes: 8 KB, 16 KB, and 32 KB (if configured).
29. What is an aggregate function?
Aggregate functions are built-in mathematical tools in SQL that perform calculations on multiple rows of a table's column and return a single value. Common aggregate functions include:
SUM():Calculates the total sum of a numeric column.AVG():Computes the average value of a numeric column.MAX():Finds the maximum value in a column.MIN():Finds the minimum value in a column.COUNT():Counts the number of rows in a column.
30. What is the physical storage length of each of the data types, DATE, TIME, and TIMESTAMP?
- DATE: Stored in 4 bytes.
- TIME: Stored in 3 bytes.
- TIMESTAMP: Stored in 10 bytes.