What is the difference between a sensitive and insensitive cursor in DB2?

  • A sensitive cursor cannot be used for update operations
  • A sensitive cursor reflects all changes made to the underlying data, while an insensitive cursor does not
  • An insensitive cursor is faster than a sensitive cursor
  • An insensitive cursor locks the data it fetches
The key difference between a sensitive and an insensitive cursor in DB2 lies in how they react to changes made to the underlying data. A sensitive cursor reflects all changes made to the data, ensuring that any updates, inserts, or deletes performed by other transactions are visible to the cursor. On the other hand, an insensitive cursor does not reflect such changes and presents a consistent view of the data as it was when the cursor was opened. Sensitive cursors are useful in applications where real-time data updates are essential, while insensitive cursors may be preferred for performance reasons or when data consistency is not critical. 

What is the main role of an index in a database?

  • Enforcing data integrity
  • Generating reports
  • Improving query performance
  • Storing data
The main role of an index in a database is to improve query performance by facilitating rapid data retrieval. An index is a data structure that contains keys derived from one or more columns of a table, allowing the database management system to locate specific rows efficiently. By reducing the number of disk accesses needed to fulfill queries, indexes speed up data retrieval operations and enhance overall system performance. 

What does the UPDATE statement do in SQL?

  • Define the structure of a database table
  • Delete records from a table
  • Modify existing records in a table
  • Retrieve data from a database
The UPDATE statement in SQL is used to modify existing records in a table. It allows users to change the values of one or more columns in existing rows based on specified conditions. This statement is crucial for updating data in a database when there are changes or corrections needed in the existing records. 

Which system tables can be queried to gather information about database health in DB2?

  • SYSIBM.SNAPDB
  • SYSIBM.SYSINDEXES
  • SYSIBM.SYSSTATS
  • SYSIBM.SYSTABLES
SYSIBM.SNAPDB is a system table that provides a snapshot of database health, including information about locks, buffer pool usage, and other vital statistics. Querying this table can help DBAs monitor and maintain the health of their DB2 databases. 

What factors should be considered before running the Reorg utility in DB2?

  • Available disk space and memory
  • Compatibility with other utilities and applications
  • Database schema changes and transaction logs
  • Database size and workload
Before running the Reorg utility, it's essential to consider factors such as the size of the database, current workload, available disk space, and memory to ensure efficient execution. 

Which type of constraint ensures that each value in a column is unique?

  • Check constraint
  • Default constraint
  • Foreign key constraint
  • Unique constraint
The unique constraint in DB2 ensures that each value in a column is unique and does not allow duplicate values. This constraint is often used to enforce entity integrity by ensuring that no two rows in a table have the same value for a specified column or combination of columns. 

How does DB2 handle deadlock situations during monitoring and troubleshooting?

  • DB2 automatically resolves deadlocks by rolling back the least expensive operation
  • DB2 employs a timeout mechanism to break deadlocks
  • DB2 notifies the administrator and allows manual intervention to resolve deadlocks
  • DB2 uses a deadlock detection algorithm
DB2 uses a deadlock detection algorithm that detects when two or more transactions are waiting for locks held by each other, and it resolves these deadlocks by rolling back one of the transactions involved. Deadlocks are detected by DB2 automatically. 

How can a stored procedure improve performance in a DB2 environment?

  • Enhance data integrity
  • Minimize compilation overhead
  • Optimize query execution
  • Reduce network traffic
Stored procedures can enhance performance in a DB2 environment in various ways. One key advantage is minimizing compilation overhead. By precompiling stored procedures, DB2 can save time during execution by avoiding the need to recompile the same SQL statements repeatedly. This reduces CPU usage and improves overall system performance. Additionally, stored procedures can optimize query execution by executing complex operations on the server side, reducing network traffic and improving response times for client applications. Thus, using stored procedures can significantly enhance performance in a DB2 environment. 

Triggers in DB2 can be classified based on their ________.

  • Action
  • Level
  • Scope
  • Timing
Triggers in DB2 can be classified based on their action, which specifies the event that triggers the execution of the trigger. Common actions include BEFORE and AFTER, determining whether the trigger fires before or after the triggering event. 

The DB2 event monitor captures information about ________.

  • Buffer pool usage
  • Database connections
  • Lock escalations
  • SQL statements
The DB2 event monitor captures information about SQL statements executed against the database. This includes details such as the execution time, resource usage, and the frequency of specific SQL statements. While buffer pool usage, database connections, and lock escalations are important metrics, they are not the primary focus of the event monitor.