DB2 Interview Questions and Answers

DB2 Interview Questions and Answers

1. Define DB2.

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

Locking services are provided by Locking services component which is known as Internal Resource Lock Manager (IRLM) and manages concurrency issues and dead locks.

2.) What is the purpose of using COMMIT?

The data changes can be made permanent by using COMMIT. It also permits data to be accessed by other applications who can reference the committed data

3. List out the Data types available.

The Data types available here are:

 

4. What are the uses of DB2 Optimizer?

    1. It processes SQL statements.
    1. It helps to select the access path

5. Define SQLCA.

SQL Communication Area is a structure of variables, which are updated after every execution of SQL statements. For an application that contains executable SQL statements, only one SQLCA is needed. FORTRAN need more than one SQLCA to be provided .For Java, SQLCA is not applicable.

200px-Applications-database.svg

6. Define CHECK constraint.

It is specified as a condition or criteria to ensure data integrity. A value to be inserted or updated to a table is tested by CHECK constraint. The CHECK constraints are created during the creation of the table.

Locking services are provided by Locking services component which is known as Internal Resource Lock Manager (IRLM) and manages concurrency issues and dead locks.

Database services component is responsible for execution of SQL statements and it manages buffer pool also.

System services component handles DB2 startup and shutdown.

Locking can be applied on either of these − Page, table and table space.

In Bind step we specify the isolation level.

12. What is SQLCA’s maximum length?

136 is the maximum length of the SQLCA.

Check the value of SQLERRD to know how many rows got updated after an update statement.

TIMESTAMP data type takes 10 bytes and default is YYYY-MM-DD:HH:MM:SS-NNNNNN

DATE data types take 4 bytes and default is ‘YYYY-MM-DD’

DB2 optimizer is used to select the access paths & to process the SQL queries.

This statement is incorrect as SQL statements must be coded in Area B.

This statement is incorrect as cursors are used to handle multiple row selections at a time.

This statement is incorrect as host variables cannot be renamed & redefined.

20. List out the buffer pools in DB2

There are four buffer pools in DB2 and they are:

    1. BP0
    2. BP1
    3. BP2
    4. BP32

21. Define clustering index.

Clustering index is a type of index, which locates the table rows and determines how to group the rows together in the tablespace.

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

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

 24. 

Bind step converts all the SQL statements into executable form in COBOL-DB2 program.

A negative SQL code indicates a failure while a positive one indicates an exception.

REVOKE statement is used to take away the permissions from a Database.

GRANT keyword is used to grant privileges on DB2 tables.

Summary
Review Date
Reviewed Item
Thank you for this interview questions
Author Rating
51star1star1star1star1star
Share