DB2 Interview Questions and Answers (41 - 50)
41. Cursors are used to handle single row selections at a time. They are data structures that hold all the results of a query. State whether true or false.
This statement is false. Cursors are used to handle multiple row selections, allowing programs to process each row individually. They act as pointers to the result set of a query, enabling row-by-row processing.
42. What is meant by EXPLAIN?
EXPLAIN is a DB2 feature that reveals how the DB2 optimizer intends to access data for a given SQL statement. By analyzing the access path, developers can identify and improve inefficient queries. EXPLAIN can be executed using tools like SPUFI for individual SQL statements or during the BIND process for embedded SQL.
43. How to perform EXPLAIN for any Dynamic SQL statement?
To analyze dynamic SQL statements using EXPLAIN, you can:
- SPUFI or QMF: Execute the dynamic SQL within these tools with the EXPLAIN option enabled to view the access path.
- Embedded SQL: Incorporate the EXPLAIN statement within your dynamic SQL code to capture the access path details during execution.
44. What is a collection?
A collection is a user-defined namespace that groups related packages. While it doesn't have a physical existence, it serves as a logical container to organize packages, making it easier to manage and reference them during program execution.
45. What is Dynamic SQL?
Dynamic SQL refers to SQL statements that are constructed and executed at runtime, rather than being hard-coded in the program. This allows applications to adapt to varying query requirements based on user input or other runtime conditions. Dynamic SQL is often used in applications requiring flexible database interactions.
46. Which component checks DB2 deadlocks?
The Internal Resource Lock Manager (IRLM) is the DB2 component responsible for managing locks and detecting deadlocks. It ensures data integrity by controlling concurrent access to database resources and resolving conflicts that arise when multiple transactions compete for the same resources.
47. Which component is used to process SQL statements and select the access paths?
The DB2 Optimizer is the component responsible for processing SQL statements and determining the most efficient access paths to retrieve or modify data. It analyzes various factors, such as available indexes and table statistics, to optimize query performance.
48. In a COBOL-DB2 program, all SQL statements must be coded in Area A. State whether true or false.
This statement is false. In COBOL-DB2 programs, SQL statements should be coded in Area B, which starts from column 12 onward. Area A comprises columns 8 to 11 and is typically reserved for paragraph names and section headers.
49. When a table is dropped, all the aliases get dropped automatically. State whether true or false.
This statement is false. When a table is dropped in DB2, its synonyms are also dropped automatically. However, aliases remain intact and must be dropped explicitly if no longer needed.
50. What action does DB2 take when a program abends?
If a program abends (abnormally ends) during a transaction, DB2 performs an automatic rollback. This means that any changes made to the database during the transaction are undone, ensuring data integrity.