Most Common DB2 Interview Questions & Answers

Top DB2 Interview Questions and Answers for 2022

1. What is a database? 

A database is defined as a collection of interrelated data or logically related data. Common examples of the database is that of a University that contains data of faculty members, their employee ID, the date of joining subjects handled, and similar data. It will also contain data related to the classes, teachers, student data, enrollment number, name of the student, age, parent's name, and more. 

2. What type of database is DB2?

DB2 is a relational database, the arrangement of data is in a tabular form with rows and columns called tuples and attributes. Other examples of relational databases are MySQL, PostgreSQL, and others. 

3. What is meant by DB2? Or Explain DB2.

Db2 is described as an overarching family of data management services and products presented by IBM driven by artificial intelligence to manage structured as well as unstructured data. DB2 is written in C ++ and assembly language. Its operating system is Linux and Windows.

4. Which data types are supported? 

DB2 supports 8 data types. The different types of data types available in DB2 are as follows: smallint, integer, float, decimal, date, time, char, varchar.

5. What is the method to find rows in DB 2 table?

The command -select count(*) on the DB2 query is used to find the number of rows in a table.

6. How are duplicated values eliminated from the DB2 table?

The command -select distinct- is used to eliminate duplicate values in the DB2 query. 

7. Describe aggregate.

The function aggregate is an inbuilt mathematical tool that is used within the DB 2 - select -command.

8. Describe the function value. 

The value function performs two tasks. Mainly, it is used to avoid the negative SQLCODES and capture null and zero errors. Secondly, it also substitutes the numerical values for nulls in any computation. 

9. What is meant by Union and Union All?

The main purpose of using both these commands is to integrate the results brought about by multiple select commands or statements. Where a Union is a definite function to eliminate any duplicates in the table, Union All retains such duplicates.

10. Can union commands be used in embedded SQL?

In embedded SQL, the Union command is used in the cursor

11. Define between and in; what are the specific range values of between?

Between supplies range of values while it would not provide any value list. There is always a specific range value that is inclusively defined with the between command.

12. Define cursor and its use.

The command cursor is used in combination with the SELECT statement to identify the rows that shall be displayed one at a time, within a set. Usually, this is in the host language and needs to deal with only one row at a time. 

13. What is the procedure to retrieve rows in embedded SQL?

The command -select the - statement is used to retrieve the rows from a DB2 table in embedded SQL. Alternatively, the cursor command is also used. 

14. What happens when the open cursor statement is used?

The open cursor command is used to place the cursor on the very first row of a table. Conversely the -order by cursor - will ensure rows are sorted, fetched, and available for the -fetch- command.

15. Can you select rows with indexes?

Yes, in WHERE clause the indexed columns have to be specified

16. Aggregate functions, what are they?

In the SELECT clause, there are in-built mathematical functions. These are called Aggregate functions. 

17. How can more than one cursor be used to open any program?

The user can open more than one cursor of a program at a given time.

18. What is meant by deadlocks in DB2?

By using the Internal Resource Lock Manager (IRLM) it is possible to manage concurrency aspects or deadlocks. These locks are classified in terms of mode, duration, and size.

19. Where are the level locks and how many types of page locks can be used? 

Level locks are applied at 3 levels namely page tablespace and table. The different types of page locks available are exclusive, update and share.

20. Why is the commit command used in DB2?

Commit is used to make changes to the data permanently. The database changes that occur during the current transaction and what is needed to make changes permanent are executed with the comet command.

21. Why is the DB2 Optimizer used?

The DB2 Optimizer is used as a part of the SQL statement processing to enhance performance. 

22. Which component is used for executing statements in SQL? 

The database services component is used for the execution of SQL statements as well as for managing the buffer pool. 

23. How is DB2 shut down and started up?

The component system services are used to handle the DB2start-upp as well as shutdown. 

24. Identify sqlca.

The sqlca is used for the server query language communication area. It is considered a collection of variables that will be updated once the SQL statement is executed.

25. Name the use of storage.

The definition of a storage group is the storage path for the data to be stored. Usually, table space is assigned to a storage group.

26. Why is predicate used?  

Predicates are used to improve the performance of queries. 

27. What is physical storage in the context of date data type?  

The physical storage for the Time data type is 4 bytes.

28. Why is ALTER used?

ALTER is used to make changes to the definition of DB2 objects.  

29. What is meant by DBRM?

Database Request Module contains SQL statements generated by the pre-compiler from the host language program obtained by the pre-compiler.

30. How are PACKAGES used?

PACKAGES are containers of code for executing SQL statements for one DBRM.

31. Why are Packages used? 

Mainly used to avoid overcrowding of large DBRM members in a plan and minimize complexities from fall back resulting in changes and error. 

32. Define a collection?

A user-defined name that will hold all the packages and have no physical existence. It is mainly used to bring together many groups into the package. 

33. What is meant by  SPUFI?

SPUFI: is defined as the execution engine using SQL command. It helps by executing many queries at the same time and storing it as PS and PDS members. 

34. What is meant by QMF? 

It is defined as a reporting environment that is used in the formatting of reports.

FAQs

1. What type of SQL is DB2?

DB2 can be described as a relational database management system. The purpose of DB2 design is storage, analysis, and retrieval of data in an efficient manner.

2. What is DB2 bind process?

In a given application program, the DBRM has SQL statements that were extracted by the DB2 pre-compiler. When using the BIND plan the SQL statements are formed into an operational unit or are "bound." Here this is formed by translating the statements into control structures which the DB2 uses when it executes the SQL statements.

3. Is DB2 a mainframe database?

Data in DB2 is stored in a structural format called a page. This is very similar to an Oracle block in the Oracle package. In DB2, the basic page structures include- a fixed length page header, a page trailer with variable length as well as spaces in between, which are used for data purposes or as free space. Physically, the data is stored in VSM files or LDS. 

4. How is data stored in DB2?

DB2 uses a relational database management system that operates on a mainframe. In a relational database, there is a structured arrangement of data in tables. The relationship between the units is what gives it the name "relational." In DB2, the relational database products enable enterprise-level data model accessing and queries. Hence, IBM built the standardized structured query language for this purpose. Typically, in DB2 and Oracle, the mainframe, as well as Windows functionality, is available, but the products are not identical on the different platforms.

Looking to accelerate your career as a skilled Full Stack Web Developer? Leverage Caltech CTME's academic excellence in a unique bootcamp-style Post Graduate Program in Full Stack Web Development. Enroll Now!

Conclusion 

While these questions and answers set you up for facing interviews for any type of role that involves the use of DB2, the most important thing is - how accurately and confidently you answer! You can gain complete subject knowledge and hands-on learning with the right learning partner. 

If you are looking to enhance your software skills, then we would suggest you take a look at Simplilearn’s Post Graduate Program in Full Stack Web Development in collaboration with Caltech CTME. This course will help you hone key software development skills and make you job ready.

If you have any questions or queries, then feel free to post them in the comments section below. We will review them and get back to you at the earliest.