DB2 offers support for multiple ________ types.

  • Data
  • Index
  • Storage
  • Table
DB2 offers support for multiple table types, including regular tables, system-maintained temporal tables, and declared global temporary tables. This flexibility allows users to organize and manage data efficiently based on their specific requirements. 

What is the behavior of the STDDEV() function in DB2 when applied to a column with NULL values?

  • It returns NULL
  • It returns 0
  • It returns 1
  • It returns error
The correct option is 1. The STDDEV() function in DB2 returns NULL when applied to a column with NULL values. It does not include NULL values in the calculation of standard deviation. 

Which DB2 feature facilitates automatic failover in the event of a primary server failure?

  • Data Partitioning
  • Database Encryption
  • HADR (High Availability Disaster Recovery)
  • Multi-Instance Failover
HADR (High Availability Disaster Recovery) in DB2 facilitates automatic failover by maintaining a standby server that automatically takes over in the event of a primary server failure. 

Row-level locking in DB2 provides ________ granularity for locking compared to table-level locking.

  • Coarser
  • Finer
  • No locking
  • Similar
Row-level locking in DB2 provides finer granularity for locking compared to table-level locking. With row-level locking, only the specific rows that are being modified are locked, allowing other transactions to access and modify different rows concurrently. This reduces contention and improves concurrency in the database. 

Which utility is commonly used for database backup and recovery tasks in DB2?

  • db2backup
  • db2dart
  • db2recovery
  • db2restore
db2restore is commonly used for database backup and recovery tasks in DB2. It allows users to restore the database to a previous state by applying archived log files or backup images. db2backup is used to create full or incremental backups of the database, while db2recovery is used to roll forward or roll back changes to the database. db2dart is a diagnostic tool used to analyze and repair corrupted database objects. 

What is the purpose of database backup in DB2?

  • To encrypt sensitive data
  • To improve query performance
  • To manage user access permissions
  • To prevent data loss in case of system failures
Database backup in DB2 serves the purpose of preventing data loss in case of system failures. By creating backups, organizations can restore their data to a previous state if it gets corrupted or lost due to various reasons such as hardware failures, human errors, or disasters. This ensures data integrity and business continuity. 

Scenario: A developer needs to store images in a DB2 database. Which data type should they choose for the corresponding column?

  • BLOB
  • CHAR
  • DECIMAL
  • VARCHAR
BLOB (Binary Large Object) data type is suitable for storing images in a DB2 database. BLOB allows for the storage of large binary objects, such as images, audio, or video files. It preserves the binary format of the data without any character encoding, making it ideal for non-textual data like images. 

What is data integrity in DB2?

  • Ensuring accuracy of data
  • Ensuring consistency of data
  • Ensuring availability of data
  • Ensuring security of data
Data integrity in DB2 refers to maintaining the accuracy and consistency of data stored in the database. It ensures that data remains valid, reliable, and consistent throughout its lifecycle. This includes enforcing rules and constraints to prevent invalid data entry or modification, such as ensuring referential integrity and maintaining data consistency across multiple tables. Therefore, the correct option is to ensure the consistency of data. 

Triggers in DB2 are activated by ________ events.

  • Data manipulation
  • Database connection events
  • Software installation events
  • User authentication events
Triggers in DB2 are primarily activated by data manipulation events, such as INSERT, UPDATE, and DELETE operations on specified tables. These triggers execute automatically when the defined event occurs in the database. 

Scenario: A DBA notices that a query is running slowly in DB2. Upon investigation, it is found that the table being queried does not have any indexes. What recommendation would you provide to optimize the query performance?

  • Create indexes on the columns involved in the query.
  • Increase the server memory to improve query performance.
  • Partition the table to distribute data across multiple disks.
  • Rewrite the query to use optimized SQL constructs.
Adding indexes to the columns involved in the query can significantly enhance query performance by allowing DB2 to quickly locate the relevant rows. This reduces the need for full table scans and can lead to faster query execution. Rewriting the query might help in some cases, but adding indexes is a more direct solution to the problem of slow query performance due to the lack of indexes. Increasing server memory or partitioning the table may not directly address the issue of slow query performance caused by missing indexes.