Are you an aspirant aiming to crack IBM DB2 interview? Great news for you. We have compiled the top IBM DB2 questions and answers in this blog. No matter whether you are fresher or experienced, this blog will be highly beneficial to you. Read the given IBM DB2 interview questions from top to bottom. Once you have done it, we are sure that cracking your IBM DB2 interview is no longer a big deal.
Know that DB2 is the database product offered by IBM. DB2 is a relational database management system with which you can store, manage, and retrieve data in an organized way. Database servers play a vital role in the database management of DB2. As a whole, DB2 is one of the most secure, reliable, and efficient database systems.
With DB2, you can store large data arrays and retrieve the data faster. DB2 uses SQL queries to store, manage and retrieve data. Apart from all these, DB2 offers flexibility, independency, concurrency, and many more features to users.
In this blog, we compiled the IBM DB2 interview questions and answers in the following categories in greater detail.
A database is nothing but an organized collection of information or data stored in tables. You can easily access, update, and manage the data in the tables. In every database, the information is stored in the rows and columns of one or more tables. We can use Database Management Systems (DBMS) to manage or control a database. Also, we can use Structured Query Language (SQL) to write as well as query data in a database.
In its basic form, DB2 is a relational database management system. It is also a cloud-native database with which you can store, manage, and retrieve data effectively. Mainly, this database performs real-time data analytics and low-latency transactions. You can use DB2 for mission-critical applications since it offers a high level of security as well as continuous availability. With this database, you can manage both structured and unstructured data effortlessly.
Do you want to get certified and build your career in IBM DB2? Then enroll in "IBM DB2 Online Training" this course will help you to achieve excellence in this domain. |
There are totally four components in DB2 as follows:
It is the substitute for a table or view. In other words, it is the alternative name of objects. The objects can be tables, sequences, views, etc. An alias is usually defined as a local server. It can also refer to a table or view at a remote server. We can use an alias if we want to refer to a table or view in an SQL statement. Another thing is that we can create and drop aliases like we create and drop tables and views.
A tablespace is nothing but a set of volumes on disks. The disks contain the data sets where tables are usually stored. A table space will have linear VSAM datasets. Tables are generally divided into equal-sized units, known as pages. We can compress data in table spaces, which in turn saves space and allows for storing more data. DB2 creates two types of table spaces such as partition by growth and partition by range.
Structured Query Language (SQL) is nothing but the language used in DB2 to access data from DB2. With this language, we can define and manipulate data in relational databases. We can modify data objects such as tables. We can insert, delete, and update data in tables. Not only that, we can use SQL to authorize users to access tables and views.
An attachment facility is an interface that connects DB2 with other environments. Here, the environments can be WebSphere, Information Management System (IMS), Customer Information Control System (CICS), Time Sharing Option (TSO), and Batch. We can use ODBC, SQLJ, and JDBC interfaces to connect DB2 with other environments.
A Buffer pool is part of the main memory. You can store tables and indexes in a buffer pool. Generally, the buffer pool is managed by the database manager.
Every DB2 has a buffer pool. Every new database comes with a buffer pool known as IBMDEFAULTBP. You can create, drop or modify buffer pools with the help of the statements such as CREATE BUFFERPOOL, ALTER BUFFERPOOL, and DROP buffer pool.
The isolation level in DB2 denotes the degree of isolation between the data and other concurrent processes. There are four isolation levels in DB2: Repeatable Read (RR), Cursor Stability (CS), Read Stability (RS), and Uncommitted Read (UR).
This is one of the isolation levels of IBM DB2. According to cursor stability, a row is locked when the cursor is pointed at the row. Note that the specific row is accessed during a transaction, and the lock remains locked until the transaction gets completed. Significantly, an application can only update the row once the lock is released.
Usually, it is essential to declare tables and views when you write codes. The declarations generator, or DCLGEN, generates DECLARE statements for programs written in C in addition to COBOL. As a result, you can avoid writing codes for statements. Along with that, DCLGEN generates respective host variable structures.
It is the process of releasing any number of page locks, LOB, row locks, and XML locks. Applications on a single table or table space usually make these locks. Whenever a lock escalation occurs in the database, it releases a message like DSNI0311. Using this message, we can identify the tablespace where the lock escalation occurred.
The function receives a set of inputs but returns only a single value. In other words, the argument of every aggregate function is a set of values.
ARRAY_AVG, COUNT, AVG, COUNT_BIG, MEDIAN, MAX (aggregate), and SUM are some examples of aggregate functions.
SQLCA refers to SQL Communications Area. SQLCA is a set of variables updated at the end of every SQL statement execution. There will be only one SQLCA for every application with executable SQL statements. But at the same time, every multi-threaded application will have more than one SQLCA.
The binding process uses DB2 as well as DBRM. Here, DBRM is nothing but a database request module. During the binding process, the SQL statements in the DBRM switch to operational mode. The binding process builds access paths to the DB2 table. Also, the binding process supports authenticating users along with validating SQL statements in the DBRM.
Monitor elements are nothing but the data structures used to update the status of the DB2. Following are a few examples of monitoring elements in DB2.
In its basic form, it is a rule that ensures data integrity in DB2. Only based on this rule, values are allowed to store in one or more columns of a base table. This means that values are stored in a base table after being verified by the check constraint. When you create a table in DB2, you can also create the CHECK constraint.
The clustering index decides how the rows must be physically ordered in a table space. Know that every table in DB2 comes with a clustering index. We can define clustering index on both partitioned tablespaces as well as segmented table space. Also, Clustering indexes are highly helpful in ordering operations, grouping operations, etc.
Two modes of security control are available in DB2, such as authentication and authorization.
Authentication is the security control used to allow users to access the DB2. It is managed by external systems.
The authorization is the security control that allows users to access data within the DB2. A database manager manages it.
Essentially, storage groups in DB2 represent a set of volumes on disks. This is where tables and indexes are stored in datasets. Virtual Storage Access Method (VSAM) catalog stores the datasets. With storage groups, DB2 defines, deletes, extends, and modifies VSAM datasets.
We can assign table spaces for storage groups. However, it is essential to note that table space is usually associated with only one storage group, whereas one storage group may be associated with many tablespaces.
Related Article: VSAM Interview Questions |
Cursor Stability (CS) |
Repeated Read (RR) |
It is the default isolation level and is used if you want maximum concurrency |
It is one of the isolation levels. |
It locks only the current row on which the cursor is positioned |
It and locks all the rows specified by the SQL statement |
Locks are released once the program moves to the next row |
All locks are held until a commit or rollback releases them |
CS offers good consistency and data integrity |
RR offers high-level data integrity due to its locking strategy |
Below is the list of DB2 advantages:
DB2 physically stores the NULL value by adding an extra byte in prefix to the column value.
We can use the SELECT statement if we want to retrieve rows from the DB2 table. When more than one row is returned, you can use the cursor to fetch a single row out of the multiple rows.
INTEGER |
SMALLINT |
It is used to store large binary integers with 31 bits |
It is used to store small binary integers with 15 bits |
The range of integers varies from -2147483648 up to +2147483647 |
The range of small integers varies from -32,768 up to +32,767 |
Advantages:
Disadvantages:
DELETE Statement |
DROP statement |
It deletes a row from a table or view |
It removes an object or table from the database |
Only the rows are deleted. No other associated entities are deleted. |
If an object is removed, the objects associated with the dropped object also get removed. It doesn’t matter whether they are associated directly or indirectly with the object. |
You can restore the rows using the ROLLBACK function |
You cannot restore the table or object. |
QMF |
SPUFI |
QMF stands for Query Management Facility |
SPUFI stands for SQL Processor Using File Input |
It is a query or reporting environment. You can format reports with QMF. |
It is a SQL execution engine. |
You can store your queries in tables and reports in QMF |
It needs datasets to store SQL statements as well as move them to DB2. |
It is an add-on product that comes with a cost |
It is absolutely a free product |
There is no need that all the DB2 users should have QMF |
Every DB2 customer will have SPUFI, undoubtedly. |
Simple |
Segmented |
Partitioned |
It contains one or more tables and stores rows for all tables |
It contains one or more tables |
It contains only one table rows |
Rows from multiple tables can be interleaved |
A tablespace is divided into multiple equal-sized segments. This is where tables are stored. |
A tablespace is divided into multiple parts. Here, rows are stored in all the parts. |
IMS, TSO, CICS, and BATCH are the four environments that can access DB2 seamlessly.
DB2 optimizer processes every SQL statement and selects a query access plan. It estimates the execution costs of multiple access plans and selects the access plan with the minimum cost.
When selecting the optimum access plan, the DB optimizer achieves it in the following way.
Pros:
Cons:
We can use the COUNT function to get the number of rows in a set of rows or values. The argument can be of any data type, such as BLOB, DBCLOB, CLOB, and XML.
It is essential to note that the result of this function will be an integer. And it cannot be a null value.
The argument of the COUNT (*) function is a set of rows. This function returns the number of rows in a set of rows.
The argument of the COUNT (expression) function is a set of values. This function will return the number of non-null values in the set. Note that this function includes duplicate values for counting.
The argument of the COUNT (DISTINCT expression) function is a set of values. This function will return the number of non-null values in the set. Note that this function removes duplicate values.
We can move the table space by using ALTER STOGROUP statement. This is possible only when the tablespace is allocated to that storage group.
When there is a program abend, DB2 performs the auto rollback function.
If we change the structure of a table, then we need to change the program. The change can be adding or deleting a field in the table.
Sometimes, it leads to I/O overhead due to the retrieval of columns that is not required for the users.
We create Materialized Query Table (MQT) to improve the performance of SQL applications.
If we want to create an MQT, we need to write a CREATE TABLE statement. We need to specify a full selection too. Then we need to include the DATA INITIALLY DEFERRED as well as REFRESH DEFERRED clauses to define an MQT.
Yes. We can remove many identical rows from the result table of a query. You need to use the DISTINCT keyword in the query to achieve this. This is because the DISTINCT keyword eliminates the duplicate rows from the query result table. As a result, every result table will contain only the unique data.
If this case, it will impact PLANS negatively. The PLANS may contain the SQLs using that dropped index. Simply put, the PLAN will be marked as invalid.
Know that locks are essential to achieve concurrency in DB2. Timeout, Suspension, and Deadlock are the contention situations created by locks. And they will degrade the performance of DB2, undoubtedly.
PLAN |
Package |
A plan contains many packages or DBRMs |
A package contains a single DBRM |
It is an executable unit since it has access path |
It is not an executable unit since it doesn’t have any access path |
Less preferred |
Highly preferred |
You can add a maximum of 133 volumes in a storage group.
It’s a wrap! We hope you have thoroughly learned the top IBM DB2 interview questions in one place. You will now be able to attend the IBM DB2 interviews with your polished knowledge and enhanced confidence. Keep in mind that hard work never fails. We advise you to read the blog periodically to keep the knowledge fresh. We wish you all great success and bag your dream job soon.
Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:
Name | Dates | |
---|---|---|
IBM DB2 Training | Dec 07 to Dec 22 | View Details |
IBM DB2 Training | Dec 10 to Dec 25 | View Details |
IBM DB2 Training | Dec 14 to Dec 29 | View Details |
IBM DB2 Training | Dec 17 to Jan 01 | View Details |
Madhuri is a Senior Content Creator at MindMajix. She has written about a range of different topics on various technologies, which include, Splunk, Tensorflow, Selenium, and CEH. She spends most of her time researching on technology, and startups. Connect with her via LinkedIn and Twitter .