DB2 Interview Questions

Excel in your career with our DB2 interview questions. In this blog, our experts have gathered and listed out the most frequently asked DB2 interview questions that you may come across in the interview. Reading these questions may give you a better understanding and knowledge of DB2. Without further delay, let's start with the most frequently asked questions in the DB2 interview.

Most Frequently Asked DB2 Interview Questions and Answers

1) What is DB2?

DB2 is a database management product which offers advanced data management as well as the analytical capability to manage transactions. DB2 is used for providing actionable information, providing reliability, high performance, and data availability using Unix, Linux, and Windows operating systems.

2) State what data types are used in DB2?

Data types used in DB2 are as follows:

  • CHAR
  • TIME
  • DATE

Want to get certified in DB2. Learn from our experts and do excel in your career with HKR'S DB2 Online Training

3) What are the main components of DB2?

DB2 is the enormous relational database management system with a number of components. There are four main components of DB2:

System Service Component: It is responsible for system operations, logging, other communications, and similar functions. It manages startup and shutdowns.

Locking Services Component: It offers the controls required to manage simultaneous access to data. It is referred to as the Internal Resource Lock Manager (IRLM), and it handles the deadlocks and concurrency issues. 

Database services component: It supports defining, retrieving, and updating user and system data. It is used for executing SQL statements and handles the buffer pool as well.

Distributed data facility component: If offers DB2's distributed database support

4) What is SPUFI and DCLGEN?

SPUFI stands for SQL Processing Using File Input. It enables the direct input of the SQL commands in the time-sharing option environment.

DCLGEN refers to DeCLarations GENerator. It is used for creating the host language copy books for table definitions. It creates DECLARE table.

5) What are the contention situations caused by locks?

Locks are essential to the maintenance of concurrency in the DB2 environment. Following are the contention situations caused by locks that may degrade the performance of the DB2.

Suspension: This is the situation that arose by the application when the application requests lock, which is already held by other applications and could not be shared. This suspended process pauses temporarily and will resume when all processes with the conflicting Lock release them.

Timeout: This is the situation that occurs when the application process is terminated due to a suspension that exceeds the predetermined level. DB2 will end the process, send a message and return the error codes.

Deadlock: This is the situation that occurs when two or more application processes hold locks on resources that are required by others without which they may not proceed further.

6) What is COMMIT and DB2 optimizer?

COMMIT is used to permanently modify data by committing that database changes occur within the current transaction and that permanent changes occur.

DB2 Optimizer is used for processing of the SQL statement. DB2 Optimizer enables the improvement of SQL performance.

7) What are the Isolation Levels in DB2?

Uncommitted Read: 

This is used to access the read-only queries and read-only tables.

Record locking is not done.

Cursor Stability:

This is the default isolation level. 

It locks or unlocks every row simultaneously. 

It ensures that only the data committed at the time of reading is returned.

Read Stability: 

It releases locks on the rows which do not meet the query predicates.

It is used for the result set stability or when future auctions on the returned row can be done.

Repeatable Read: 

It enables the application to fetch and run on the rows often as per the requirement.

It is used to lock the entire table.

IBM DB2 Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

8) What is UNION and UNION ALL?

UNION: This function is used for eliminating the duplicate values from the table.

UNION ALL: This function is used for retaining the duplicate values in the table.

Both these functions are used for combining results from more than one SELECT statement.

9) what are the types of locks available in DB2?

Lock modes within DB2 are used for determining if a lock is compatible with another lock.

S Lock: S indicates share. This mode enables the lock owner and any simultaneous process to read but not edit the locked page or the row.

U Lock: U indicates Update. It allows the owner of the Lock to read but not edit the locked page. It decreases the risk of deadlocks.

X Lock: X indicates Exclusive. It allows the owner of the Lock to read or edit the Locked page or the row.

10) Explain check constraint and buffer pool.

Check constraint: It is used for inserting and updating the table that guarantees the integrity of the data by considering which values are allowed.

Buffer pool: The buffer pool meets the need to buffer one or more indexes or spaces of the table. It may be created by 4K or 32K pages.

11) What is RCT?

