DB2 Realtime (Scenario based) Interview Questions (1 - 10)

1. How can the FIRSTNAME and LASTNAME from the EMP table be concatenated to generate complete names?

To combine the FIRSTNAME and LASTNAME columns from the EMP table into a single full name, you can use the concatenation operator (||) in SQL:

SELECT FIRSTNAME || ' ' || LASTNAME AS FULLNAME FROM EMP;

This query merges FIRSTNAME and LASTNAME, adding a space between them, and labels the result as FULLNAME.

2. How would the VARCHAR column REMARKS be defined?

Let us assume the table column is defined as -

CREATE TABLE table_name 
	(...
	REMARKS   VARCHAR(100),
	...);

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).

Here, REMARKS-LEN stores the actual length of the text, and REMARKS-TEXT holds the variable-length character data, accommodating up to 100 characters.

3. How to find the number of rows in a DB2 table?

To determine the number of rows in a DB2 table, you can use the COUNT(*) function in a SQL query. For example:

SELECT COUNT(*) FROM table_name;

This query counts and returns the total number of rows present in the specified table_name. ​

4. How can duplicate values be eliminated from a DB2 SELECT statement?

To remove duplicate values from the results of a SELECT statement, you can use the DISTINCT keyword. For example:

SELECT DISTINCT column_name FROM table_name;

This query retrieves unique values from the specified column_name, eliminating any duplicates. ​

5. How will you achieve record-level locking in DB2 versions where record-level locking is not allowed?

If DB2 does not support record-level locking directly, you can simulate it using application-level logic:

  • Add a "status" or "lock" column in the table.
  • Mark a record as "in use" before modifying.
  • Release the "lock" after processing. This mimics record-level control to prevent multiple users from updating the same row.

6. What is RUNSTATS and why is it used?

RUNSTATS is a utility that gathers statistics about tables and indexes. DB2 uses this data to determine the most efficient way (access path) to run queries. Keeping stats updated helps optimize performance.

Usually, the RUN STATS is run after the following:

  • After a load.
  • After a mass update.
  • After any major deletions, insertions
  • After REORG the table.

It improves query performance by providing DB2 with fresh data about table sizes and structure.

7. Let us say that I have a program it uses dynamic SQL. It has been working fine till now, however, lately, I have found that the performance has been affected and deteriorated. Can you guess what can be the reason behind this?

Possible reasons:

  • Outdated statistics → Use RUNSTATS.
  • Query access paths have changed.
  • Indexes may have been dropped or modified.
  • Increased data volume. Solution: Rebind your package and run RUNSTATS.

8. Can I ALTER a table (add a column) while others are using it?

Yes, in most DB2 versions, some changes (like adding a nullable column) can be done while the table is being used. But structural changes (like dropping a column) usually require exclusive access.

9. How do you optimize a DB2 query?

Optimizing a DB2 query involves several techniques:

  • Indexing: Use appropriate indexes to speed up data retrieval.
  • Query Rewriting: Simplify complex queries or restructure them for better performance.
  • Avoiding Unnecessary Columns: Select only the columns needed to reduce data retrieval time.
  • Using Joins Efficiently: Choose the right type of join (INNER, LEFT, RIGHT) and avoid unnecessary joins.
  • Analyzing Access Plans: Use tools like the DB2 Explain utility to analyze the access plan and identify bottlenecks.
  • Maintaining Statistics: Keep database statistics up-to-date to help the optimizer choose the best execution plan.

10. What is the difference between static and dynamic SQL in DB2?

Static SQL - In Static SQL, SQL statements are embedded directly within the application code, and they are compiled and optimized during the compile time. This approach enhances both performance and security, as the execution plans are determined in advance.

Dynamic SQL - In contrast, Dynamic SQL constructs and executes SQL statements at runtime. This offers greater flexibility, allowing applications to perform ad-hoc queries. However, it may result in lower performance and security compared to Static SQL, since execution plans are generated at runtime.