DB2 Interview Questions and Answers for freshers

1. What is DB2?

   • Answer: DB2 is a relational database management system (RDBMS) developed by IBM. It's known for its scalability, reliability, and performance, often used in enterprise-level applications.

2. What are the different types of DB2 databases?

   • Answer: DB2 offers various database types, including LUW (Linux, UNIX, and Windows), z/OS (mainframe), and i (IBM iSeries).

3. Explain the concept of a schema in DB2.

   • Answer: A schema is a logical grouping of database objects like tables, indexes, views, and stored procedures owned by a specific user or application. It provides a way to organize and manage database components.

4. What is a table in DB2?

   • Answer: A table is a structured set of data organized into rows (records) and columns (fields). It's the fundamental unit for storing data in a relational database.

5. What are data types in DB2? Give examples.

   • Answer: DB2 supports various data types, including INTEGER, SMALLINT, DECIMAL, FLOAT, VARCHAR, CHAR, DATE, TIME, TIMESTAMP, etc. Each type defines the kind of data a column can hold.

6. Explain the difference between CHAR and VARCHAR.

   • Answer: CHAR stores fixed-length strings, always consuming the defined space. VARCHAR stores variable-length strings, using only the space needed for the actual data.

7. What is an index in DB2?

   • Answer: An index is a separate data structure that speeds up data retrieval from a table. It's like an index in a book, allowing faster lookups based on specified columns.

8. What are different types of indexes in DB2?

   • Answer: Common index types include B-tree indexes (most common), unique indexes (ensure uniqueness), and clustered indexes (physically order data).

9. What is a primary key?

   • Answer: A primary key uniquely identifies each row in a table. It cannot contain NULL values and must be unique.

10. What is a foreign key?

    • Answer: A foreign key establishes a link between two tables. It references the primary key of another table, enforcing referential integrity.

11. What is a view in DB2?

    • Answer: A view is a virtual table based on the result-set of an SQL statement. It doesn't store data but provides a customized way to access data from underlying tables.

12. What is a stored procedure in DB2?

    • Answer: A stored procedure is a pre-compiled SQL code block stored in the database. It can accept parameters, perform complex operations, and return results, improving performance and code reusability.

13. What is a trigger in DB2?

    • Answer: A trigger is a procedural code automatically executed in response to certain events on a particular table, such as INSERT, UPDATE, or DELETE operations.

14. Explain normalization in DB2.

    • Answer: Normalization is a database design technique that organizes data to reduce redundancy and improve data integrity. It involves breaking down larger tables into smaller ones and defining relationships between them.

15. What are the different normal forms?

    • Answer: Common normal forms include 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form), and BCNF (Boyce-Codd Normal Form). Each form addresses specific types of redundancy.

16. What is an SQL statement?

    • Answer: SQL (Structured Query Language) is the standard language used to interact with relational databases. SQL statements are used to create, modify, query, and manage data.

17. Write an SQL statement to create a table.

    • Answer: CREATE TABLE employees (employee_id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(30));

18. Write an SQL statement to insert data into a table.

    • Answer: INSERT INTO employees (employee_id, name, department) VALUES (1, 'John Doe', 'Sales');

19. Write an SQL statement to select data from a table.

    • Answer: SELECT * FROM employees;

20. Write an SQL statement to update data in a table.

    • Answer: UPDATE employees SET department = 'Marketing' WHERE employee_id = 1;

21. Write an SQL statement to delete data from a table.

    • Answer: DELETE FROM employees WHERE employee_id = 1;

22. Explain the difference between INNER JOIN and OUTER JOIN.

    • Answer: INNER JOIN returns only matching rows from both tables. OUTER JOIN (LEFT, RIGHT, or FULL) returns all rows from one table and matching rows from the other; non-matching rows are filled with NULLs.

23. What is a subquery?

    • Answer: A subquery is a query nested inside another query. It's used to filter data or provide data for the main query.

24. What is a UNION in SQL?

    • Answer: UNION combines the result sets of two or more SELECT statements into a single result set, removing duplicate rows.

25. What is an aggregate function in SQL? Give examples.

    • Answer: Aggregate functions perform calculations on sets of values and return a single value. Examples include SUM, AVG, COUNT, MIN, and MAX.

26. What is a GROUP BY clause?

    • Answer: GROUP BY groups rows with the same values in specified columns into summary rows, often used with aggregate functions.

27. What is a HAVING clause?

    • Answer: HAVING filters groups created by GROUP BY based on conditions applied to aggregate values.

28. What is the use of WHERE clause?

    • Answer: WHERE clause filters rows based on conditions applied to individual columns.

29. What is a transaction in DB2?

    • Answer: A transaction is a logical unit of work that comprises one or more database operations. It ensures data integrity by either committing all changes or rolling back all changes in case of failure.

30. What are ACID properties?

    • Answer: ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee reliable database transactions.

