DB2 Interview Questions and Answers

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

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

• SMALLINT
• INTEGER
• FLOAT
• DECIMAL
• CHAR
• VARCHAR
• DATE
• TIME

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

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.

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

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.

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.

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.

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.

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

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

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.

• 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.

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

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.

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.

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.

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.

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.

• Date:-4 Bytes
• Time:-3Bytes
• Timestamp:-10 Bytes

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.

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.

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