DB2 Interview Questions ~ GNIIT Solutions

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

7) What are the data types available in DB2?

The data types available in DB2 are:

SMALLINT
INTEGER
FLOAT
DECIMAL
CHAR
VARCHAR
DATE
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 also helps to select the access path.

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

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

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

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

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

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

21) What is predicate?

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

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

26) What are the several buffer pools in DB2?

Following are the for buffer pool in DB2:

BP0
BP1
BP2
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?

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.

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?

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.

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:

SELECT COUNT(*) FROM TAB

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 the information associated with SYSIBM.SYSLINKS table?

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

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

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 structure of the table 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

Monday, March 12, 2018