DB2 Interview Questions and answers

DB2 Interview Questions and answers
What is a DBRM, PLAN ?
DBRM: DataBase Request Module, has the SQL statements extracted from the host language program by the pre-compiler.
PLAN: A result of the BIND process. It has the executable code for the SQL statements in the DBRM.
What is ACQUIRE/RELEASE in BIND?
Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks.
What else is there in the PLAN apart from the access path?
PLAN has the executable code for the SQL statements in the host program
What happens to the PLAN if index used by it is dropped?
Plan is marked as invalid. The next time the plan is accessed, it is rebound.
What are PACKAGES ?
They contain executable code for SQL statements for one DBRM.
What are the advantages of using a PACKAGE?
1. Avoid having to bind a large number of DBRM members into a plan
2. Avoid cost of a large bind
3. Avoid the entire transaction being unavailable during bind and automatic rebind of a plan
4. Minimize fallback complexities if changes result in an error.
What is a collection?
a user defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.
In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.
What are the 2 sqlcodes that are returned?
100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).