Top 50 DB2 Interview Questions and Answers for Freshers & Experienced

Introduction

Those dreadful minutes of interaction that can make or break your career. Many times, it is seen that the candidates are really good and talented, but they experience interview anxiety. You can control this anxiety by preparing well for your interview. To prepare well for your interview, you need to take a good glance at what kind of questions are asked for the job role that you are applying for.

Today in this blog, we have collated a list of the most frequently asked questions for interviews that require a sound knowledge of DB2 related concepts and principles. The blog covers db2 mainframe interview questions and answers for experienced as well as entry-level candidates or freshers you can say. These DB2 mainframe interview questions will let you know about Subselect, Inner and Outer join, GroupBy, OrderBy, etc. DB2 questions have been written to get you acquainted with SQL databases.

DB2 Interview Questions and Answers for Fresher

Q1). What is DB2?

DB2 is a popular Database Management System used for the MVS Operating System where the DB2 is a subsystem of the MVS Operating System.

Q2). What Are the Major Components of DB2?

DB2 is an enormous relational database, the board framework with numerous segments in it. The four noteworthy segments of DB2 are:

• System services component: It underpins framework tasks, other correspondence, logging, and comparative capacities. It handles startup and shutdowns.
• Locking services component: It gives the fundamental controls to overseeing simultaneous access to information. This part is known as the Internal Resource Lock Manager (IRLM), and it deals with the simultaneousness issues and gridlocks.
• Database services component: It bolsters the definition, recovery, and update of client and framework information. It is in charge of the execution of SQL explanations, and it additionally deals with the buffer pool.
• Distributed data facility component: It gives DB2's, circulated database support.

Q3). Explain the Purpose of Using Commit?

The information changes can be caused perpetual by utilizing To submit. It likewise allows information to be gotten to by different applications that  can reference the submitted information.

Q4). What Do You Understand by the SQL Communications Area, and What Are Some of Its Key Fields?

It is an information structure that must be incorporated into any host-language program utilizing SQL. It is utilized to pass criticism about the SQL activities to the program. Fields are return codes, blunder messages, taking care of codes, and warnings.

Q5). Define what SQLCA is.

SQL Communication Area is a structure of factors, which are refreshed after each execution of SQL explanations. For an application that contains executable SQL proclamations, only one SQLCA is required. FORTRAN needs more than one SQLCA to be given. For Java, SQLCA isn't pertinent.

Q6). Name a Few Fields From Sqlca?

SQLCODE, SQLERRM, SQLERRD

Q7). Discuss the DB2 Bind?

The procedure that assembles get to ways to the DB2 table is known as the bind. The quandary utilizes Database Request Modules from the DB2 pre-arrange venture as information and produces an application plan. It checks for client confirmation and approves SQL explanations in the DBRM(s). DB2 bind procedure does the accompanying-

• Validates:- SQL statements are approved for valid tables, view, and section names. Linguistic structure blunders are checked so that these can be remedied.
• Verifies:- Bind procedure checks that the client or the procedure is approved to bind the arrangement.
• Selects:- Selects the entranceway from the application program to the DB2 objects.

Q8). What Do You Understand by Concurrency?

More than one DB2 application procedure can get similar information in the meantime, known as simultaneousness. Be that as it may, issues can happen; for example, lost updates access to unrepeatable reads and uncertain information.

Read: SSRS Sub Reports and deployment process-How to do it

Q9). Explain in Brief the Function Performed by the Data Manager.

Information chief can be considered as a part that is fit for dealing with the databases that are physically present and is equipped for conjuring different segments related to the framework for performing functionalities like logging, securing, and performing other I/O operations.

Read: SQL Server Tutorial for Beginners

Q10). What is DBRM?

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

Q11). What Is the Data Page?

Data page can be considered as a unit that is fit for recovering information from the database. The database from which the information can be recovered is as 4 kilobytes or 32 kilobytes. The structure wherein information is recovered relies upon how the table is characterized inside the database. The information page likewise contains data in regards to the index or client that are a piece of the database.

