The following is the entire collection of DB2 interview questions:

Q36. What are some characteristics of columns that benefit from indexes?

A36. Primary key and foreign key columns;  columns that have unique values; columns that have aggregates computed frequently and columns used to test the existence of a value.

Q37. What is a composite index and how does it differ from a multiple index?

A37. A multiple index is not one index but two indexes for two different columns of a table.  A composite index is one index made up of combined values from two columns in a table.  If two columns in a table will often be accessed together a composite index will be efficient.

Q38. What is meant by index cardinality?

A38. The number of distinct values for a column is called index cardinality.  DB2′s RUNSTATS utility analyzes column value redundancy to determine whether to use a tablespace or index scan to search for data.

Q39. What is a clustered index?

A39. For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently.

Q40. What keyword does an SQL SELECT statement use for a string search?

A40. The LIKE keyword allows for string searches.  The % sign is used as a wildcard.

Q41. What are some sql aggregates and other built-in functions?

A41. The common aggregate, built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT.

Q42. How is the SUBSTR keyword used in sql?

A42. SUBSTR is used for string manipulation with column name, first position and string length used as arguments.  Eg. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.

Q43. What are the three DB2 date and time data types and their associated functions?

A43. The three data types are DATE, TIME and TIMESTAMP.  CHAR can be used to specify the format of each type. The DAYS function calculates the number of days between two dates.  (It’s Y2K compliant).

Q44. Explain transactions, commits and rollbacks in DB2.

A44. In DB2 a transaction typically requires a series of updates, insertions and deletions that represent a logical unit of work. A transaction puts an implicit lock on the DB2 data. Programmers can use the COMMIT WORK statement to terminate the transaction creating smaller units for recovery.  If the transaction fails DB2 uses the log to roll back values to the start of the transaction or to the preceding commit point.

Q45. What is deadlock?

A45. Deadlock occurs when transactions executing at the same time lock each other out of data that they need to complete their logical units of work.

Q46. What are the four lockable units for DB2?

A46. DB2 imposes locks of four differing sizes:  pages, tables, tablespace and for indexes subpage.

Q47. What are the three lock types?

A47. 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 lock bars all other users from accessing the space.  An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.

Q48. What is isolation level?

A48. SQL statements may return any number of rows, but most host languages deal with one row at a time by declaring a cursor that presents each row at a unique isolation level.

Q49. What is an intent lock?

A49. An intent lock is at the table level for a segmented tablespace or at the tablespace level for a nonsegmented tablespace.  They indicate at the table or tablespace level the kinds of locks at lower levels.

Q50. What is the difference between static and dynamic sql?

A50. 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.

Q51. What is cursor stability?

A51. 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.

Q52. What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?

A52. The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.

Q53. What is the SQL Communications Area and what are some of its key fields?

A53. 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.

Q54. What is the purpose of the WHENEVER statement?

A54. 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.

Q55. What is DCLGEN?

A55. DCLGEN stands for declarations generator;  it is a facility to generate DB2 sql data structures in COBOL or PL/I programs.

Q56. What is the FREE command?

A56. The FREE command can be used to delete plans and/or packages no longer needed.

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

A57. 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.

Q58. Compare a subselect to a join.

A58. 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.

Q59. What is the difference between IN subselects and EXISTS subselect?

A59. 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).

Q60. What is a Cartesian product?

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

Q61. 4/99 Mail from Joseph Howard:  ’Q: 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?

A61. Package and plan are usually used synonomously, as in this site. Both contain optimized code for SQL statements – a package for a single program, module or subroutine contained in the datebase 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.

Q62. What is an asychronous write?

A62. It is a write to disk that may occur before or long after a commit.  The write is controlled by the buffer manager.

Q63. What is a lock?

A63. A lock is the mechanism that controls access to data pages and tablespaces.

Q64. What is meant by isolation level?

A64. 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’ holds all locks untile the transaction completes or a syncpoint is issued.  For transactions using ‘cursor stability’ the page lock releases are issued as the cursor ‘moves’, i.e. as the transaction releases addressability to the records.

Q65. What are leaf pages?

A65. 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 rows.

Q66. What is a precompiler?

A66. It is a DB2 facility for static SQL statements – it replaces these statements with calls to the DB2 language interface module.

Q67. What is a root page?

A67. The opposite of a leaf page; it is the highest level index page.  An index can contain only the one root page; all other index pages are associated to the root.

Q68. What is a thread?

A68. A thread is the connection between DB2 and some other subsystem, such as CICS or IMS/DC.

Popularity: unranked [?]

Related interview questions

• Interview Questions DB2 : Part – I
• DB2 understanding : Part – II
• DB2 : SQL Codes and Description
• What is a clustering index?
• What is a “nonleaf” page?