Can user-defined functions be used within SQL queries in DB2?

  • No, user-defined functions are not supported in SQL queries
  • User-defined functions can only be used in stored procedures
  • User-defined functions can only be used in triggers
  • Yes, user-defined functions can be used in SQL queries
Yes, user-defined functions can indeed be used within SQL queries in DB2. This capability allows developers to encapsulate complex logic into functions, making SQL queries more concise and easier to understand. Additionally, leveraging user-defined functions enhances code modularity and promotes better code organization. 

What are some common performance metrics monitored in DB2?

  • Buffer pool hit ratio
  • CPU utilization
  • Lock contention
  • Network latency
Buffer pool hit ratio is a critical performance metric in DB2. It measures the percentage of times a requested page is found in the buffer pool, avoiding expensive disk I/O operations. Monitoring and optimizing buffer pool hit ratio can significantly improve database performance. 

In DB2, what does the MIN() function return?

  • The average value in a column
  • The largest value in a column
  • The smallest value in a column
  • The sum of values in a column
The MIN() function in DB2 returns the smallest value in a column, helping users identify the minimum value among a set of values. 

In DB2, a shared lock allows ________ transactions to read data but prevents them from modifying it.

  • Concurrent
  • Concurrent and Simultaneous
  • Multiple
  • Simultaneous
A shared lock in DB2 allows multiple transactions to read data simultaneously. However, it prevents them from modifying it concurrently. This ensures that multiple transactions can access the data for reading purposes without interfering with each other's modifications, promoting data consistency and integrity in a multi-user environment. Shared locks are commonly used to facilitate data retrieval operations without the risk of conflicting modifications by other transactions. 

What are the advantages of using triggers over stored procedures in certain scenarios?

  • All of the above
  • Enhanced performance
  • Improved data integrity
  • Reduced application complexity
Triggers in DB2 offer improved data integrity by enforcing constraints and business rules directly at the database level. This ensures that data remains consistent and valid, regardless of how it is accessed or modified. While stored procedures can also enhance data integrity, triggers provide an additional layer of protection by automatically executing when specific events occur, without requiring explicit invocation by applications. 

What advantages does XML provide over JSON in DB2?

  • Better suited for complex data structures
  • Easier to parse and manipulate
  • More structured and hierarchical data representation
  • Supports namespaces and validation
XML offers more structured and hierarchical data representation compared to JSON, making it better suited for complex data structures. XML also supports namespaces and validation, which can be crucial for certain applications. 

In what scenarios would you prefer using Command Line Tools over Control Center for database administration tasks?

  • When performing repetitive tasks with automation scripts
  • When requiring a visual representation of database objects
  • Both A and B
  • None of the above
Command Line Tools are preferred over Control Center for database administration tasks when performing repetitive tasks with automation scripts. These tools allow administrators to automate routine tasks by scripting commands, making it more efficient for managing large-scale or repetitive operations. Control Center, with its graphical interface, is better suited for tasks that require a visual representation of database objects, such as exploring database structures or visually analyzing query results. Therefore, both options A and B are correct. 

Which normal form requires all determinants to be candidate keys?

  • First Normal Form
  • Fourth Normal Form
  • Second Normal Form
  • Third Normal Form
Third Normal Form (3NF) requires that every non-prime attribute is non-transitively dependent on every candidate key in the table. In other words, all determinants must be candidate keys. This eliminates any transitive dependencies and ensures data integrity and consistency. 

How does Visual Explain aid in query optimization in DB2?

  • Generates SQL scripts for query optimization
  • Identifies potential bottlenecks in query execution
  • Provides detailed statistics on query execution
  • Provides visual representation of access paths
Visual Explain in DB2 aids in query optimization by providing a visual representation of the access paths chosen by the query optimizer for executing the SQL statement. This helps in understanding and analyzing the query execution plan, which is crucial for optimizing query performance. 

Which SQL command is used to add new records to a table?

  • DELETE
  • INSERT
  • SELECT
  • UPDATE
The INSERT command in SQL is used to add new records (rows) to a table. It allows users to specify the values for each column in the table when inserting new data. This command is essential for adding data to tables and expanding the dataset in a database.