DB2 Interview Questions and Answers (11 - 20)

11. What are the contents of DCLGEN Copybook?

A DCLGEN output includes:

  • Table declaration: An SQL statement that outlines the table's schema, detailing each column's name and data type.​
  • Host variable definitions: A set of declarations in the host language that correspond to the table's columns, ensuring that data can be accurately exchanged between DB2 and the application program.

12. How would the VARCHAR column REMARKS be defined?

In embedded SQL (e.g., COBOL with DB2), a VARCHAR column like REMARKS is defined using a length field + data field.

01 REMARKS.
   05 REMARKS-LEN  PIC S9(4) COMP.
   05 REMARKS-TEXT PIC X(100).

13. Host variables cannot be group items, but they may be grouped together in host structures. They can be renamed or redefined. State whether true or false.

This statement is false. Host variables can indeed be group items in COBOL-DB2 programs. However, they cannot be renamed or redefined within the program.

14. What is concurrency in terms of DB2?

Concurrency refers to the ability of multiple users or programs to access the same data simultaneously without interfering with each other. Ensures data consistency while allowing parallel access.

15. What is the difference between the above two queries i.e. DELETE and DROP?

The query DELETE * FROM table-name removes all rows from the specified table, but the table itself remains in the database, as empty. In contrast, the query DROP TABLE table-name not only deletes all the rows and columns but also permanently removes the table from the database, i.e., the table is entirely removed.

16. What do you understand by DBRM and PLAN?

  • Database Request Module (DBRM): Contains compiled SQL statements extracted from a program during precompilation. It is used as input to the bind process.
  • PLAN: A DB2 executable created by binding one or more DBRMs. It is used by DB2 to execute the SQL statements during program runtime.

Relationship: Source Code → Precompile → DBRM → Bind → PLAN

17. What is meant by ACQUIRE/RELEASE in BIND?

During the BIND process, the ACQUIRE and RELEASE options specify when DB2 should obtain and free locks on database resources:

  • ACQUIRE: Determines when DB2 should acquire locks—either at the start of the plan execution (ALLOCATE) or as needed (USE).
  • RELEASE: Specifies when DB2 should release locks—either at each commit point (COMMIT) or at the end of the plan execution (DEALLOCATE).

18. What is meant by PACKAGES?

In DB2, a package is a database object containing the compiled (executable) form of SQL statements from a single Database Request Module (DBRM). Packages are produced during the BIND process and are associated with a specific collection. They allow for more granular control over SQL execution and can improve program maintenance and performance.

19. What are the advantages of using PACKAGES?

Using packages offers several benefits:

  • Instead of binding all DBRMs into a single plan, packages allow for binding individual DBRMs separately, reducing the impact of changes.
  • Binding or rebinding a package doesn't require the entire plan to be unavailable, minimizing disruptions.
  • Errors or changes in one package don't necessitate rebinding the entire plan, making error resolution and updates more manageable.

20. What is meant by Lock Escalation?

Lock escalation is the process where DB2 automatically replaces multiple row or page locks with a single table-level lock to conserve system resources.

Why It Happens: Occurs when a program acquires too many locks on rows or pages. To avoid lock resource exhaustion, DB2 escalates to a higher-level lock.

Impact: Reduces system overhead, but can increase contention by restricting access to the entire table.

Example: If many row-level locks are held on a table, DB2 may escalate to a table-level lock to reduce lock control block usage.