Q12). What is RCT?

RCT is extended as a Resource–Control Table and is characterized in the DB2/CICS district. This is the part that includes highlights that are assembled through macros of DSNCRCT. RCT matches with the exchange ID that of CICS, with the approval ID that of DB2. This ought to likewise be coordinated with plan ID.

Q13). What is a CURSOR and what is its use?

A CURSOR is a programming device that helps the SELECT statement find out a set of rows but displays them one at a time. This is because the host language can deal with only one row at a time.

Q14). How to retrieve rows from a DB2 table in Embedded SQL?

Rows from a DB2 table in Embedded SQL can be retrieved using the single-row SELECT statement. Another way is by using the CURSOR statement. However, the first process is the preferred one.

Q15). What is the result of the OPEN CURSOR statement?

In the simple OPEN CURSOR statement, the CURSOR is placed on the first row of a table. On the other hand, when the ORDER BY clause is added, rows would be fetched, sorted, and would be available for the FETCH statement.

Q16). Can there be more than one cursor open for any program?

Yes! The user can have more than one cursor open for any program.

Q17). How would the VARCHAR column REMARKS be defined?

The VARCHAR column REMARKS would be defined as follows:

• 10 REMARKS
• 49 REMARKS-LEN PIC S9(4) USAGE COMP
• 49 REMARKS-TEXT PIC X(1920)

Q18). What is the physical storage length of each of the data types, DATE, TIME, and TIMESTAMP?

The physical storage length of DATE is 4 bytes, TIME is 3 bytes, and TIMESTAMP is 10 bytes.

Q19). What is meant by DCLGEN?

DCLGEN refers to Declaration Generator that is used to create the host language copy books for the table definitions. It also creates the DECLARE table.

Q20). What are the contents of DCLGEN?

DCLGEN has the EXEC SQL DECLARE TABLE statement that gives the layout of a table/view in terms of DB2 data types. It can also be defined as the host language copy book giving the host variable definitions for column names.

Q21). What are some of the main fields in SQLCA?

Major fields in SQLCA are SQLCODE, SQLERRM, and SQLERRD.

Q22). What is meant by EXPLAIN?

EXPLAIN is used for displaying the access path as determined by the optimizer for SQL statements. It can also be used in the case of SPUFI for single SQL statements or in the BIND step for Embedded SQL.

Q23). How to perform EXPLAIN for any Dynamic SQL statement?

Users can use SPUFI or QMF statements to perform EXPLAIN for Dynamic SQL statements. They can also include the EXPLAIN command in the Embedded Dynamic SQL statements.

Q24). What are the isolation levels possible?

Two isolation levels are possible: One is Cursor Stability and the other is Repeatable Read denoted as CS and RR, respectively.

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

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

Q26). What is meant by Lock Escalation?

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.

DB2 Interview Questions and Answers for Experienced

Q27). How Can the Tablespace Be Possibly Shifted to Some Other Dasd Volume That Is Allocated for That Particular Tablespace?

The tablespace that you are utilizing is distributed distinctly to STOGROUP, at that point you can enter the direction ALTER STOGROUP for including just as erasing volume. REORG TABLESPACE and RECOVER TABLESPACE are articulations that are useful in making new STOGROUP that can point towards the new volume. Adjust tablespace and REORG, and RECOVER are proclamations utilized for modifying and recuperating the tablespace dispensed in the memory. 

Q28). Can You Highlight the Advantages That Are Attached to a Package?

The following are the points of interest connected to a PACKAGE.

• Avoid the expense of an enormous accumulation of ties. It is significantly more fitting to go for a little accumulation rather than a huge one.
• Ensure that you don't need to bring an enormous accumulation of individuals from DBRM together for a specific arrangement.
• In case if you wish to make changes in the program and these progressions, in the end, lead to mistakes, at that point you can diminish the fallback complexities with the assistance of PACKAGE.
• During the procedure of programmed official and rebinding of a specific arrangement, ensure that the absolute exchange appended to the procedure is inaccessible during the course. 

Q29). Describe a Way of Highlighting and Putting a Cursor to Use in a Cobol Program.

The ideal method for putting a CURSOR to use in a COBOL program is to utilize DECLARE CURSOR, which can be utilized either in system division activity or in working stockpiling. This is being done fundamentally to feature the SELECT articulation. Once DECLARE CURSOR is utilized, this is trailed by OPEN, FETCH lastly CLOSE.

Read: Top 50 Informatica Interview Questions and Answers

Q30). What Do You Understand by Cursor Stability?

It is cursor stability that "tells" DB2 that database esteems pursued by this application are secured just while they are being utilized. (Changed qualities are secured until this application comes to the submit point). When a program moves to start with one line then onto the next, different projects may peruse or the main column.

Q31). Comment Whether or Not Dclgen Is Mandatorily Used. If Not, Then What Is the Point of Using It in the First Place?

It isn't required to bring DCLGEN into utilization. The essential utilization of DCLGEN comes during the pre-arrangement stage, where it helps in identifying the incorrectly spelled section names. Since DCLGEN is only an apparatus, it will just create variable definitions for the host and decrease the odds of mistakes.

Read: What is NoSQL? NoSQL Tutorial Guide for Beginner

Q32). In Case if at Some Point in Time DB2 Is Down, Would That Impact the Pre-Compilation Process of a DB2-Cobol Program?

Regardless of whether DB2 is down sooner or later of time, and still, at the end of the day, it won't affect the pre-gathering procedure of DB2-COBOL program. The purpose behind the equivalent is that the pre-compiler never alludes to the inventory tables of DB2. 

Q33). What Is a DBRM Plan?

DBRM indicates DataBase Request Module. It has the SQL explanations that are removed from the host language program gotten by the pre-compiler. PLAN is the consequence of the BIND procedure and has executable code for SQL statements in DBRM.

Q34). Explain the Function of a Buffer Manager?

The buffer manager is the DB2 segment in charge of physically moving information between an outer medium and (virtual) stockpiling (plays out the genuine I/O activities). It limits the measure of physical I/O really performed with complex buffering techniques (i.e., read-ahead buffering and look-aside buffering).

Q35). What Are the Various Contention Situations Caused by Locks?

Locks assume a crucial job in keeping up the simultaneousness in the DB2 condition. The following are the dispute circumstances brought about by Locks, which can corrupt the DB2 execution.

• Suspension: This circumstance is experienced by an application when it demands the lock that is held by another application and can't be shared. This suspended procedure incidentally quits running and continues when every one of the procedures that hold the clashing lock discharges them.
• Timeout: This circumstance is experienced by an application procedure when it ends due to a suspension that surpasses the preset dimension. DB2 then ends the procedure, issues a message, and returns the mistake codes.
• Deadlock: This circumstance happens when at least two application forms, each hold bolts on assets that others need and without which they can't continue.

Q36). What Do You Gather by the Term Null Value?  What Are the Pros and Cons of Using Nulls?

A NULL worth takes up one byte of capacity and shows that worth is absent instead of space or zero worth. It's what might be compared to TBD on an authoritative outline and regularly effectively depicts a business circumstance. Sadly, it requires additional coding for an application program to deal with this circumstance.

Q37). What Happens When the Base Table Underlying a View Is Restructured?

The table and its view are re-built from scratch, but the programs that are accessing the view do not have to be altered in case the view and attributes that have been accessed remaining the same.

Q38). What Are the Circumstances That Will Permit DB2 to Allow an SQL Statement to Update More Than One Primary Key Value at a Given Time?

Never does DB2 allow such a thing.  Such dispensation could yield more duplicate values after violating entity integrity. The Primary keys in DB2 must, in all cases, be updated one at a time only.

