DB2 Interview Questions

the difference is that a Package is at a more granular level. If a module is bound to a Package, any change in module requires only the Package to be re-bound, without the necessity to re-bind the ent...

Package can be bound for single DBRM only. Plan can contain both DBRM and Packages. Packaqes can't be executed until they are bound into a plan. 

a

To improve the performance following are some of the tips :

1. Always use FOR UPDATE OF when updating with a cursor.
2. Use WHERE CURRENT OF to delete a single row.
3. Check the SQLCODE after each SQL statemnt.
4. Avoid using WHENEVER.

SQLCA or SQL Communication area (136 bytes) is used by DB2 to communicate with the Program

c. Thread

Embedded SQL -> Access Path is generated during BIND time. also table authorizations are checked during BIND (if VALIDATE(BIND) is used).

Dynamic SQL => Access path is generated during Run-time. Table authorizations can only be checked during run-time.

STATIC sql(embedded) : It is hardcoded in the program itself and the changeable                   &n...

Cursor Declaration can be done either in WORKING STORAGE SECTION in DATA DIVISION or in PROCEDURE DIVISION, the only rule is that it has to be coded before the OPEN CURSOR statement in the Procedure Division.

Correct ans is WORKING STORAGE section

-805 will never occur if a DBRM is directly bound to a PLAN. -805 occurs when 1) Source Program is changed and per-compiled and new LOAD Module created as a result of change is used at Run time, but ...

Rebind the plan.

DB2 Access path is (in most cases) the path which results in the most optimized execution of an SQL query. Whenever any Static SQL (Embedded SQL) is bound to a package/plan, the DB2 optimizer creates...

The access path is determined by DB2 during the bind process. This determines what indexes will be used.

Check Pending is when you have data added to the table using either insert or LOAD utility which violate table check constraints. The IBM DB2 LOAD UTILITY has ENFORCE CONSTRAINTS and similar options w...

SET INTEGRITY Option....please read IBM libraries before using this.

I will say the Answer Depends barring a few parameters which are mostly used. 1) ISOLATION -> Isolation Level to decide level of locking used along with LOCK parameters on the DB2 objects. 2) ACQUI...

The bind parameters are generally generic the most important is isolation level which is always CS and sometimes UR if you want to rock right thru.

Lock contention are used for maintaining concurrency in the DB2® environment.There are several types of contention situations that degrade DB2 performance, including suspension, timeout, and deadlock.
Begin program-specific programming interface information.

Self-referencing constraint makes the table both the parent and dependent table in the same referential constraint.
Here the table is created first and then the foreign key is defined to that table.

By using the concept of list prefetch DB2 uses multiple index processing.I is used to satisfy multiple predicates in a select statement that are joined by an AND or OR.

The indexes of DB2 are represented using b-tree,the b-trees top page is called as the root page.The root page entries represent the upper range limits of the index and are referenced first in a search.

Asynchronous write is when the pages from bufferpool is written to disk by the page cleaners.

Asynchronous write is a function where the the function call immediately returns after the operation was enqueued or if before this happens an error was encountered.This function is present in "aio.h"...

This will yield to a correlated subquery return in SQL which will be each combination of record from the first table to second table. Which will be 6*6 = 36.

Ans: a. 6

You can table partitioning feature

There is nothing called "fetch last n records only" in db2...

You can use select col1 from table1 order by col1 desc fetch first 5 records only

First sort the records by using order by clause and then issue the following query

Code
SELECT * FROM tablename fetch first 5 rows only;

4141... in decimal

yes

Answer is 1 Both are True.

ii) is Only True
Unique indexes can be created on any column.