DB2 Interview Questions and Answers

Interview- 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 questions and answers for experienced as well as entry level candidates or freshers you can say. 

DB2 Interview Questions and Answers 

• What is DB2?
• What are the major components of DB2?
• Explain the purpose of using COMMIT?
• What do you understand by the SQL Communications Area, and what are some of its key fields?
• Define what SQLCA is.
• Name a few fields from SQLCA?
• Discuss the DB2 bind?
• What do you understand by concurrency?
• Explain in brief about the function performed by the data manager.
• What is DBRM?
• What is Data page?
• What is RCT?
• How can the tablespace be possibly shifted to some other DASD volume that is allocated for that particular tablespace?
• Can you highlight the advantages that are attached to a PACKAGE?
• Describe a way of highlighting and putting a CURSOR to use in a COBOL program.
• What do you understand by Cursor Stability?
• Comment whether or not DCLGEN is mandatorily used. If not, then what is the point of using it in the first place?
• In case if at some point of time DB2 is down, would that impact the pre-compilation process of a DB2-COBOL program?
• What is a DBRM PLAN?
• Explain the function Of a Buffer Manager?
• What are the various contention situations caused by locks?
• What do you gather by the term NULL value?  What are the pros and cons of using NULLS?
• What happens when the base table underlying a view is restructured?
• 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?

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.

Read: SQL Operators you Need to Know

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 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 which 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 need 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 entrance way from the application program to the DB2 objects.

Q8). What do you understand by concurrency?

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

Q9). Explain in brief about 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 with the framework for performing functionalities like logging, securing and performing other I/O operations.

Read: What is Complex SQL Queries? Explain Complex SQL Queries with Examples

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

DB2 Interview Questions and Answers for Experienced

Q13). How can the tablespace be possibly shifted to some other DASD volume that is allocated for that particular tablespace?

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. 

Q14). Can you highlight the advantages that are attached to a PACKAGE?

Following are the points of interest connected to a PACKAGE.

• Avoid the expense of an enormous accumulation of tie. 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. 

Q15). 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.

Q16). What do you understand by Cursor Stability?

It is cursor stability that "tells" DB2 that database esteems perused 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.

Q17). 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 decreases the odds of mistakes.

Read: Introduction to SQL Injection and Attacks

Q18). In case if at some point of 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. 

Q19). What is a DBRM PLAN?

DBRM indicates Data Base 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.

Q20). 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).

Q21). 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 circumstance 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 discharge 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 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.

Q22). 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

Q23). 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.

Q24). 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.

Conclusion

These are some of the most frequently asked questions in a DB2 interview. You really need to pay good attention to the answers to these questions. Do read the list thoroughly before your interview. Good Luck!

Read: How to Install Microsoft SQL Server Express