Updated IBM DB2 Interview Questions and Answers

Q1. What is IBM Db2 used for?

Ans

IBM Db2 is a family of data management products, including the Db2 relational database. The products feature AI-powered capabilities to help you modernize the management of both structured and unstructured data across on-premises and multicloud environments.

Q2. What is DB2?

Ans

DB2 in general is a family of data management products. It is developed by IBM. The AI-powered family of data management tools helps us in the management of both structured as well as unstructured data. The DB2 relational database is a relational database that is part of the DB2 family of data management tools. 

Q3. What is the difference between the above two queries i.e. DELETE and DROP?

Ans

The DELETE * FROM table-name query deletes all the rows from the table but, there is still an empty table with no rows and no columns in the database. However, the DROP TABLE table-name query deletes all the rows and columns and along with that, the table gets deleted from the database too i.e. the table is completely removed (deleted) from the database. 

Q4. What are UNION and UNION ALL in DB2? 

Ans

The union command is used to merge 2 or more SELECT statements and the select statements can be applied on a single table as well as on multiple tables. The major difference between UNION and UNION ALL is that UNION removes the duplicate rows when it is applied on the tables whereas UNION ALL retains the duplicates.

Q5. What is concurrency in terms of DB2?

Ans

Concurrency means that more than one application process of DB2 can access the data at the same time. Concurrency can cause some issues like lost updates, access to unrepeatable reads and uncommitted data.

Q6. What is DCLGEN in DB2?

Ans

DCLGEN stands for Declarations generator. The programs that we write should declare the tables and views that it accesses. The declarations generator i.e. DCLGEN produces these DECLARE statements for C, Cobol and PL/I programs so that one does not need to code the statements manually. It also generates corresponding host variable structures. When DCLGEN is used to generate a table declaration, the DB2 uses the DB2 catalog to get the relevant information from it. DCLGEN, then uses this information to create an SQL DECLARE TABLE statement for the table/view.

Q7. What is SQLCA?

Ans

The full form of SQLCA is the SQL communication area or Structured Query Language Communication Area. It is a structure of variables that is updated after every execution of an SQL statement.

Q8. What are SPUFI and QMF? What is the difference between them?

Ans

• SPUFI: SPUFI is an execution engine for SQL where the SQL commands are executed. Several SQL queries can be executed at once and the result of the queries can be stored in PS or PDS members. Also, the SQL is known to us even after the execution of the query.
• QMF: It is a reporting environment and supports the formatting of reports. More than one query cannot be executed and the result of the query cannot be persisted. Also, the SQL code of the query cannot be known.

Q9. What is DSNDB07?

Ans

DSNDB07 is a database where the DB2 performs its sorting operations. It includes the sort work-area of DB2 and external storage.

Q10. What is the RUN STATS?

Ans

RUN STATS is a DB2 utility used to collect statistics about the data value in TABLES. It is used by the optimizer to decide the access path. RUN STATS also collects statistics that are used for space management. These statistics are stored in the DB2 catalog tables.

Usually, the RUN STATS is run after the following:

• After a load.
• After a mass update.
• After any major deletions, insertions, or
• After REORG the table

Q11. What is meant by Lock Escalation?

Ans

Lock escalation is the process of promoting page lock sizes to table or table space lock size when the transaction has acquired more locks than the ones specified in NUMLKTS. Locks have to be taken on objects in a single table space for escalations to take place.

Q12. What are the advantages of using PACKAGES?

Ans

When used, PACKAGES help avoid binding of large numbers of DBRM members in one plan. They also dispense with the cost of large BIND and avoid the entire transactions making them unavailable during BIND and automatic REBIND of the plan. Another advantage is that they minimize the fallback complexities when changes result in an error.

Q13. What do you understand by DBRM and PLAN?

Ans

DBRM denotes Database Request Module. It has the SQL statements that are extracted from the host language program obtained by the pre-compiler.

PLAN is the result of the BIND process and has executable code for SQL statements in DBRM.

Q14. What is the difference between CS and RR isolation levels?

Ans

CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of a transaction.

Q15. Why SELECT is not preferred in Embedded SQL programs?

Ans

SELECT is not preferred in Embedded SQL programs for three reasons. First, if the table structure is changed by the addition or deletion of a field and the program is modified, then using SELECT might retrieve the columns that the user may not use. This would lead to Input-Output overhead and the chances of index-only scan are also eliminated.

Q16. What is a Resource Control Table (RCT)? Describe its characteristics?

Ans

The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics that are assembled via the DSN CRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID and plan ID ( CICS attachment facility).

Q17. Describe what a storage group (STOGROUP) is?

Ans

A STOGROUP is a named collection of DASD volumes to be used by table spaces and index spaces of databases. The volumes of STOGROUP must be of the same device type.

Q18. What is the SQL Communications Area and what are some of its key fields?

Ans

It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the SQL operations to the program. Fields are return codes, error messages, handling codes, and warnings.

Q19. How does DB2 determine what lock-size to use?

Ans

• Based on the lock-size given while creating the tablespace.
• Programming can direct the DB2 what lock-size to use
• If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE