DB2 interview question set 1/DB2 Interview Questions and Answers for Freshers & Experienced

Mention the way of highlighting as well as putting a CURSOR to use in a COBOL program.

The best way of putting a CURSOR to use in a COBOL program is to make use of DECLARE CURSOR, which can be used either in procedure division operation or in working storage. This is being done basically to highlight the SELECT statement. Once DECLARE CURSOR is used, this is followed by OPEN, FETCH and finally CLOSE.

In an SQL table that is embedded, what is the procedure to retrieve rows that are part of a DB2 table?

Either you can use SELECT statements of single rows or an alternative way is to use CURSOR.

Comment whether the cursor is closed during COMMIT or not.

Yes. The cursor is closed during COMMIT.

Distinguish between segmented, simple, and partitioned tablespaces:

A simple tablespace and a segmented tablespace are used to accommodate more than one table. Records are held in a single statement present in the segments held in the pages of segmented tablespaces.

Explain correlated sub-queries.

Correlated sub-queries are those queries wherein the nester query on the inner side refers directly back to outer query’s table. For each and every row that is qualified, the evaluation of correlated sub-query is a must.

Mention a credible reason why SELECT* is never given preference in an SQL program that has been embedded.

There are primarily three reasons why SELECT* is never given preference in an embedded SQL program. These are: –

>> In case if there is an alteration in the structure of the table, then the program has to undergo a modification process.
>> All columns will be retrieved by the program including those columns , which might not be used.
>> In case if the user wishes to scan the index, then that will not be possible.

What is the information associated with SYSIBM.SYSLINKS table?

This is the table that contains information on the links that exists between the tables created through referential constraints.

How can tablespace be moved to another DASD volume that is allocated for that tablespace?

Tablespace that you are using is allocated only to STOGROUP, then you can enter the command ALTER STOGROUP for adding as well as deleting volume. REORG TABLESPACE and RECOVER TABLESPACE are statements that are helpful in creating new STOGROUP that can point towards the new volume. ALTER tablespace and REORG and RECOVER are statements used for altering and recovering the tablespace allocated in the memory.

Explain about RCT.

RCT is expanded as Resource – Control Table and is defined in the DB2/CICS region. This is the component that comprises of features that are gathered through macros of DSNCRCT. RCT matches with the transaction ID that of CICS, with the authorization ID that of DB2. This should also be matched with plan ID.

Explain the Function done by data manager?

Data manager can be considered as a component that is capable of managing the databases that are physically present and is capable of invoking other components associated with the system for performing functionalities like logging, locking and in performing other I/O operations.

What is concurrency?

More than one DB2 application process can access the same data at the same time, is known as concurrency. However, problems can happen such as, lost updates access to unrepeatable reads and uncommitted data.

Define predicate?

Predicate is an element of a search condition. It expresses or implies a search condition.

What do you mean by a recovery log?

A recovery log is used to identify all the changes formulated in the database. A recovery log can recover the sequence of events that had taken place before the failure when the system failure had occurred.

Distinguish between segmented, simple, and partitioned tablespaces:

A simple tablespace and a segmented tablespace are used to accommodate more than one table. Records are held in a single statement present in the segments held in the pages of segmented tablespaces.

Describe the four environments that access DB2:

IMS, CICS, TSO, and BATCH are the four environments that can access DB2.

Define COPY PENDING status?

COPY PENDING status means an image copy to be taken still pending, and therefore, till that time table cannot be updated. To withdraw this status, one can get the image copy. REPAIR command can be used to eradicate the COPY PENDING status.

Explain UNION and UNION ALL:

UNION and UNION ALL are used to collaborate the results provided by multiple SELECT statements. UNION eliminates duplicates present in the table, whereas UNION ALL retains the duplicates.

Define the value function purpose:

Two tasks can be performed using the value function:

-To avoid negative SQLCODEs by taking care of Nulls and 0s in the computation.

-Nulls can be substituted for numeric values used in the computation.

What does the buffer manager do?

The buffer manager uses some extremely sophisticated buffering techniques to reduce the amount of physical output and input. He also transfers data between an external medium and virtual storage.

