DB2 Interview Questions and Answers for 5 years experience

1. What are the different types of joins in DB2? Explain with examples.

   • Answer: DB2 supports various JOIN types: INNER JOIN (returns rows only when there is a match in both tables), LEFT (OUTER) JOIN (returns all rows from the left table and matching rows from the right table; NULLs for non-matching rows), RIGHT (OUTER) JOIN (returns all rows from the right table and matching rows from the left table; NULLs for non-matching rows), and FULL (OUTER) JOIN (returns all rows from both tables; NULLs where there's no match in the other table). Example: An INNER JOIN between `Employees` and `Departments` tables would only return employees who belong to existing departments. A LEFT JOIN would return all employees, even those without a department assigned (department information would be NULL in those cases).

2. Explain the difference between COMMIT and ROLLBACK in DB2.

   • Answer: `COMMIT` permanently saves all changes made in a transaction to the database. `ROLLBACK` undoes all changes made within a transaction, returning the database to its state before the transaction began. They are crucial for database integrity and managing changes.

3. What are indexes in DB2 and why are they important?

   • Answer: Indexes are data structures that improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space. They work similarly to an index in a book, allowing DB2 to quickly locate specific rows without scanning the entire table. They're crucial for optimizing query performance, especially on large tables.

4. How do you handle deadlocks in DB2?

   • Answer: Deadlocks occur when two or more transactions are blocked indefinitely, waiting for each other to release locks. Techniques to handle deadlocks include: designing transactions to minimize lock duration, using appropriate isolation levels, monitoring for deadlocks using DB2 tools, and having a strategy to automatically detect and resolve them (typically by rolling back one of the transactions).

5. Explain different isolation levels in DB2.

   • Answer: Isolation levels control the degree to which concurrent transactions are isolated from each other. Common levels include: CS (Cursor Stability): allows dirty reads but prevents non-repeatable reads and phantom reads. RR (Repeatable Read): prevents dirty reads and non-repeatable reads, but may allow phantom reads. UR (Uncommitted Read): allows dirty reads, non-repeatable reads, and phantom reads. The choice of isolation level depends on the application's requirements and the trade-off between concurrency and data consistency.

6. What are stored procedures in DB2 and their benefits?

   • Answer: Stored procedures are pre-compiled SQL code blocks stored in the database. They offer several benefits including improved performance (due to pre-compilation), enhanced security (by centralizing access control), code reusability, and simplified application development.

7. Explain the concept of ACID properties in DB2 transactions.

   • Answer: ACID properties ensure database integrity: Atomicity (all operations in a transaction succeed or fail as a unit), Consistency (transactions maintain database integrity), Isolation (concurrent transactions appear to execute serially), Durability (committed transactions survive system failures).

8. How do you optimize query performance in DB2?

   • Answer: Query optimization involves various techniques like using appropriate indexes, writing efficient SQL code (avoiding `SELECT *`), using hints, analyzing execution plans, optimizing table designs, and utilizing DB2's query optimization features.

9. What are triggers in DB2 and how are they used?

   • Answer: Triggers are stored procedures automatically executed in response to specific events on a particular table (e.g., INSERT, UPDATE, DELETE). They are used for enforcing business rules, auditing data changes, and maintaining data integrity.

10. Describe your experience with DB2 performance tuning. Give a specific example.

    • Answer: [This answer should be tailored to your experience. Example: "In a previous role, a slow-running report was significantly improved by identifying a missing index on a frequently queried column. We used DB2's explain plan feature to identify the bottleneck and then added the index. This reduced query execution time from over 10 minutes to under 30 seconds."]

11. What is a materialized query table (MQT) in DB2?

    • Answer: A materialized query table (MQT) is a table that stores the result-set of a pre-defined query. This is useful for frequently run queries which can be updated periodically. It enhances performance for frequently accessed data by eliminating the need to run the query each time.

12. Explain the concept of data partitioning in DB2.

    • Answer: Data partitioning horizontally divides a large table into smaller, more manageable partitions. This improves performance for parallel processing and data management, simplifies backup and recovery, and enhances scalability.

13. What is a utility in DB2 and what are some examples?

    • Answer: DB2 utilities are programs that perform administrative and maintenance tasks such as backup and recovery, reorganization, statistics collection, and data loading. Examples include `RUNSTATS`, `REORG`, `LOAD`, and `BACKUP`.

14. How do you handle large data imports into DB2?

• Answer: For large data imports, using the `LOAD` utility or parallel load techniques is significantly faster than inserting rows individually. Staging data in smaller batches can further improve efficiency and resilience to errors.

15. What is the difference between a clustered and a non-clustered index in DB2?

    • Answer: In DB2, the term "clustered index" doesn't directly apply in the same way it does in other databases like SQL Server. Instead, the concept relates to the way data is physically stored. A logically clustered index is created on a table's primary key (or a unique index). This index then causes the data to be physically stored in a way that's optimized for accessing data in the order of that index. Non-clustered indexes don't affect the physical storage order of the data.

16. What are some common DB2 error codes and how do you troubleshoot them?

    • Answer: Common error codes vary, but generally involve SQLCODE values. Troubleshooting involves examining the full error message (including SQLSTATE), checking DB2 logs for more information, verifying database connections, examining the SQL statement for syntax errors, and using DB2 diagnostic tools. The DB2 documentation is a crucial resource.

17. Explain your experience working with DB2 LUW (Linux, UNIX, Windows).

    • Answer: [Describe your experience, mentioning specific tasks, challenges overcome, and the version of LUW used.]

18. How do you monitor DB2 performance and identify bottlenecks?

    • Answer: I use DB2's monitoring tools (like Performance Monitor on Windows, or command-line tools on other platforms), along with performance views and explain plans to identify bottlenecks. I also look for slow queries, high CPU usage, and excessive disk I/O.

19. How familiar are you with DB2 security features?

    • Answer: [Describe your familiarity with features like user roles, privileges, access control lists (ACLs), encryption, and auditing. Mention specific examples from your experience.]

20. How do you handle database backups and recovery in DB2?

    • Answer: I use DB2's backup and recovery utilities to create full and incremental backups, regularly testing restoration procedures. The approach taken depends on the Recovery model (online or offline).

Thank you for reading our blog post on 'DB2 Interview Questions and Answers for 5 years experience'.We hope you found it informative and useful.Stay tuned for more insightful content!