Top Interview Questions & Answers | Learn Now

DB2 Interview Questions and Answers | Basic and Advnaced Levels

Written by Bhavya Sri | May 26, 2018 4:20:07 AM

Q1. Define DB2.

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

Q2. Which component checks DB2 deadlocks?

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

Q3. What is the purpose of using COMMIT?

Ans: 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

Q4. List out the Data types available.

Ans: The Data types available here are:

Ans: 
 
  • It processes SQL statements.
  • It helps to select the access path

Q6. Define SQLCA.

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

Q7. Define CHECK constraint.

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

Q8. Which component checks DB2 deadlocks?

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

 

Q9. Which component is responsible for execution of SQL statements?

 

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

Ans: System services component handles DB2 startup and shutdown.

 

Q11. On which level we can apply the locks?

 

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

Q12. In which step we specify the isolation level?

Ans: In Bind step we specify the isolation level.

Q13. What is SQLCA’s maximum length?

Ans: 136 is the maximum length of the SQLCA.

Q14. Which field in SQLCA shows the number of updated rows after update statement?

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

Q15. What is the physical storage length of TIMESTAMP data type?

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

Q16. What is the physical storage length of DATE data type?

Ans: DATE data types take 4 bytes and default is 'YYYY-MM-DD'

Q17. Which component is used to processes SQL statements and selects the access paths?

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

Q18.  In a COBOL-DB2 program all SQL statements must be coded in Area A. State whether true or false?

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

Q19. Cursors are used to handle single row selections at a time. They are data structure which hold all the results of a query. State whether true or false?

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

Q20. Host variables cannot be group items but they may be grouped together in host structure. They can be Renamed or Redefined. State whether true or false?

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

Q21. List out the buffer pools in DB2

Ans: There are four buffer pools in DB2 and they are:

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

Q22. Define clustering index.

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

Q23. What is concurrency?

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

Q24. Explain the Function done by data manager?

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

Q25. What happens in BIND step in a DB2 program?

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

Q26. What does a positive SQL code indicates?

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

Q27. Which statement is used to revoke the access from a database?

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

Q28. Which statement is used to authorize access on database tables?

Ans: GRANT keyword is used to grant privileges on DB2 tables.
View full post