Describe check constraint.

Check constraint enables the insertion or updating of the table, which ensures data integrity by considering what values are granted permission.

Explain DB2 isolation levels:

The DB2 isolation levels are Cursor Stability (CS), Red Stability (RS), Repeatable Read (RR), and Uncommitted Read (UR). UR is used to get access to read-only queries and read-only tables. CS can lock or unlock currently referred rows by the cursor. RS enables the result set stability and unlocks the rows that don’t coordinate with query predictions. RR, known for higher isolation level, ensures the data’s integrity for the complete duration of the entry.

What is DB2 on a cloud?

It is also known as “dash DB for Transactions”, it is a cloud SQL database that is fully managed with a high-availability option that has 99.99 per cent uptime SLA. DB2 on the cloud presents independent scaling of computing and storage and rolling security updates.

What is meant by SQL Communication Area?

An SQLCA is a collection of variables that are updated after the implementation of SQL statements. Only one SQLCA is needed for an application that contains executable SQL statements.

What does Data Warehousing use?

To run in multiple environments like private cloud, on-premise, and various public clouds, whether managed and unmanaged, DB2 Warehouse uses Docker containers. DB2 Warehouse can be employed as software, appliance, Intel x86, mainframe, and Linux platforms.

What Is A Host Variable?

This is a data item that is used in an SQL statement to receive a value or to supply a value. It must be preceded by a colon (:) to tell DB2 that the variable is not a column name.

What Does Dsndb07 Database Do?

DSNDB07 is where DB2 does its sorting. It includes DB2’s sort work area and external storage.

What Is Declaration Generator(dclgen)?

DCLGEN is a facility that is used to generate SQL statements that describe a table or view. These table or view descriptions are then used to check the validity of other SQL statements at precompile time. The table or view declares are used by the DB2I utility DCLGEN to build a host language structure, which is used by the DB2 precompiler to verify that correct column names and data types have been specified in the SQL statement.

What Is A Data Page?

A data page is a unit of retrievable data, either 4K or 32K (depending on how the table is defined), containing user or catalog information.

What Is A Database Request Module(dbrm)?

A DBRM is a DB2 component created by the DB2 pre-compiler containing the SQL source statements extracted from the application program. DBRMs are input to the bind process.

What Is The Function Of The Data Manager?

The Data Manager is a DB2 component that manager the physical databases. It invokes other system components, as necessary, to perform detailed functions such as locking, logging, and physical I/O operations (such as search, retrieval, update, and index maintenance).

What Is Cursor Stability?

It is cursor stability that “tells” DB2 that database values read by this application are protected only while they are being used. (Changed values are protected until this application reaches the commit point). As soon as a program moves from one row to another, other programs may read or the first row.

What Is Meant By Concurrency?

Concurrency is what allows more than one DB2 application process to access the same data at essentially the same time. Problems may occur, such as lost updates, access to uncommitted data, and un-repeatable reads.

What Will The Commit Accomplish?

COMMIT will allow data changes to be permanent. This then permits the data to be accessed by other units of work. When a COMMIT occurs, locks are freed so other applications can reference the just committed data.

What Is A Clustering Index?

It is a type of index that (1) locates table rows and (2) determines how rows are grouped together in the tablespace.

On The Create Tablespace, What Does The Close Parameter Do?

CLOSE physically closes the tablespace when no one is working on the object. DB2 (release 2.3) will logically close tablespaces.

How Many Buffer Pools Are There In Db2?

There are four buffer pools: BP0, BP1, BP2, and BP32.

Explain various types of Locks in DB2?

The lock modes in DB2 are used to determine whether one lock is compatible to another lock.
<> S Lock (Share): This mode allows the Lock owner and any concurrent process to read but not change the locked page or row.
<> U Lock (Update): This mode allows the Lock owner to read but not change the locked page. This lock mode reduces the chance of deadlocks.
<> X Lock (Exclusive): This mode allows the Lock owner to read or change the Locked page or row.

What are the full forms of SPUFI and DCLGEN and why are they used?