31. Explain Atomicity in transactions.

    • Answer: Atomicity ensures that all operations within a transaction are treated as a single, indivisible unit. Either all changes are made, or none are.

32. Explain Consistency in transactions.

    • Answer: Consistency guarantees that a transaction maintains the integrity constraints of the database. The database remains in a valid state before and after the transaction.

33. Explain Isolation in transactions.

    • Answer: Isolation ensures that concurrent transactions do not interfere with each other. Each transaction appears to run in isolation from others.

34. Explain Durability in transactions.

    • Answer: Durability guarantees that once a transaction is committed, the changes are permanently stored and survive system failures.

35. What is a deadlock in DB2?

    • Answer: A deadlock occurs when two or more transactions are blocked indefinitely, waiting for each other to release resources.

36. How to resolve deadlocks?

    • Answer: Deadlocks are usually resolved by the database system automatically. One or more transactions are rolled back to break the cycle.

37. What is concurrency control?

    • Answer: Concurrency control manages concurrent access to the database by multiple users or applications, ensuring data integrity and consistency.

38. What are different concurrency control mechanisms?

    • Answer: Common mechanisms include locking (exclusive, shared), timestamping, and optimistic concurrency control.

39. What is locking in DB2?

    • Answer: Locking is a concurrency control mechanism that prevents simultaneous access to data by multiple transactions. It ensures data integrity by allowing only one transaction to access a resource at a time.

40. What are different types of locks?

    • Answer: Types include shared locks (allow reading) and exclusive locks (allow reading and writing).

41. What is a cursor in DB2?

    • Answer: A cursor is a temporary work area that holds a set of rows retrieved from a database. It's used to process data row by row in applications.

42. Explain different cursor types.

    • Answer: Cursors can be forward-only, scrollable, and updatable, depending on the level of navigation and modification allowed.

43. What is data integrity?

    • Answer: Data integrity refers to the accuracy, consistency, and reliability of data. It ensures that data is valid, free from errors, and trustworthy.

44. How to ensure data integrity?

    • Answer: Data integrity is ensured through various techniques such as constraints (primary keys, foreign keys, check constraints), normalization, transactions, and validation rules.

45. What is referential integrity?

    • Answer: Referential integrity ensures that relationships between tables are consistent. It prevents actions that would destroy links between tables.

46. What is a constraint in DB2?

    • Answer: A constraint is a rule that enforces data integrity by limiting the type of data that can be stored in a table. Examples include primary key, foreign key, unique, check, and NOT NULL constraints.

47. What is a checkpoint in DB2?

    • Answer: A checkpoint is a process that periodically synchronizes the database's in-memory data with the disk, minimizing data loss in case of a system failure.

48. What is DB2 catalog?

    • Answer: The DB2 catalog is a set of system tables that store metadata about the database, including information about tables, indexes, views, and other database objects.

49. What is a sequence in DB2?

    • Answer: A sequence is a database object that generates unique numerical values automatically. It is often used as a primary key in tables.

50. What is the role of a database administrator (DBA)?

    • Answer: A DBA is responsible for the overall management and maintenance of the database system, including installation, configuration, performance tuning, security, backup and recovery, and user access control.

51. What are some common DB2 performance tuning techniques?

    • Answer: Techniques include creating appropriate indexes, optimizing SQL queries, using stored procedures, adjusting buffer pools, and monitoring database activity.

52. What are some common DB2 backup and recovery strategies?

    • Answer: Strategies include full backups, incremental backups, and point-in-time recovery (PITR).

53. Explain the concept of a buffer pool in DB2.

    • Answer: A buffer pool is a shared memory area used to cache frequently accessed data pages, improving database performance by reducing disk I/O.

54. What is the difference between COMMIT and ROLLBACK?

    • Answer: COMMIT saves all changes made within a transaction to the database permanently. ROLLBACK undoes all changes and reverts the database to its previous state.

55. How do you handle exceptions in DB2 stored procedures?

    • Answer: Exceptions are handled using TRY...CATCH blocks, allowing you to gracefully handle errors and prevent unexpected termination of stored procedures.

56. What is the importance of database security?

    • Answer: Database security protects sensitive data from unauthorized access, modification, or deletion. It is crucial for maintaining data confidentiality, integrity, and availability.

57. What are some common DB2 security features?

    • Answer: Features include user authentication, authorization (granting privileges), encryption, access controls, and auditing.

58. What is a materialized query table (MQT)?

    • Answer: An MQT is a table that stores the pre-computed results of a query. It improves query performance by reducing the need to execute the query every time.

59. What is data warehousing?

    • Answer: Data warehousing is a process of collecting, storing, and managing large amounts of data from various sources for analytical processing and reporting.

60. How does DB2 support data warehousing?

    • Answer: DB2 provides features like partitioning, indexing, and data compression to optimize the performance of data warehousing applications.

