IBM DB2 Technical Interview Questions Answers

What is the picture clause of Null Indicator variable?

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

Which component checks DB2 deadlocks?

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

Which component is responsible for execution of SQL statements?

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

Which component handles DB2 startup and shutdown?

System services component handles DB2 startup and shutdown.

On which level we can apply the locks?

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

In which step we specify the isolation level?

In Bind step we specify the isolation level.

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

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

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

What is the physical storage length of DATE data type?

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

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

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

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

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

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?

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

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?

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

When a table is dropped all the alias get dropped automatically. State whether true or false?

This statement is incorrect. Synonyms get dropped automatically when we drop the table but alias of the table remains there. We need to manually drop the alias.

What action DB2 takes when a program abends?

When a program abends in middle of some transaction then DB2 performs a auto rollback.

If a transaction takes a Update lock on some data, then other transactions can get what type of lock?

If a transaction takes an Update lock on some data, then other transactions can only get only Shared lock on it.

If a transaction takes a Shared lock on some data, then other transactions can get what type of lock?

If a transaction takes a Shared lock on some data, then other transactions can get either a Shared or Update lock on it.

How the locks can be classified?

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

Which utility is used to load data into a table from a sequential file?

Load utility is used to load data into tables.

Which utility reclaims space from pages when some rows were deleted?

Reorg utility is used for reorganization of data on physical storage.

Which utility provides statistical information such as the number of pages and rows and stores them in Catalog tables?

Runstats utility updated the catalog tables with the statistical information.

Which command is used to commit a transaction in CICS program?

SYNCPOINT command is used to commit a transaction in CICS program.

Which isolation level provides maximum concurrency?

Uncommitted read provides maximum concurrency.

Which isolation level ensures highest data integrity?

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

If a transaction has an exclusive lock on some data, then what are the other types of lock which can be applied on it?

A transaction gets an Exclusive lock when it is about to write at that time no other lock can be applied on it.

What is the input to the bind process?

DBRM is the input to the bind process which is produced in the pre-compile step.

How you will count the number of rows from a table TAB?

SELECT COUNT(*) FROM TAB query is used to count the number of rows in a table.

Suppose we added a new column by using ALTER statement, where the column will be added?

When we use ALTER statement for adding a new column then it will be added at the end of the table.

In a COBOL-DB2 program where can we declare a cursor?

We can declare a cursor either i Working Storage Section or in Procedure Division also.

When you will face SQLCODE -818?

SQLCODE -818 is a timestamp mismatch of load module and bind timestamp built from DBRM.

When you will face SQLCODE -922?

If a user is not authorized to access DB2 objects, he will face SQLCODE -922.

When you will face SQLCODE -911?

At the time of deadlock or timeout you will face SQLCODE -911.

When you will face SQLCODE -803?

When you will try to insert or update a record with duplicate key then you will face SQLCODE = -803.

What does application plan contains?

Application plan consists of one or more DBRM & application package which is kept in buffer pool during program execution.

Suppose a Plan contains 4 Packages & we modified one of the DBRM which is present in one of the Package. Then for normal functionality of application what should we do?

We will bind only that package which contains the modified DBRM. No need to bind all the packages.

What happens in BIND step in a DB2 program?

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

What does a positive SQL code indicates?

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

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

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

Which statement is used to authorize access on database tables?

GRANT keyword is used to grant privileges on DB2 tables.

In which statement you can define primary key?

Primary keys are optional and we can define them in CRETAE TABLE & ALTER TABLE statements.

How many primary keys can be declared on a table?

A table can have only one primary key.

Write a query to delete a table from database.

DROP TABLE table-name is the syntax to delete a table from database.

Write the query to delete all the rows from a table.

DELETE * FROM table-name is the syntax to delete all the rows from a table.

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

Char data type maximum size is of 254 bytes.

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

Varchar data type maximum size is of 4046 bytes.

What does SPUFI stands for?

SPUFI stands for SQL Processor Using File Input.

In which format index in stored?

An indexed is stored in B-tree format.

Which index must be present for partitioned table space?

A partitioned table space must have a clustered index.

Suppose a table A has an Alias named AL1. Which of the query is correct to drop the AL1 alias?

DROP ALIAS AL1 is the query to drop a alias.

How many clustering index we can have for a single table?

We can have only one clustering index for a table.