SPUFI- SQL Processing Using File Input
Usage- It allows direct input of SQL commands in the time sharing option environment.
DCLGEN- DeCLarations GENerator
Usage- It is used to create the DECLARE table and to create the host language copy books for the table definitions.

What is a buffer pool?

A buffer pool is the main storage that is reserved to satisfy the buffering requirements for one or more tablespaces or indexes, and is made up of either 4K or 32K pages.

Give a brief description of DB2 Isolation Levels?

UR- Uncommitted Read:
It is good for accessing read-only tables and read-only queries.
No record Locking is done.

CS- Cursor Stability:

<> It is a default isolation level.
<> It locks or unlocks each row at a time.
<> It guarantees to return only data which was committed at the time of reading.

RS- Read Stability:

<> It releases Locks on rows that do not satisfy the query predicates.
<> It is used for result set stability or when the future actions on returned rows may be taken.

RR- Repeatable Read:

<> It allows the application to retrieve and operate on rows as many times as needed.
<> It locks the entire table.

What is meant by AUTO COMMIT?

AUTO COMMIT is a SPUFI option that commits the effects of SQL statements automatically if they are successfully executed.

What is meant by the attachment facility?

The attachment facility is an interface between DB2 and TSO, IMS/VS, CICS, or batch address spaces. It allows application programs to access DB2.

What information is used as input to the binding process?

The database request module produced during the pre-compile. The SYSIBM.SYSSTMT table of the DB2 catalog.

Describe major components of DB2?

DB2 is the huge relational database management system with many components in it. The four major components of DB2 are:
>> System services component: It supports system operations, other communication, logging and similar functions. It handles startup and shutdowns.
>> Locking services component:It provides the necessary controls for managing concurrent access to data. This component is known as Internal Resource Lock Manager (IRLM) and it manages the concurrency issues and deadlocks.
>> Database services component: It supports the definition, retrieval, and update of user and system data. It is responsible for the execution of SQL statements and it also manages the buffer pool.
>> Distributed data facility component: It provides DB2’s, distributed database support.

Explain PACKAGE in DB2 and its advantages?

PACKAGE:They are units, which consists of many executable codes that are meant for SQL statements for one respective DBRM (Database request module).
Below are some advantages of Packages:

<> It ensures that you do not have to bring a large collection of DBRM members together for a particular plan.
<> In case of making changes in a program that will eventually lead to errors, then the decrease in fallback complexities can be made with the help of Package.
<> It is advisable to go for a small collection instead of a large one. Abstain from the cost of large collection of a bind.
<> In the course of automatic binding or rebinding of a specific plan, ensure that the total transactions attached to the process are unavailable.

What is Buffer pool and list some of them?

It is a reserved main storage which is to satisfy the buffer needs for one or more tablespaces or indexes. It is made up of either 4K or 32K pages.
There are four Buffer pools in DB2.

<> BP0
<> BP1
<> BP2
<> BP32

Mention data types used in DB2 ?

The data types used in DB2 are:


What is an alias?

It is an alternate name that can be used in SQL training Hyderabad statements to refer to a table or view in the same or remote DB2 subsystem

What is an access path?

The path that is used to get to data specified in SQL statements.

What is DB2 ( IBM Database 2 )?

DB2 is a subsystem of the MVS operating system. It is a Database Management System ( DBMS ) for that operating system.

R4R Team
R4R provides DB2 Freshers questions and answers (DB2 Interview Questions and Answers) .The questions on R4R.in website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,DB2 interview question set 1,DB2 Freshers & Experienced Interview Questions and Answers,DB2 Objetive choice questions and answers,DB2 Multiple choice questions and answers,DB2 objective, DB2 questions , DB2 answers,DB2 MCQs questions and answers Java, C ,C++, ASP, ASP.net C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for DB2 fresher interview questions ,DB2 Experienced interview questions,DB2 fresher interview questions and answers ,DB2 Experienced interview questions and answers,tricky DB2 queries for interview pdf,complex DB2 for practice with answers,DB2 for practice with answers You can search job and get offer latters by studing r4r.in .learn in easy ways .