DB2 Interview Questions | Eklavya Online

DB2 Interview Questions

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

Three types of page locks can be held in DB2:

  • Exclusive
  • Update
  • Share

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.

Uncommitted read provides maximum concurrency.

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

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.

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

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.

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

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

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

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.

DATE: PIC X(10)

TIME PIC X(08)

TIMESTAMP PIC X(26)

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

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

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

The data types available in DB2 are:

  • SMALLINT
  • INTEGER
  • FLOAT
  • DECIMAL
  • CHAR
  • VARCHAR
  • DATE
  • TIME

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.

136 is the maximum length of the SQLCA.

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.

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

SPUFI stands for SQL Processor Using File Input.

The following three are the fields from SQLCA:

  • SQLCODE
  • SQLERRM
  • SQLERRD

The default page size of buffer pool is 4kb.

  • BP0
  • BP1
  • BP2
  • BP32

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

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

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

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.

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

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

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

Predicates are used to enhance the performance of query.

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

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

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

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

The package provides following advantages:

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

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

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

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

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

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

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

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.

By applying the following query:

SELECT COUNT(*) FROM TAB