DB2 Interview Questions & Answers

Db2 is a family of data management products, including database servers, developed by IBM. They initially supported the relational model, but were extended to support object–relational features and non-relational structures like JSON and XML. Here you will find the most commonly asked Db2 interview questions with answers which are faced by interviewee. Also these questions will get you acquainted with the nature of the questions you may be ask during your interview.

1. What is DB2?

Answer:- DB2 is a database server developed by IBM. It is a Relational Database Management Syatem which is designed to store, analyze and retrieve the data efficiently. DB2 database supports Object Oriented features and non relational structure with XML.

2. What are the features of DB2?

Answer:- Here are the features of using DB2 −

1. Simple to Administer : With the latest state of the art technique, the administration becomes super simple for users through the GUI.
2. SQL Compatibility : This feature is like a boon for users who want to transfer their legacy applications built in the Oracle database to DB2. This ensures that the existing skills and assets of legacy systems are intact but still migrated quicker.
3. Faster Querying : With the latest AI techniques getting involved, the monitoring of SQL query performance information and then correlating it to the query using AI techniques allows the model to be created and as a response to this procedure the query is significantly faster.
4. High Availability Disaster Recovery : No matter wherever the data resides, DB2 ensures high availability for complete or partial site failures and has the characteristic to support up to 3 remote standby servers.

3. How to Creating database in DB2?

Answer:- You can use a SQL script to create aDB2 database (and a user for the new database) for any version of IBM DB2 that is installed on your environment, including IBM DB2 versions 9.7 and 10.1.
You can create a database in instance using the CREATE DATABASE command.

Creating non-restrictive database :
Syntax: db2 create database <database name>

Creating restrictive database :
Syntax: db2 create database <database name> restrictive

4. What is Bufferpools in DB2?

Answer:- The bufferpool is segment of a fundamental memory space that can be distributed by the manage of the database. The function for the bufferpools includes in index data and cache table that are reserved inside the table. Each and every databases has their individual bufferpools. A default bufferpool is generated at the time of generation of a new database. It called as "IBMDEFAULTBP". Contingent upon the client necessities, it is conceivable to make various bufferpools.

Creating the bufferpool : db2 create bufferpool <bp_name> pagesize <size>
Dropping the bufferpool : drop bufferpool <bp_name>

5. What are benefits of tablespaces in databases?

Answer:- Table space is nothing but the space where the tables are stored. A table space is a set of volumes or disks where the tables actually stored. Table space contains all the tables in the database.
The table spaces are beneficial in database in various ways given as follows:

1. Recoverability: Tablespaces make backup and restore operations more convenient. Using a single command, you can make backup or restore all the database objects in tablespaces.
2. Automatic storage Management: Database manager creates and extends containers depending on the needs.
3. Memory utilization: A single bufferpool can manage multiple tablespaces. You can assign temporary tablespaces to their own bufferpool to increase the performance of activities such as sorts or joins.

6. What is storage group in DB2?

Answer:- Storage group is a named collection of repository path, where information can be saved. Storage groups are used to construct various classes of storage groups that are accessible inside the system of database.
The default storage group for a database is IBMSTOGROUP. When you create a new database, the default storage group is active, if you pass the AUTOMATIC STOGROUP NO parameter at the end of CREATE DATABASE command. The database does not have any default storage groups.

7. Difference between MS SQL Server and IBM DB2 ?

Answer:- Difference between MS SQL Server and IBM DB2 is:

Microsoft SQL Server : It is relational database management system (RDBMS) developed by Microsoft Corporation and initially released on April 24, 1989. It is written in C and C++ languages. It is platform-dependent and it is both GUI and command based software. It supports a variety of transaction processing, analytics applications, and business intelligence in corporate environments.

IBM DB2 : It is Relational Database Management System (RDBMS) product from IBM. It is made to analyze, store, and retrieve the data efficiently. DB2 product is increased with the support of Object-Oriented features and non-relational structures with XML. It is written in C and C++ languages.

8. What is the purpose of using COMMIT and ROLL BACK in DB2?

COMMIT : COMMIT is used to save all transactions performed on database from the SYNC point.If the COMMIT performed then the changes performed from the SYNC point will be made as permanent.COMMIT can also closes all the open cursors at the point of COMMIT executes except the cursors WITH HOLD.

ROLLBACK : ROLLBACK is used to revert back all transactions performed on database from the SYNC point.If the ROLLBACK performed then the changes performed from the SYNC point will be reverted back.

9. What are constraints and how many types of constraints there in DB2?

Answer:- To enforce database integrity, a set of rules is defined, called constraints. The constraints either permit or prohibit the values in the columns. The constraints types are: NOT NULL, Unique, Primary key, Foreign Key, Check, Informational.
Constraints are only associated with tables. They are applied to only particular tables. They are defined and applied to the table at the time of table creation.

10. What is the DBRM?

Answer:- A DBRM is nothing more than a module containing SQL statements extracted from a source program by the DB2 precompiler. It is stored as a member of a partitioned data set. It is not stored in the DB2 Catalog or DB2 Directory.

Also check :