The following is the entire collection of DB2 interview questions:

Q1. What is a DB2 bind?

A1. A DB2 bind is a process that builds an access path to DB2 tables.

Q2. What is a DB2 access path?

A2. An access path is the method used to access data specified in DB2 sql statements.

Q3. What is a DB2 plan?

A3. An application plan or package is generated by the bind to define an access path.

Q4. What is normalization and what are the five normal forms?

A4. Normalization is a design procedure for representing data in tabular format.  The five normal forms are progressive rules to represent the data with minimal redundancy.

Q5. What are foreign keys?

A5. These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.

Q6. Describe the elements of the SELECT query syntax.

A6. SELECT element FROM table WHERE conditional statement.

Q7. Explain the use of the WHERE clause.

A7. WHERE is used with a relational statement to isolate the object element or row.

Q8. What techniques are used to retrieve data from more than one table in a single SQL statement?

A8. Joins, unions and nested selects are used to retrieve data.

Q9. What do the initials DDL and DML stand for and what is their meaning?

A9. DDL is data definition language and DML is data manipulation language.  DDL statements are CREATE, ALTER, TRUNCATE.  DML statements are SELECT, INSERT, DELETE and UPDATE.

Q10. What is a view? Why use it?

A10. A view is a virtual table made up of data from base tables and other views, but not stored separately.

Q11. Explain an outer join.

A11. An outer join includes rows from tables when there are no matching values in the tables.

Q12. What is a subselect?  Is it different from a nested select?

A12. A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

Q13. What is the difference between group by and order by?

A13. Group by controls the presentation of the rows, order by controls the presentation of the columns  for the results of the SELECT statement.

Q14. Explain the EXPLAIN statement.

A14. The explain statement provides information about the optimizer’s choice of access path of the sql.

Q15. What is tablespace?

A15. Tables are stored in tablespaces (hence the name)!  There are three types of tablespaces: simple, segmented and partitioned.

Q16. What is a cursor and what is its function?

A16. An embedded sql statement may return a number of rows while the programming language can only access one row at a time.  The programming device called a cursor controls the position of the row.

Q17. What is referential integrity?

A17. Referential integrity refers to the consistency that must be maintained between primary and foreign keys, ie every foreign key value must have a corresponding primary key value.

Q18. Usually, which is more important for DB2 system performance – CPU processing or I/O access?

A18. I/O operations are usually most critical for DB2 performance (or any other database for that matter).

Q19. Is there any advantage to denormalizing DB2 tables?

A19. Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

Q20. What is the database descriptor?

A20. The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

Q21. What is lock contention?

A21. To maintain the integrity of DB2 objects the DBD permits access to only on object at a time.  Lock contention happens if several objects are required by contending application processes simultaneously.

Q22. What is SPUFI?

A22. SPUFI stands for SQL processing using file input.  It is the DB2 interactive menu-driven tool used by developers to create database objects.

Q23. What is the significance of DB2 free space and what parameters control it?

A23. The two parameters  used in the CREATE statement are the PCTFREE which specifies the percentage of free space for each page and FREEPAGE which indicates the number of pages to be loaded with data between each free page.  Free space allows room for the insertion of new rows.

Q24. What is a NULL value?  What are the pros and cons of using NULLS?

A24. A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value.  It’s the DB2 equivalent of TBD on an organizational chart and often correctly portrays a business situation.  Unfortunately, it requires extra coding for an application program to handle this situation.

Q25. What is a synonym?  How is it used?

A25. A synonym is used to reference a table or view by another name.  The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated.  The synonym is linked to the AUTHID that created it.

Q26. What is an alias and how does it differ from a synonym?

A26. An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view.  The alias is not dropped when the table is dropped.

Q27. What is a LIKE table and how is it created?

A27. A LIKE table is created by using the LIKE parameter in a CREATE table statement.  LIKE tables are typically created for a test environment from the production environment.

Q28. If the base table underlying a view is restructured, eg. attributes are added, does the application code accessing the view need to be redone?

A28. No.  The table and its view are created anew, but the programs accessing the view do not need to be changed if the view and attributes accessed remain the same.

Q29. Under what circumstances will DB2 allow an SQL statement to update more than one primary key value at a time?

A29. Never.  Such processing could produce duplicate values violating entity integrity. Primary keys must be updated one at a time.

Q30. What is the cascade rule and how does it relate to deletions made with a subselect.

A30. The cascade rule will not allow deletions based on a subselect that references the same table from which the deletions are being made.

Q31. What is the self-referencing constraint?

A31. The self-referencing constraint limits in a single table the changes to a primary key that the related foreign key defines.  The foreign key in a self referencing table must specify the DELETE CASCADE rule.

Q32. What are delete-connected tables?

A32. Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect the contents of the foreign key table.

Q33. When can an insert of a new primary key value threaten referential integrity?

A33. Never. New primary key values are not a problem.  However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity.

Q34. In terms of DB2 indexing, what is the root page?

A34. The simplest DB2 index is the B-tree and the B-tree’s top page is called the root page. The root page entries represent the upper range limits of the index and are referenced first in a search.

Q35. How does Db2 use multiple table indexes?

A35. DB2 use the multiple indexes to satisfy multiple predicates in a SELECT statement that are joined by an AND or OR.

Popularity: unranked [?]

Related interview questions

• Interview Questions DB2 : Part – II
• DB2 : SQL Codes and Description
• DB2 understanding : Part – II
• What is a foreign key?
• What is a Declarations Generator (DCLGEN)?