DB2 Interview Questions with Answers

The DB2 interview questions and answers are intended for those with a working knowledge of DB2 as a programmer. This will help you prepare for technical DB2 interview and online selection tests during campus placement for freshers and job interviews for professionals.

DB2 Interview Questions

1. What do the initials DDL and DML stand for and what is their meaning?
Answer: 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.

2. What is a sub-select? Is it different from a nested select?
Answer: A sub-select is a select which works in conjunction with another select. A nested select is a kind of sub-select where the inner select passes to the where criteria for the outer select.

3. What is the difference between group by and order by?
Answer: Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

4. Explain the EXPLAIN statement.
Answer: The explain statement provides information about the optimizer’s choice of access path of the SQL.

5. What Is an Access Path?
Answer: The path that is used to get to data specified in SQL statements.

6. What is referential integrity?
Answer: Referential integrity refers to the consistency that must be maintained between primary and foreign keys i.e. every foreign key value must have a corresponding primary key value.

7. What is a foreign key?
Answer: A foreign key is the key defined in one table to reference the primary key of a reference table. This foreign key must have the same structure as the reference table’s primary key.

8. What is the database descriptor?
Answer: The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

9. What is lock contention?
Answer: 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.

10. What is the significance of DB2 free space and what parameters control it?
Answer: 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.

11. What is a NULL value? What are the pros and cons of using NULLS?
Answer: 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. For DB2 queries use NULL indicator.
0 : If column is not null that is it have some value in it.
-1: If column contains null
-2: If column contains null as a part of data conversion.

12. What is a synonym? How is it used?
Answer: 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.

13. What is an alias and how does it differ from a synonym?
Answer: 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.

14. If the base table underlying a view is restructured, e.g. attributes are added, does the application code accessing the view need to be redone?
Answer: No. The table and its view are created a new, but the programs accessing the view do not need to be changed if the view and attributes accessed remain the same.

15. What is the self-referencing constraint?
Answer: 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.

16. What is a composite index and how does it differ from a multiple index?
Answer: 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.

17. What is meant by index cardinality?
Answer: 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.

18. Explain transactions, commits and rollbacks in DB2?
Answer: 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.

19. What are the three lock types?
Answer: 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.

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

21. What is the difference 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.

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

23. What is the difference between IN sub-select 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).

24. Define clustering index.
Answer: Clustering index is a type of index, which locates the table rows and determines how to group the rows together in the tablespace.

25. What is concurrency?
Answer: More than one DB2 application process can access the same data at the same time, is known as concurrency. However, problems can happen such as, lost updates access to unrepeatable reads and uncommitted data.

26. What happens in BIND step in a DB2 program?
Answer: Bind step converts all the SQL statements into executable form in COBOL-DB2 prog

27. Explain What A Plan Is?
Answer: Plan is a DB2 object (produced during the bind process) that associates one or more database request modules with a plan name.

28. What Is a Db2 Bind?
Answer: Bind is a process that builds “access paths” to DB2 tables. A bind uses the Database Request Modules(s) (DBRM(s)) from the DB2 pre-compile step as input and produces an application plan. It also checks the user’s authority and validates the SQL statements in the DBRM(s).

29. What Information Is Used as input to The Bind Process?
Answer: The database request module produced during the pre-compile. The SYSIBM.SYSSTMT table of the DB2 catalog.

30. What Is the Function of Buffer Manager?
Answer: The buffer manager is the DB2 component responsible for physically transferring data between an external medium and (virtual) storage (performs the actual I/O operations). It minimizes the amount of physical I/O actually performed with sophisticated buffering techniques (i.e., read-ahead buffering and look-aside buffering).

31. What Is A Buffer Pool?
Answer: A buffer pool is main storage that is reserved to satisfy the buffering requirements for one or more tablespaces or indexes and is made up of either 4K or 32K pages. The buffer pool area helps to improve database system performance by allowing data to be accessed from memory instead of from disk.

32. How many Buffer Pools are there in Db2?
Answer: There are four buffer pools: BP0, BP1, BP2, and BP32.

33. What is an Index?
Answer: An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.

