Thursday, February 16, 2006

DB2 Interview Questions

What is integrity ?

Accuracy, correctness or validity of data in the database.

a) Domain Integrity : making sure that every field value is a member of a domain.

b) Entity Integrity : Making sure each row is unique. Achieved using primary key.

c) Referential Integrity : automatic enforcement of referential constrains using primary and foreign key.

Primary key – unique identifier of the table.

Foreign key – primary key of the child table which is related to primary key of base table.

How do you retrieve multiple rows from a table and list the SQL statements required?

DB2 sequentially retrieves multiple rows through the use of a CURSOR.

DECLARE defines the cursor using a SELECT statement.

OPEN opens the cursor. This would be the slowest process since the data is built.

FETCH retrieves each row one at a time until there are no more rows (SQLCODE = +100).

CLOSE closes the cursor.

What is Bind ? What are the different bind parameters ?

Bind builds access strategies to retrieve the data.

Bind takes DBRM as input and produces mechanism to access data as directed by SQL statements being bound. There are two kinds of BINDS, BIND PLAN & BIND PACKAGE. The output of BINDPLAN is application plan.

BINDPLAN(Application Plan) --- Executable logic representing optimized access path to DB2 data.

- Checks the syntax of DBRM’s SQL statements.

- Checks the Tables & columns being accessed confirm to the catalog information.

- Perform authorization validations.

- Optimizes SQL statements into efficient access paths.

What is Referential Integrity or RI ? How is it enforced ?

Means that foreign keys from one table references the primary key in another.

The DBA (database administrator) uses referential constraints when creating tables using the FOREIGN KEY clause.

A refential constraint consists of 3 components:

1-a constraint name

2-columns comprising the FOREIGN KEY


what is Precompiler ?

- Extracts the SQL statements and place them into DBRM.

- Places a time stamp token in the modified source data & the DBRM.