What are leaf pages ?
They are the opposite of root pages. Leaf pages are the lowest level index pages - the pages that contain index entries and information to the corresponding table... Read more »
‘Database Interview Questions’ - KyaPoocha.com
What is meant by isolation level ?
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 DB@ a ‘repeatable... Read more »
What is an asychronous write ?
It is a write to disk that may occur before or long after a commit. The write is controlled by the buffer manager. Read more »
DB2 What is the difference between a package and a plan? How does one bind 2 versions of a CICS transaction with the same module name in two different CICS regions that share the same DB2 subsystem?
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... Read more »
What is a Cartesian product ?
A Cartesian product results from a faulty query. It is a row in the results for every combination in the join tables. Read more »
What is the difference between IN subselects and EXISTS subselect ?
If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. Read more »
Compare a subselect to a join ?
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... Read more »
DB2 can implement a join in three ways using a merge join, a nested join or a hybrid join. Explain the differences?
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... Read more »
What is the FREE command ?
The FREE command can be used to delete plans and/or packages no longer needed. Read more »
What is the purpose of the WHENEVER statement ?
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. Read more »
What is the significance of the CURSOR WITH HOLD clause in a cursor declaration ?
The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened. Read more »
What is the difference between static and dynamic SQL ?
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... Read more »
What is an intent lock ?
An intent lock is at the table level for a segmented tablespace or at the tablespace level for a non-segmented tablespace. They indicate at the table or tablespace... Read more »
What are the three lock types ?
The three types are shared, update and exclusive. Shared locks allow two or more programs to read simultaneously but not change the locked space. An exclusive... Read more »
What are the four lockable units for DB2 ?
DB2 imposes locks of four differing sizes: pages, tables, tablespace and for indexes subpage. Read more »