DB2 Realtime (Scenario based) Interview Questions (11 - 20)

11. What are DB2 data types, and how are they classified?

DB2 supports a wide range of data types, which are classified into the following categories:

  • Numeric Data Types: This category includes INTEGER, SMALLINT, BIGINT, DECIMAL, FLOAT, and DOUBLE.
  • Character Data Types: This includes CHAR, VARCHAR, and CLOB, which are used for storing character strings.
  • Date and Time Data Types: This category encompasses DATE, TIME, and TIMESTAMP.
  • Binary Data Types: This includes BINARY and VARBINARY, which are used for storing binary data.
  • Boolean Data Type: This represents true/false values using the BOOLEAN type.

These data types enable DB2 to efficiently store and manipulate various kinds of data, thereby supporting diverse business requirements.

12. What is the picture clause of Null Indicator variable?

S9(4) COMP

13. Which component checks DB2 deadlocks?

The IRLM (InterSystem Resource Lock Manager) component checks and handles DB2 deadlocks.

Functions -

  • Manages locking and concurrency.
  • Detects deadlocks between transactions.
  • Resolves deadlocks by terminating one of the conflicting processes (usually with a -911 SQLCODE).

14. On which level we can apply the locks?

Locking can be applied on either of these − Page, table and table space.

15. Which component is used to processes SQL statements and selects the access paths?

The DB2 Optimizer is the component that: processes SQL statements, and selects the most efficient access path to retrieve data.

Key Functions:

  • Analyzes SQL queries.
  • Evaluates available indexes, table statistics, and join methods.
  • Chooses the optimal execution plan for performance.

16. Cursors are used to handle single row selections at a time. They are data structure which hold all the results of a query. State whether true or false?

True — Cursors in DB2 are used to handle one row at a time from a multi-row result set. They are a data structure that holds all the rows returned by a SELECT query. You can then fetch one row at a time for processing.

17. Host variables cannot be group items but they may be grouped together in host structure. They can be Renamed or Redefined. State whether true or false?

True. Host variables cannot be group items themselves, but they can be part of a group (host structure). They can also be RENAMED or REDEFINED in COBOL for flexibility.

18. What could be the reason that the SQL statement 'SELECT AVG(SALARY) FROM EMP' generates an inaccurate output?

If the SALARY column contains NULL values, the AVG(SALARY) function might produce unexpected results. By default, aggregate functions like AVG ignore NULL values, which can lead to inaccuracies if NULL represents unknown or missing salaries. To handle this, you can use the COALESCE function to treat NULL as zero:

SELECT AVG(COALESCE(SALARY, 0)) FROM EMP;

This ensures that all NULL salaries are considered as zero in the average calculation.

19. How is the VALUE function used?

The VALUE function in SQL is used to replace NULL values with a specified default. It returns the default value if the column is NULL. For example:

SELECT EMP_ID, VALUE(SALARY, 0) FROM EMPLOYEE;

If SALARY is NULL, it returns 0.

20. What action DB2 takes when a program abends?

When a program abends, DB2 performs an automatic rollback of all uncommitted changes made by that program.