Javatpoint Logo

91-9990449935

 0120-4256464

JavaTpoint

DB2 Interview Questions

A list of top frequently asked DB2 interview questions and answers are given below.

1) What is DB2? Explain.

DB2 is a relational database management system (RDBMS) product form IBM. It is a subsystem for MVS Operating System. It is designed to store, analyze and retrieve data efficiently.

2) Which components manage deadlocks in DB2?

Locking services are provided by Locking services component known as "Internal Resource Lock Manager" (IRLM) and manages concurrency issues and deadlocks.

3) On which levels locks can be applied?

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

4) How many types of page locks can be held in DB2?

Three types of page locks can be held in DB2:

  • Exclusive
  • Update
  • Share

5) What is the purpose of using COMMIT in DB2?

COMMIT is used to change the data permanently. It also allows to access data by other applications that can reference the committed data.

6) What are the data types available in DB2?

The data types available in DB2 are:

  1. SMALLINT
  2. INTEGER
  3. FLOAT
  4. DECIMAL
  5. CHAR
  6. VARCHAR
  7. DATE
  8. TIME

7) What is the picture clause of Null indicator variable?

S9(4)COMP is the picture clause of a null indicator variable.

8) What is the use of DB2 Optimizer?

  • DB2 Optimizer is used to process the SQL statement.
  • DB2 Optimizer also helps to select the access path.

9) Which component is used to execute the SQL statements?

Database Services component is used to execute the SQL statement. It also manages buffer pool.

10) Which component is responsible for DB2 startup and shutdown?

System Services component is responsible for handling DB2 startup and shutdown.

11) What is SQLCA?

SQLCA stands for SQL Communication Area. It is a structure of variables, which are updated after every execution of SQL statements.

If an application contains executable SQL statements then only one SQLCA is needed. FORTRAN needs more than one SQLCA to be provided. For Java, SQLCA is not applicable.

12) What is the maximum length of SQLCA?

136 is the maximum length of the SQLCA.

13) What is CHECK constraint in DB2?

CHECK constraint is defined as a condition or criteria to ensure data integrity. The CHECK constraints are created during the creation of the table and each value inserted or updated to the table is tested by CHECK constraint.

14) What is DB2 Bind?

DB2 bind is a process that builds access paths to the DB2 table. It uses Database Request Modules from the DB2 pre-compile step as input and produces an application plan. It checks for user authentication and validates SQL statements in the DBRM(s).

15) What is DBRM?

DBRM stands for Database Request Module. It is a component inside DB2, which is created by the pre compiler of DB2. It contains SQL source statements that get extracted out of the application program. DBRMs form inputs that are helpful in the binding process.

16) What is buffer Pool?

Buffer pool is a reserved main storage which is used as buffering requirements for one or more table spaces or indexes. It is made up of either 4K or 32K pages.

17) What is the use of data manager?

Data manager is a DB2 component that is responsible to manage physical database. It invokes other system components to perform logging, locking etc.

18) What is a storage group (STOGROUP)?

STOGROUP is a named collection of DASD volumes. It is used by index spaces and table spaces of the database.

19) What is predicate?

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

20) What is the physical storage length of TIMESTAMP data type?

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

21) What is Declaration Generator (DCLGEN)?

Declaration Generator (DCLGEN) is a facility used to form SQL statements that describes a table or view. At pre-compile time, the table or view descriptions are then used to check the validity of SQL statements.

22) What are the several buffer pools in DB2?

Following are the for buffer pool in DB2:

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

23) Which component is responsible for processing SQL statements and selecting access paths?

DB2 optimizer is used to select the access paths and for processing SQL statements.

24) What is concurrency?

Concurrency specifies that more than one DB2 application processes can access the same data at the same time. However, problems can happen such as, lost updates access to unrepeatable reads and uncommitted data.

25) Which isolation level provides maximum concurrency?

Uncommitted read provides maximum concurrency.

26) Which isolation level provides highest data integrity?

Repeatable Read provides highest data integrity as it holds page and lock the rows until a COMMIT point.

27) What is RCT?

RCT stands for Resource Control Table defined in 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.

28) What action DB2 takes when a program aborts in the middle of a transaction?

DB2 performs auto rollback when a program is aborted in the middle of some transaction.

29) Where can you declare a cursor in a COBOL-DB2 program?

A cursor can be declared either in Working Storage Section or in Procedure Division also.

30) How can you count the number of rows from a table TAB?

By applying the following query:

31) What is the maximum size of a CHAR data type in DB2?

The maximum size of a CHAR data type in DB2 is 254 bytes.

32) What is the maximum size of VARCHAR data type in DB2?

The maximum size of a VARCHAR data type in DB2 is 4046 bytes.

33) What is SPUFI?

SPUFI stands for SQL Processor Using File Input.

34) What is the information associated with SYSIBM.SYSLINKS table?

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

35) What is cursor stability?

Cursor stability is the property that tells the DB2 that the values of database that are read by making use of this application gets protected while the data is used.