Loading…

DB2 Interview Questions and Answers

By Admin
Last update: 16 May 2020, 25 Questions
DB2 Interview Questions

DB2 is a product developed by IBM.DB2 is the relational database management system(RDMS). DB2 is specially designed to store, analyze and retrieve the data efficiently. DB2 can handle a large amount of data and at the same time serving multiple users. The DB2 interview questions will be answered below in the article.

Advantages of DB2 database

  • DB2 has a more powerful structured SQL query than Microsoft's SQL.
  • DB2 has some useful features like Object tables, Java method support, and multiple user-defined functions
  • IBM DB2 run on all the available platforms

If you are preparing for the db2 dba interview questions, we have one of the biggest collection of db2 interview questions.

Most Frequently Asked DB2 Interview Questions

1. Explain DB2.

DB2 is the relational database management system(RDMS) which is specially designed to store, analyze and retrieve the data efficiently.

2. List down the data types in the DB2 database.

The data types that are available in the DB2 database are:-

  • SMALLINT
  • INTEGER
  • FLOAT
  • DECIMAL
  • CHAR
  • VARCHAR
  • DATE
  • TIME
3. What is the use of DB2 optimizer?

DB2 optimizer processes the SQL statements, and it also helps to select the access path

4. What is DBRM in DB2 database?

DBRM stands for Database Request Module.DBRM is the component inside DB2, which is created by the precompiler of DB2. DRBM consists of the SQL source statements which get extracted out of the application program.DBRMs form the inputs which are very helpful in the binding process.

5. What is the meaning concurrency in the DB2 database?

Concurrency is the period when more than one DB2 application process can access the same data at the same time.

6. What is the role of Data Manager in the DB2 database?

DB2 database has a component which manages the physical database, and also it requests the system components to perform the operations like locking, logging, etc. and also to perform I/O operations.

7. What is the Clustering Index in the DB2 database?

A clustering index is a type of index, which locates the roes of the table and determines how to group these rows in the tablespace.

8. What is the role of the Data page in the DB2 database?

Data page retrieves the data from the database. The database from which the data can be extracted is in the form of 4 kilobytes or 32 kilobytes. Data page also includes the information regarding the user that is part of the database.

9. How to find the number of rows in DB2 tables?

The user can find the number of rows in the DB2 tables by using the SELECT COUNT (*) on the DB2 query.

This question has always been a center of the discussion in db2 Interview Questions.

10. How can the duplicate values be eliminated from DB2 SELECT?

A user can eliminate the duplicate values from DB2 SELECT by using SELECT DISTINCT in DB2 query.

11. How to find the maximum value in a column in the DB2 database?

A user can find the maximum value in a column by using SELECT MAX(...)..in DB2 query.

12. What is buffer pool in the DB2 database?

A buffer pool is the are of the main memory allocated by the data manager. It is expressly reserved to satisfy the buffering requirements for one or more tablespaces, or the indexes and Bufferpool is made up of either 4K or 32K pages.

13. What are the benefits of using the DB2 database?
  • DB2 database is straightforward to use
  • DB2 has a feature of automatic navigation in which the user only has to write their requirements and rest is done by DB2.
  • DB2 us a very much interactive database due to which debugging or testing is done very quickly.
14. What is the role of UNION ALL and UNION

The UNION command eliminates the duplicates and the UNION ALL command retains the duplicates while combining the results of the different SELECT statements.

15. What is the role of the cursor in DB2?

A cursor is a programming device which allows the SELECT statement to find a set of rows but it returns one row at a time, and the cursor plays a vital role because the host language needs only one row at a time.

16. What types of tables are there in the DB2 database?

There are three types of tables in the DB2 database:-

  • Base Tables-These tables hold the persistent data
  • Temporary Tables-These tables are used for the temporary work of the database operations
  • Materialized Query Tables-These tables are used to increase the performance of queries

Point to be noted: Make sure that you go through this twice as this is the favorite db2 dba interview questions for fresher and experienced as well.

17. What are the different types of Base tables?

There are six types of base tables in the DB2 database:-

  • Regular Tables: General purpose tables and Common tables with the indexes are known as the general purpose tables.
  • Multidimensional Clustering Table (MDC): This type of table is used to maintain large database environments.
  • Insert time clustering Table (ITC): They can be partitioned tables.
  • Range-Clustered tables Table (RCT): This type of table provides fast and direct access to data.
  • Partitioned Tables: These type of tables are used in the data organization schema where the table data is divided into multiple storage objects.
  • Temporal Tables: History of a table in a database is stored in a temporary table like the details of the modifications done previously.
18. What is the syntax for creating a table in the DB2 database?

Example

                                                    

db2 create table <schema_name>.<table_name> (column_name column_type....) in <tablespace_name>

20. What is the function of Logging in the DB2 database?

The log files consist of the error logs, which are used to recover from the application errors. The logs keep the record of changes that happened in the database.

21. What are the two types of Logging in the DB2 database? Explain them.

The two types of logging are:-

1. Circular logging:- When the user wants to allocate the new transaction log file then in Circular Clogging the old transaction logs are overwritten that means erasing the sequences of old log files and reusing them.

2. Archive logging:-This method supports for the Online Backup and database recovery using the log files called roll-forward recovery.

23. What is the role of Schema in the DB2 database?

In a database user cannot create the multiple database objects with the same name. In this case, Schema provides a group which means a user can create various database objects with the same name in different schema groups.

24. What is a trigger in the DB2 database?

A trigger is a set of particular actions that are primarily performed to respond to the operations (INSERT, UPDATE or DELETE) on a specific table in the database. Triggers can be accessed and shared among the multiple applications.

This particular db2 interview questions defines explicitly the about triggers.

25. List down the types of Triggers in the DB2 database?

There are two types of triggers:-

  • BEFORE triggers:-These triggers are executed before any SQL operation.
  • AFTER triggers:-These triggers are executed after any SQL operation

Development History

IBM Db2 which was announced in the year 2017 is the successor name to the DB2 that was introduced in the1980s by IBM only for their specific platform. In 1990 IBM decided to design a Universal Database (UDB) DB2 server that can run on any operating systems like Linux, UNIX, and Windows.

Latest Version

The newest version for IBM DB2 is 10.5, and the name of this version is “Kepler.”