DB2 Frequently Asked Questions and Answers:Part 2

11/23/2009 No Comment

Mainframe DB2 Frequently Asked Interview Questions and Answers.

Explain what is an intent lock in DB2?
Answer : An intent lock is at the table level for a segmented table space or at the table space level for a non segmented table space They indicate at the table or table space level the kinds of locks at lower levels.

Differentiate between Static and Dynamic SQL?
Answer : Static SQL is hard-coded in a program when the programmer knows the statements to be executed For dynamic SQL the program must dynamically allocate memory to receive the query results.

What do you mean by cursor stability?

Answer : Cursor stability means that DB2 takes a lock on the page the cursor is accessing and releases the lock when the cursor moves to another page.

What is the importance of the CURSOR WITH HOLD clause in a cursor declaration?

Answer : The clause avoids closing the cursor and re-positioning it to the last row processed when the cursor is reopened.

Define SQL Communications Area and what are some of its key fields?

Answer : It is a data structure that must be included in any host-language program using SQL It is used to pass feedback about the SQL operations to the program Fields are return codes, error messages, Handling codes and warnings.

What is the significance of the WHENEVER statement?

Answer : The WHENEVER statement is coded once in the host program to control program actions depending on the SQL-CODE returned by each SQL statement within the program.

What do you mean by DCLGEN?

Answer : A DCLGEN stands for declarations generator; it is a facility to generate DB2 SQL data structures in COBOL or PL/1 programs.

Explain FREE command in DB2?

Answer : The FREE command can be used to delete plans and/or packages no longer Needed.

DB2 can implement a join in three ways using a merge join, a nested join or a hybrid join Explain the differences.

Answer : A merge join requires that the tables being joined be in a sequence; the rows are retrieved with a high cluster ratio index or are sorted by DB2 A nested join does not require a sequence and works best on joining a small number of rows DB2 reads the outer table values and each time scans the inner table for matches The hybrid join is a nested join that requires the outer table be in sequence.

Differentiate between a sub select to a join.

Answer : Any sub select can be rewritten as a join, but not vice versa Joins are usually more efficient as join rows can be returned immediately, sub selects require a temporary work area for inner selects results while processing the outer select.

Differentiate between IN sub selects and EXISTS sub select?

Answer : If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN (IN for index is the mnemonic).

What do you mean by a Cartesian product? 

Answer : A Cartesian product results from a faulty query It is a row in the results for every combination in the join tables.

Explain in brief what is the difference between a package and a plan?

Answer : Package and plan are usually used synonymously as in this site Both contain optimized code for SQL statements - a package for a single program, module or subroutine contained in the data base request module (DBRM) library A plan may contain multiple packages and pointers to packages The one CICS module would then exist in a package that could be referenced in two different plans.

What do you mean by a asynchronous write?
Answer : It is a write to disk that may occur before or long after a commit The write is controlled by the buffer manager.

Explain what is a lock in DB2?

Answer : A lock is the mechanism that controls access to data pages and table spaces.

Explain what is isolation level?

Answer : This is a key concept for any relational database Isolation level is the manner in which locks are applied and released during a transaction For DB2 a 'repeatable read' holds all locks until the Transaction completes or a sync point is issued. For transactions using 'cursor stability' the page lock releases are issued as the cursor 'moves', ie as the transaction releases address ability to the records.
Related Posts

No comments :


Aired | The content is copyrighted and may not be reproduced on other websites. | Copyright © 2009-2016 | All Rights Reserved 2016

Contact Us | About Us | Privacy Policy and Disclaimer