61. What is a partition in DB2?

    • Answer: A partition divides a large table into smaller, more manageable parts. This improves performance by allowing parallel processing and reducing I/O.

62. What is DB2 pureScale?

    • Answer: DB2 pureScale is a high-availability and scalability feature that allows multiple database servers to work together as a single system.

63. What is the use of the `FETCH` statement?

    • Answer: The `FETCH` statement retrieves rows from a cursor one at a time.

64. Explain the concept of a temporary table in DB2.

    • Answer: A temporary table is a table that exists only for the duration of a connection or session. It's useful for storing intermediate results during complex operations.

65. How do you handle NULL values in DB2?

    • Answer: NULL values represent missing or unknown data. They are handled using functions like `IS NULL`, `IS NOT NULL`, `COALESCE`, and `NVL`.

66. What is the role of the `CASE` statement?

    • Answer: The `CASE` statement allows conditional logic within SQL queries, enabling different actions or results based on different conditions.

67. What is the purpose of the `DECLARE` statement in DB2?

    • Answer: The `DECLARE` statement declares variables within stored procedures or functions.

68. How can you improve the performance of a DB2 query?

    • Answer: Use appropriate indexes, optimize WHERE clauses, avoid using SELECT *, and use efficient data types.

69. Explain the use of the `LIKE` operator.

    • Answer: The `LIKE` operator is used for pattern matching in string comparisons, often used with wildcard characters `%` and `_`.

70. How do you handle large datasets in DB2?

    • Answer: Use techniques like partitioning, indexing, and optimizing queries. Consider using specialized tools for large data processing.

71. What is a synonym in DB2?

    • Answer: A synonym is an alias for a table or other database object. It simplifies referencing objects with long or complex names.

72. What are some common DB2 utilities?

    • Answer: `db2look` (generates DDL statements), `db2 batch` (runs SQL scripts), and `db2 restore` (restores database backups).

73. What is the `GRANT` statement used for?

    • Answer: The `GRANT` statement is used to grant database privileges to users or roles.

74. What is the `REVOKE` statement used for?

    • Answer: The `REVOKE` statement is used to revoke previously granted database privileges.

75. What is the difference between `TRUNCATE` and `DELETE` statements?

    • Answer: `TRUNCATE` removes all rows from a table quickly, deallocating space. `DELETE` removes rows one by one and can be used with a `WHERE` clause for selective deletion.

76. Explain the concept of implicit and explicit cursors.

    • Answer: Implicit cursors are automatically managed by DB2 for single-row operations. Explicit cursors are declared and managed by the programmer for multi-row operations.

77. How can you monitor DB2 performance?

    • Answer: Use DB2 performance monitoring tools, analyze query execution plans, and review database logs.

78. What is a role in DB2?

    • Answer: A role is a named collection of database privileges. It simplifies managing privileges by granting them to a role, and then granting the role to users.

79. What is the significance of the `WITH` clause?

    • Answer: The `WITH` clause (also known as a common table expression or CTE) allows you to define temporary named result sets, simplifying complex queries.

80. Explain the concept of data fragmentation in DB2.

    • Answer: Data fragmentation occurs when data is not stored contiguously, impacting performance. It can be internal (within a data page) or external (across multiple pages).

81. How can you prevent data fragmentation in DB2?

    • Answer: Use appropriate table space types, regularly reorganize tables, and consider data partitioning.

82. What is the use of the `ROW_NUMBER()` function?

    • Answer: `ROW_NUMBER()` assigns a unique sequential integer to each row within a partition of a result set.

83. How would you handle long-running queries in DB2?

    • Answer: Optimize the query, add indexes, and consider using asynchronous processing or background jobs.

84. What are some strategies for improving DB2 database availability?

    • Answer: Use high-availability features like DB2 pureScale, implement regular backups and recovery strategies, and use load balancing techniques.

85. What are some common DB2 error messages and their solutions?

    • Answer: This is too broad. Specific error messages require specific solutions, usually found in the DB2 documentation.

86. Describe your experience with SQL query optimization.

    • Answer: As a fresher, my experience is limited to academic exercises and possibly some small projects. I understand the basic principles of optimizing queries, such as using indexes, analyzing execution plans, and avoiding inefficient operations like `SELECT *`.

87. Explain your understanding of database normalization and its benefits.

    • Answer: I understand that normalization is a process of organizing database tables to reduce redundancy and improve data integrity. The benefits include reduced data storage space, improved data consistency, and easier data modification.

88. How familiar are you with DB2 command-line tools?

    • Answer: I have some basic familiarity with DB2 command-line tools, such as `db2`, for connecting to the database and running simple SQL commands.

89. Tell me about a time you had to troubleshoot a database issue.

    • Answer: As a fresher, I haven't encountered many complex database issues in a professional setting. However, in academic projects, I've learned to use error messages and logging information to identify and resolve minor issues, like syntax errors in SQL queries or issues with data types.

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