DB2 Interview Questions

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

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

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

System services component handles DB2 startup and shutdown.

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

In Bind step we specify the isolation level.

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

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

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

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

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

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

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

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.

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

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 either a Shared or Update lock on it.

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

Load utility is used to load data into tables.

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

Runstats utility updated the catalog tables with the statistical information.

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

Uncommitted read provides maximum concurrency.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

GRANT keyword is used to grant privileges on DB2 tables.

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

A table can have only one primary key.

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

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

Char data type maximum size is of 254 bytes.

Varchar data type maximum size is of 4046 bytes.

SPUFI stands for SQL Processor Using File Input.

An indexed is stored in B-tree format.

A partitioned table space must have a clustered index.

DROP ALIAS AL1 is the query to drop a alias.

We can have only one clustering index for a table.