DB2 Interview Questions & Answers

Basic DB2 Concepts:

1. What is DB2?

   • Answer: DB2 is a family of data management products, including database servers, developed by IBM.

2. Explain the difference between DB2 and other relational databases.

   • Answer: DB2 is known for its integration with IBM mainframes and its ability to handle complex queries and transactions efficiently.

3. What is the purpose of the DB2 catalog and directory?

   • Answer: The catalog contains metadata about the database, and the directory contains information about the location of the data.

4. Explain the ACID properties in the context of DB2.

   • Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability, ensuring data integrity in database transactions.

5. What are the different types of tables in DB2?

   • Answer: DB2 supports various table types, including base tables, created global temporary tables, and declared temporary tables.

SQL and Query Optimization:

6. Write a SQL query to retrieve data from a table in DB2.

   • Answer: SELECT * FROM tablename;

7. What is the purpose of the EXPLAIN statement in DB2?

   • Answer: The EXPLAIN statement is used to analyze the access path and optimization of SQL queries.

8. Explain the importance of an index in DB2.

   • Answer: Indexes in DB2 improve the speed of data retrieval operations by providing a quick lookup mechanism.

9. How do you optimize a query in DB2?

   • Answer: Query optimization in DB2 involves creating indexes, updating statistics, and using proper join and filter conditions.

10. What is the role of the FETCH FIRST clause in a SELECT statement?

    • Answer: The FETCH FIRST clause is used to limit the number of rows returned by a query.

DB2 Data Types:

11. Explain the TIMESTAMP data type in DB2.

    • Answer: TIMESTAMP stores date and time values, including fractional seconds.

12. What is a LOB (Large Object) data type in DB2?

    • Answer: LOB data types are used to store large amounts of binary or character data, such as images or documents.

13. Differentiate between CHAR and VARCHAR data types in DB2.

    • Answer: CHAR is a fixed-length string, while VARCHAR is a variable-length string.

14. What is the purpose of the DECIMAL data type in DB2?

    • Answer: DECIMAL is used to store fixed-point numbers with a specified precision and scale.

15. Explain the concept of null values in DB2.

    • Answer: Null values in DB2 represent missing or unknown data and are distinct from zero or an empty string.

DB2 Constraints:

16. What is a primary key constraint in DB2?

    • Answer: A primary key constraint ensures that a column or set of columns uniquely identifies each row in a table.

17. Explain the FOREIGN KEY constraint in DB2.

    • Answer: The FOREIGN KEY constraint establishes a link between two tables, enforcing referential integrity.

18. How do you add a UNIQUE constraint to a column in DB2?

    • Answer: Use the UNIQUE keyword when defining the column in the CREATE TABLE or ALTER TABLE statement.

19. What is the purpose of the CHECK constraint in DB2?

    • Answer: The CHECK constraint is used to enforce a condition on the values in a column.

20. How can you disable a constraint in DB2 temporarily?

    • Answer: Use the ALTER TABLE tablename ALTER [CONSTRAINT constraintname] SET CHECKED statement.

Transactions and Locking:

21. Explain the concept of a transaction in DB2.

    • Answer: A transaction in DB2 is a sequence of one or more SQL statements that are executed as a single unit of work.

22. What is a deadlock, and how can it be resolved in DB2?

    • Answer: A deadlock occurs when two or more transactions are waiting for each other to release locks. DB2 resolves deadlocks by rolling back one of the transactions.

23. Explain the purpose of the COMMIT statement in DB2.

    • Answer: The COMMIT statement ends a transaction, making all changes made during the transaction permanent.

24. How can you set the isolation level for a transaction in DB2?

    • Answer: The isolation level can be set using the SET ISOLATION LEVEL statement.

25. What is the purpose of the ROLLBACK statement in DB2?

    • Answer: The ROLLBACK statement undoes changes made during a transaction, restoring the database to its state before the transaction started.

DB2 System Objects:

26. What is the purpose of the SYSCAT schema in DB2?

    • Answer: The SYSCAT schema contains system catalog tables that store metadata about the database.

27. Explain the role of the SYSIBM schema in DB2.

    • Answer: The SYSIBM schema contains system catalog views and routines that provide information about the database objects.

28. How can you retrieve information about database objects using system catalog tables?

    • Answer: Queries can be run on tables in the SYSCAT schema to obtain information about tables, indexes, and other objects.

29. What is the purpose of the PLAN_TABLE in DB2?

    • Answer: The PLAN_TABLE is a system table used to store the access plan generated by the EXPLAIN statement.

30. Explain the concept of an alias in DB2.

    • Answer: An alias is an alternate name for a table or column, providing a way to reference objects with a different name.

Backup and Recovery:

31. How can you perform a backup in DB2?

    • Answer: Backups can be performed using the BACKUP DATABASE statement or the db2backup command.

32. Explain the role of the RESTORE command in DB2.

    • Answer: The RESTORE command is used to restore a database from a backup image.

33. What is a tablespace in DB2, and how is it related to backup and recovery?

    • Answer: A tablespace is a logical storage container for tables and indexes. Backups and restores are often done at the tablespace level.

34. How can you recover a database to a specific point in time?

    • Answer: Point-in-time recovery can be performed using the ROLLFORWARD command.

35. Explain the concept of a recovery log in DB2.

    • Answer: The recovery log contains a record of changes made to the database, allowing for recovery in the event of a failure.

Performance Tuning:

36. What is RUNSTATS, and why is it important in DB2?

    • Answer: RUNSTATS is a utility used to collect statistics about tables and indexes, helping the query optimizer make informed decisions.

37. **How can you monitor and

analyze SQL performance in DB2?** - Answer: The EXPLAIN statement, db2exfmt command, and monitoring tools can be used to analyze and monitor SQL performance.

38. Explain the concept of table partitioning in DB2.

    • Answer: Table partitioning involves dividing a table into smaller, more manageable pieces based on a specified key.

39. What is the purpose of the DB2 Configuration Advisor?

    • Answer: The Configuration Advisor provides recommendations for optimizing the database configuration based on workload characteristics.

40. How can you improve query performance in DB2?

    • Answer: Strategies include creating indexes, optimizing SQL queries, and adjusting database configuration parameters.

DB2 Security:

41. Explain the difference between authentication and authorization in DB2.

    • Answer: Authentication verifies the identity of a user, while authorization controls the user's access to database objects.

42. How can you grant and revoke privileges in DB2?

    • Answer: Use the GRANT and REVOKE statements to assign or revoke privileges on database objects.

43. What is a role in DB2, and how is it used for security?

    • Answer: A role is a named group of privileges. Users are assigned to roles, simplifying the management of privileges.

44. How can you encrypt data in transit in DB2?

    • Answer: Data in transit can be encrypted using Secure Sockets Layer (SSL) or Transport Layer Security (TLS) protocols.

45. Explain the purpose of the AUDIT facility in DB2.

    • Answer: The AUDIT facility is used to capture and log security-related events for later review.

Advanced Topics:

46. What is a stored procedure in DB2?

    • Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit.

47. Explain the concept of triggers in DB2.

    • Answer: Triggers are sets of instructions that are automatically executed (or "triggered") in response to specified events on a particular table or view.

48. What is Dynamic SQL in DB2?

    • Answer: Dynamic SQL involves constructing and executing SQL statements at runtime rather than at compile time.

49. Explain the concept of federation in DB2.

    • Answer: Federation allows DB2 to integrate data from multiple database sources, providing a unified view of the data.

50. How can you monitor and manage DB2 using the IBM Data Studio?

    • Answer: IBM Data Studio is an integrated development environment that provides tools for database development, administration, and performance monitoring.