Q39) What Is the DB2 Precompilation Process?

In the DB2 precompilation process where DB2 precompiler scans the program & copies all the SQL statements & hosts variable data into database request module (DBRM). The precompiler also returns the modified source code so that SQL statements aren’t causing any errors when the program is compiled.

Q40). What is SQLCA?

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.

Q41). How many SQLCA must be provided to an application that contains SQL statements (executable)?

Only one SQLCA is needed for an application that contains executable SQL statements. However, SQLCA is not applicable to Java applications.

Q42). Do you know any programming language where more than one SQLCA is used?

Yes, FORTRAN applications require more than one SQLCA.

Q43). What is the maximum length of SQLCA?

The maximum length of SQLCA is 136.

Q44). Is it possible for you to alter the table (adding a column to it) while some other person is accessing the table and even updating some values in it?

To be short and crisp, the answer is yes, we can alter the table at the same time when some other transaction is taking place but this does not exactly happen as it sounds to be.

The DB2 table will not change its structure in any form till a transaction or multiple transactions are taking place. The table-altering commands will be stored by the database engine and will not be executed till all the transactions are complete. After one user has modified the values in the table, then the column will be added.

Q45). Let us say that we have a Student Database in which we have the details of the students like their names, address, attendance percentage, etc. Now we run an SQL Query that says: SELECT AVG ATTENDANCE FROM STUDENTS.

Can you tell me any issues that may be here?

Yes, there might be an issue. For instance, if there is such a case where we have set that the attendance of any student cannot be null and at the same time we have some students in the table whose attendance we don’t know, the AVG function will calculate these students as well. Hence, we will get an answer but, it might not be correct.

Learn SQL Server from top instructor. Enroll in SQL Server Training now!

Q46). What are ACQUIRE and RELEASE in BIND?

A certain point in a program when DB2 acquires or releases the locks against the tables and/or tablespaces. These include the intent locks as well.

Q47). In a COBOL-DB2 program, where are all the SQL statements coded?

In a COBOL-DB2 program, all the statements are coded in Area B.

Learn More On: How To Use DB2/Other Data Storage As Data Source In SSRS

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

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

Read: Delete vs Truncate SQL Server – What are the Differences?

Q49). Let us say that I have a program it uses dynamic SQL. It has been working fine till now, however, lately, I have found that the performance has been affected and deteriorated. Can you guess what can be the reason behind this?

There can be 2 possibilities:

• RUN STATS is not done and it seems that the program is using an incorrect/wrong index due to incorrect/wrong stats.
• It might also be the case that the RUN STATS has been done but the optimizer has chosen a wrong access path based on the latest statistics.

Q50). What is the RUN STATS that was mentioned in the previous answer?

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.

Q51). Is there any specific reason why SELECT is not preferred in embedded SQL programs?

The SELECT statement is not preferred in embedded SQL programs for 3 reasons:

• If there is a change in the structure of the table by addition or deletion of a field, the program gets modified and using the SELECT statement might retrieve those columns that the user may not even use.
• The use of SELECT in the embedded SQL programs can lead t the input-output overhead and,
• The chances of an index-only scan are eliminated by the use of SELECT in embedded SQL programs. 

Q52). What is DSNDB07? What does it do?

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

Q53). How will you achieve record level locking in DB2 versions where record level locking is not allowed?

We can achieve the record level locking in DB2 versions where the record level locking is not allowed by having the length of the record greater than that of a page itself.

Final Thoughts on DB2 Interview Questions and Answers!

These are some of the most frequently asked questions in a DB2 interview. The DB2 interview questions and answers will let you understand the executable codes or SQL Server queries to let you know how confident you are for the interview. The DB2 interview questions are framed by JanBask experts who give training to IT industrialists. You really need to pay good attention to the answers to these questions. Do read the list thoroughly before your interview. Good Luck!

Through Janbask’s SQL Server Training, you’ll learn the skills to excel in real-wold data and storage scenarios.