DB2 Interview Questions and Answers (1 - 10)

1. What is DB2? Explain its significance.

DB2 is a relational database management system (RDBMS) developed by IBM. It is used to store, retrieve, and manage data efficiently, primarily on mainframe (z/OS) systems, but also available on Linux, UNIX, and Windows (LUW) platforms.

  • Handles large volumes of data and supports thousands of users concurrently.
  • Allows seamless integration with application languages like COBOL, Java, and C.
  • Includes stored procedures, triggers, indexing, partitions, and buffer pools for optimized performance.
  • Widely used in enterprise-level applications on IBM z/OS systems for banking, finance, insurance, and government sectors.

2. What is ALTER?

ALTER is an SQL command used to modify the structure or properties of existing DB2 database objects, such as tables or indexes. For instance, you can add a new column to a table using:

ALTER TABLE table_name ADD COLUMN new_column_name data_type;

3. When is the LIKE statement used?

The LIKE statement is used in SQL to search for a specified pattern in a column. It's commonly used for partial searches, such as finding rows where a column's value matches a certain substring. For example:, to find all employees whose names start with 'A':

SELECT * FROM employees WHERE name LIKE 'A%';

Here, % is a wildcard character that matches zero or more characters. ​

4. What do BETWEEN and IN mean? Is BETWEEN inclusive of specified range values?

  • BETWEEN: Used to filter the result set within a certain range. It is inclusive, meaning it includes the boundary values specified. For example: 
    SELECT columns_list FROM employees WHERE salary BETWEEN 50000 AND 100000;
    This query retrieves employees with salaries ranging from 50,000 to 100,000, inclusive.
  • IN: Used to filter the result set based on a list of specified values. For example: 
    SELECT columns_list FROM employees WHERE salary IN (50000, 100000);
    This query retrieves employees with salaries 50,000 or 100,000.

5. Which component is responsible for the execution of SQL statements?

The Database Services component is responsible for the execution of SQL statements. It manages the processing of SQL queries, data retrieval, and updates, ensuring that SQL operations are performed efficiently and accurately.

6. Which component handles DB2 startup and shutdown?

The System Services component manages the startup and shutdown processes of DB2. It oversees system-wide operations, coordinates interactions among various DB2 components, and ensures that the database system initializes and terminates gracefully.

7. How to retrieve rows from a DB2 table in Embedded SQL?

In embedded SQL, you can retrieve rows using:

  • Single-row SELECT statement: Fetches one row at a time when you know the query will return a single result.​
  • CURSOR: Used when the query returns multiple rows. You declare a cursor for the SELECT statement, open it, fetch each row into host variables, and close the cursor after processing.

8. Why is SELECT * not preferred in embedded SQL programs?

SELECT * is not preferred in embedded SQL because:

  • Mapping Issues: Requires matching host variables for all columns in correct order, leading to potential mismatches.
  • Performance Impact: Retrieves all columns, even if not needed, increasing I/O and CPU usage.
  • Program Dependency: If table structure changes (e.g., a new column is added), the program may fail or behave unexpectedly.

Solution - Use explicit column names to improve clarity, efficiency, and stability.

EXEC SQL SELECT EMP_ID, EMP_NAME FROM EMPLOYEE END-EXEC.

9. What are some of the main fields in SQLCA?

The SQL Communication Area (SQLCA) is a data structure used programs to receive feedback about the execution of SQL statements. Key fields include:​

  • SQLCODE: Indicates the outcome of the last executed SQL statement. A value of 0 means success, positive values indicate warnings, and negative values signify errors.
  • SQLERRM: Provides a message text corresponding to the SQLCODE, offering more details about the execution status.
  • SQLERRD: An array of integers offering additional diagnostic information, such as the number of rows processed.

10. What is meant by DCLGEN?

DCLGEN stands for Declaration Generator. It's a DB2 utility that generates:

  • EXEC SQL DECLARE TABLE statement: Describes the table's structure in terms of DB2 data types.​
  • Host variables copybook: Provides host variable definitions corresponding to the table's columns, facilitating seamless integration between SQL statements and the host language (e.g., COBOL) in embedded SQL programs.