34. What are all the different types of indexes?
Answer: There are three types of indexes
• Unique Index: Unique Indexes helps maintain data integrity by ensuring that no two rows of data in a table have identical key values. A unique index can be applied automatically when a primary key is defined. It ensures that the values in the index key columns are unique.
• Clustered Index: Clustered Index reorders the physical order of the table and search based on the key values. There will be only one clustered index per table.
• Non-Clustered Index: Non-Clustered Index doesn’t alter the physical order of the table and maintains a logical order of the data. Each table can have many non-clustered indexes.

35. What is the difference between Cluster and Non-Cluster Index?
Answer: The difference between the clustered and non-clustered index in SQL is as follows:
Clustered Index:
It is used for easy retrieval of data from the database and it is faster.
One table can only have one clustered index
It alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index.
Non-Clustered Index:
It is slower compared to the Clustered index.
One table can have multiple non clustered index
It doesn’t alter the way it was sorted but it creates a separate object within a table which points back to the original table rows after searching.

36. What is a View?
Answer: A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.

37. What are the advantages of Views?
Answer: Some of the advantages of Views are
• Views occupy no space.
• Views are used to simply retrieve the results of complicated queries that need to be executed often.
• Views are used to restrict access to the database or to hide data complexity.

38. What are the types of subquery?
Answer: There are two types of subquery:
• Correlated: In a SQL database query, a correlated subquery is a subquery that uses values from the outer query in order to complete. Because a correlated subquery requires the outer query to be executed first, the correlated subquery must run once for every row in the outer query. It is also known as a synchronized subquery.
• Non-Correlated: A Non-correlated subquery is a subquery in which both outer query and inner query are independent to each other.

39. What is the difference between Local Variables and Global Variables?
Answer: Local Variables: Local variables can be used or exist only inside the function. These variables are not used or referred by any other functions. These are not known to other functions. Variables can be created whenever that function is called.
Global Variables: Global variables can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

40. What is the difference between an inner and outer join?
Answer: An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared.
An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.

41. What is Normalization?
Answer: Normalization is the process of table design to minimize the data redundancy.

42. What are all the different Normalization?
Answer: There are different types of Normalization forms in SQL.
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce and Codd Normal Form (BCNF)

43. What is Denormalization?
Answer: Denormalization is a database optimization technique used to increase the performance of a database infrastructure. It involves in the process of adding redundant data to one or more tables. In a normalized database, we store data in separate logical tables and attempt to minimize redundant data.

44. What is Stored procedure?
Answer: A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. The stored procedure accepts input parameters and processes them and returns a single value such as a number or text value or a result set (set of rows).

45. What is the difference between Union and Union All command?
Answer: Both Union and Union All concatenate the result of two tables but the way these two queries handle duplicates are different.
Union: It omits duplicate records and returns only distinct result set of two or more select statements.
Union All: It returns all the rows including duplicates in the result set of different select statements.
Performance wise Union All is faster than Union, Since Union All doesn’t remove duplicates. Union query checks the duplicate values which consumes some time to remove the duplicate records.

46. What are aggregate functions in SQL?
Answer: SQL aggregate functions return a single value, calculated from values in a column. Some of the aggregate functions in SQL are as follows
• AVG() – This function returns the average value
• COUNT() – This function returns the number of rows
• MAX() – This function returns the largest value
• MIN() – This function returns the smallest value
• ROUND() – This function rounds a numeric field to the number of decimals specified
• SUM() – This function returns the sum

47. What is the difference between Having and Where clause?
Answer: Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with ‘GROUP BY’ to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

48. What is the diff bet Plan, Package, DBRM?
Answer: Plan is generated when you compile the DB2-SQL program.
This plan is stored in the DBRM and binded to database as packages. (stored in syscat.syspackages – pkgname)
DBRM: Data base request module is generated by pre-compiler which contains the SQL statements which are separated from the source program.
PACKAGE: By binding the DBRM, package is generated. Package contains the internal structure of the original SQL statements.
PLAN: It is the combination of packages that are bind to form a PLAN.

49. What is the error code -803, -811 & -922?
Answer: -803: unique index violation.
-811: SELECT statement has resulted in retrieval of more than one row.
-922: Authorization failure

50. What is REORG? When is it used?
Answer: REORG reorganizes data on physical storage to re-clutser rows, positioning overflowed rows in their proper sequence, to reclaim space, to restore free space. It is used after heavy updates, inserts and delete activity and after segments of a segmented tablespace have become fragmented.

COBOL Interview Questions and Answer : Click Here
IBM Reference: Click Here