RCT refers to the Resource Control Table. It is defined within the DB2 region. It consists of the features that are gathered by DSNCRCT macros. It will match with the CICS transaction Id with the DB2's authorization Id. It must also be matched with the plan ID.

12) What is SQLCA?

SQL Communication Area is the structure of variables that are updated each time SQL queries are executed. If the application includes executable SQL statements, then only one SQLCA is required. FORTRAN requires more than one SQLCA. In Java, SQLCA cannot be used.

13) What does a DCLMGEN include?

EXEC SQL DECLARE TABLE statement that provides the layout of the table according to the DB2 data type. Host language copybook, which provides definitions of the host variable to the column name.

14) What is the maximum size of CHAR and VARCHAR data types?

The maximum size for any CHAR data type within DB2 is 254 bytes.

The maximum size for any VARCHAR data type within DB2 is 4046 bytes.

15) What is PACKAGE? List some of its advantages.

These are the units that are made up of many executable codes which are intended for the SQL statements for a respective Database Request Module.

The following are some of the benefits of the packages:

  • It makes sure you don't need to gather a large collection of DBRM members for a specific plan.
  • In the event of making changes to a program which will ultimately lead to errors, then the reduction of fallback complexities can be done using Package.
  • It is recommended to choose a small collection rather than the large one. Abstain from the cost of an extensive collection of the bind.
  • When automatically binding or rebinding a particular plan, make sure that the total transactions associated with the process are not available.

16) What is a collection?

A collection is the user-defined name which functions as a package anchor but is not physically present. It is used to group packages.

17) What is the purpose of the Value function?

There are two tasks that can be done using value:

  • To avoid the negative SQLCODEs by dealing with Nulls and 0s in the calculation.
  • Null may be replaced by numerical values used in the calculation.

18) What is COPY PENDING status?

COPY PENDING status refers to an image copy to be taken still on hold, and thus until this calendar may not be updated. In order to remove this status, you can obtain the copy of the image. The REPAIR command removes the status of COPY PENDING.

19) What is CURSOR?

A CURSOR is the programming device which assists the SELECT statement to find a set of rows but shows them simultaneously. That's because the host language can only handle one row at a time.

20) What is the LIKE statement and OPEN CURSOR statement?

Like statement: The purpose of the LIKE statement is to perform partial searches, like searching for an employee by their name.

OPEN CURSOR statement: In an OPEN CURSOR statement, the CURSOR will be placed on the table's first row. However, when we add the ORDER BY clause, the rows are retrieved, available, and sorted to the FETCH statement.

21) What is a recovery log?

A recovery log is responsible for identifying any changes made within the database. A recovery log may restore the sequence of events which occurred prior to the failure when a system failure occurred.

22) What is the difference between simple, segmented tablespaces?

The simple tablespace and the segmented tablespace can be used for accomodating multiple tables. Records are kept in one single statement within segments contained in pages of the segmented tablespaces.

23) What is STOGROUP, DBRM, and PLAN?

STOGROUP: STOGROUP must be used for index spaces and tablespaces in the database, otherwise known as the DASD volume collection.

DBRM: DBRM refers to Database Request Module. It is the component of DB2 that is created by DB2's pre-compiler. DBRMs form inputs which are useful in the binding process.

PLAN: PLAN is the result of BIND process. It has executable code for the SQL statements in the DBRM.

24) What do you mean by AUTO COMMIT?

AUTO COMMIT is an option of SPUFI which automatically commits the SQL statements effects if they run successfully.

25) What is DB2 bind?

Bind is a process which creates paths for DB2 tables. It checks the authority of the user and validates the SQL statement. A bind will use the database request modules of the DB2 precompiling step as the input and generates an application plan.

Subscribe to our youtube channel to get new updates..!

26) What is Lock Escalation?

Lock escalation is a process to promote page lock sizes for the table space or table lock sizes when the transaction acquires many locks than the specified ones in NUMLKTS. The Locks should be taken over objects in a single tablespace for the escalations to occur.

27) What is RELEASE/ACQUIRE in a bind?

A point in a program where DB2 may acquire or release locks against the tables and tablespaces. It includes intent locks.

28) What is EXPLAIN?

