DB2 Interview Questions

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

1) What is DB2? Explain.

DB2 also known IBM Db2 is a relational database management system (RDBMS) product form IBM. It is designed to store, analyse 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) How can you classify the locks in DB2?

Locks can be classified based on size, duration and mode.

4) On which levels locks can be applied?

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

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

Three types of page locks can be held in DB2:

• Exclusive
• Update
• Share

6) What is the use of COMMIT in DB2?

COMMIT is used to change the data permanently by commits the database changes occur in the current transaction and made that changes permanent.

7) What are the various 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

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

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

9) What is the use of DB2 Optimizer?

• DB2 Optimizer is used to process the SQL statement.
• DB2 Optimizer can be used to enhance the performance of SQL.

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

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

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

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

12) What is SQLCA?

SQLCA stands for Server Query Language Communication Area. Basically, it is a collection of variables that may be updated after the execution of every SQL statement.

A program having SQL executable statement can provide maximum one SQLCA whereas in Java it is not applicable.

13) What is the maximum length of SQLCA?

136 is the maximum length of the SQLCA.

14) Give the name of some fields form SQLCA.

The following three are the fields from SQLCA:

• SQLCODE
• SQLERRM
• SQLERRD

15) What is CHECK constraint in DB2?

Check constraint is a database rule that checks data integrity. Thus, only values from the domain for the attribute or column are allowed.

16) What is DB2 Bind?

DB2 bind is process that prepares an access path to the data. This access path is stored as a package in the DB2 catalogue.

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

18) What is buffer Pool?

Buffer pool is part of main memory space. This space is allotted by the database manager. It cache table and index data from the disk.

19) What is data manager?

Data manager is a DB2 component that is responsible to manage physical database. It performs logging and locking by invoking other system components.

20) What do you mean by storage group (STOGROUP)?

Storage group is a storage path where data can be stored. A table space can also be assigned to storage group.

21) What is the use of predicate?

Predicates are used to enhance the performance of query.

22) What is the physical storage length of TIME data type?

The physical storage length of TIME data type is 3 bytes.

23) What is the physical storage length of DATE data type?

The physical storage length of TIME data type is 4 bytes.

24) 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

25) What is DCLGEN (Declaration Generator)?

The declaration generator provides an SQL Declare Table statement and language like COBOL, C data declaration for a particular table or view.

26) What is the default page size of buffer pools?

The default page size of buffer pool is 4kb.

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

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

28) What is concurrency in DB2?

Concurrency is the process in which more than one application can access the similar data at same time.

29) Which isolation level provides maximum concurrency?

Uncommitted read provides maximum concurrency.

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

31) What is RCT?

Resource Control Table (RCT) is controller that directs the CICS DB2 interface. Using DSNCRCT, RCT can produce a micro table.

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

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

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

By applying the following query:

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

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

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

37) What is SPUFI?

SPUFI stands for SQL Processor Using File Input.

38) What is role in DB2?

In DB2, a role is an object of database that group various privileges together and can be assigned to groups or user by GRANT statement.

39) What is cursor stability in DB2?

Cursor stability ensures that any row that has been changed by activation group with a dissimilar commitment definition cannot be read before committing.

40) What is the reason behind not using SELECT * in Embedded SQL programs?

There are three reasons for not using SELECT * in embedded SQL programs:

• If you change the table structure i.e. adding a field, the program will have to be modified.
• Program can retrieve the columns which it might not use, leading an I/O overhead.
• The chance of an index only scan is lost.

41) What is the usage of OPEN CURSOR command?

If you use the OPEN CURSOR command with ORDER BY clause, the rows are fetched, sorted and made available for the FETCH statement. Otherwise simply the cursor is placed on the first row.

42) What is the COBOL picture clause of the DB2 data types DATE, TIME, TIMESTAMP?

DATE: PIC X(10)

TIME PIC X(08)

TIMESTAMP PIC X(26)

43) What is DCLGEN?

DCLGEN is stands for DeCLarations GENErator. It is used to create the host language copy books for the table definitions. It also creates the DECLARE table.

44) What are the contents of a DCLMGEN?

• EXEC SQL DECLARE TABLE statement which gives the layout of the table in terms of DB2 data type.
• A host language copy book that gives the host variable definitions for the column name.

45) What are the advantages of using a package?

The package provides following advantages:

• Modularity
• Easy to design the applications
• Better performance
• Hiding information
• Added functionality
• Overloading