What is 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 significance of the CURSOR WITH HOLD clause in a cursor declaration? Answer: The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.
What is the 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 purpose 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 is DCLGEN Answer: DCLGEN stands for declarations generator; it is a facility to generate DB2 sql data structures in COBOL or PL/I programs.
What is the FREE command? 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.
Compare a subselect to a join.
Answer: Any subselect can be rewritten as a join, but not vice versa. Joins are usually more efficient as join rows can be
returned immediately, subselects require a temporary work area for inner selects results while processing the outer select.
What is the difference between IN subselects and EXISTS subselect?
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 is 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.