EXPLAIN is mainly used to show the access path by the optimizer for a SQL statement. Moreover, EXPLAIN may be used in SPUFI or even at the BIND step. Prior to issuing the EXPLAIN statement, we must ensure that PLAN_TABLE has been created already in AUTHID.

29) Explain buffer manager and data manager?

Buffer Manager: It is a component of DB2 that is used to transfer data between an external medium and the storage physically. It is responsible for minimizing the amount of physical input or the output that is actually performed with sophisticated buffering techniques like look-aside buffering and read-ahead buffering. 

Data Manager: It is a component of DB2 that is used to manage the physical database. It does logging and locking by using other components of the system.

30) Why can't we use SELECT* in Embedded SQL programs?

Following are three reasons why SELECT * is not used in embedded SQL programs:

If we modify the table structure, i.e., to add a field, it will be necessary to change the program.
The program can fetch columns that it may not use, that leads to an I/O overhead.
The possibility of the index-only scan is lost.

31) Explain Dynamic SQL.

Ans. It is an SQL statement created during the program's execution. Using Dynamic SQL, DB2 develops and executes the program's SQL statements when running. It is helpful when a user doesn't know the SQL statement's format before writing or running a program.

32) What is the length of SQLCA?

Ans. The total length of SQLCA is 136 bytes. 

33) Name the various Buffer pools in DB2.

Ans. DB2 contains four buffer pools:-

  • BP0
  • BP1
  • BP2
  • BP32

34) Define ALTER.

Ans. In DB2, ALTER is the SQL Command helpful for modifying the DB2 object's definition.

35) What is meant by the Clustering Index?

Ans. It is an index that finds the number of table rows and decides about grouping the rows within the tablespace.

36) Name the format in which the index is stored in DB2.

Ans. The index is stored in B-tree format in DB2. 

37) What is the Aggregate function?

Ans. The Aggregate functions in DB2 are the built-in mathematical tools helpful in performing calculations on a set of values. It returns a single value and is primarily used in the DB2 SELECT statements.

38) Define the use of Predicate in DB2.

Ans. In DB2, predicates help enhance query performance. It states the condition of the given value, row, or group, whether true, false, or unknown. 

39) Define Concurrency.

Ans. In DB2, Concurrency is the ability to simultaneously access the same data by more than one application process. However, Concurrency must be managed to prevent the lost updates' access to unrepeatable reads and uncommitted data.

40) What is meant by Role in DB2?

Ans. In DB2, a Role is a database object that groups many privileges and can be allotted to other groups or users using a GRANT statement. 

IBM DB2 Training

Weekday / Weekend Batches

41) Define MAX() function in DB2.

Ans. MAX() function is an aggregate function in DB2 that returns the maximum or highest value from the set of values. The MAX function returns NULL as a value if the group is empty. 

42) Mention the physical storage length of various data types- TIMESTAMP, DATE, TIME.

Ans. The following are the different storage lengths of these data types.:-

  • DATE - 4 bytes
  • TIME - 3 bytes
  • TIMESTAMP - 10 bytes

43) What is meant by DB2 RUN STATS?

Ans. RUNSTATS in DB2 is a DB2 utility helpful in gathering data about the table space or the index efficiency. 

44) Define DSNDB07 in DB2.

Ans. It is a database where sorting operations take place in DB2. Further, it includes external storage along with the sort-work area. 

45) Define Correlated Subqueries in DB2.

Ans. It is a subquery in DB2 that reconsiders while inspecting a new row or a group of rows as it carries out the outer SELECT statement. A subquery is a nested query, and it is considered a Correlated subquery when the subquery refers to a prime question for each execution. 

In other words, correlated subqueries include a reference to a table outside the range of a subquery. 


All the above are some of the frequently asked DB2 Interview Questions. I hope these questions and answers will help you to clear your interview related to DB2. If you could not find the answer to any question related to cyber security, feel free to comment in the comment section. We will get back to you with the answer.

Find our upcoming IBM DB2 Training Online Classes

  • Batch starts on 2nd Oct 2023, Weekday batch

  • Batch starts on 6th Oct 2023, Fast Track batch

  • Batch starts on 10th Oct 2023, Weekday batch

Global Promotional Image


Request for more information

Research Analyst
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.