IBM Certification IBM Db2 Database Administration 2 — Questions and Answers
Question 1: What is the difference between 'BACKUP' and 'EXPORT' in IBM Db2?
- BACKUP creates an offline copy of the entire database; EXPORT writes table data to a file in a portable format (Correct answer)
- They are identical commands with different names
- BACKUP is for indexes only; EXPORT is for tables only
- BACKUP requires root access; EXPORT does not
Correct answer: BACKUP creates an offline copy of the entire database; EXPORT writes table data to a file in a portable format
BACKUP creates a full binary copy of the entire Db2 database for recovery purposes, while EXPORT writes table data to an external file in formats like CSV or IXF.
Question 2: In IBM Db2, what is the purpose of the 'db2diag.log' file?
- Storing user query results
- Recording diagnostic and error messages from the Db2 instance for troubleshooting (Correct answer)
- Logging all SELECT statements issued
- Tracking license usage metrics
Correct answer: Recording diagnostic and error messages from the Db2 instance for troubleshooting
The db2diag.log file is Db2's primary diagnostic log, recording internal errors, warnings, and informational messages used for troubleshooting and support.
Question 3: What does the Db2 'EXPLAIN' facility provide to a DBA?
- A human-readable explanation of Db2 licensing terms
- The query execution plan showing how the optimizer will access tables and indexes (Correct answer)
- The list of all users connected to the database
- The explanation of syntax errors in SQL statements
Correct answer: The query execution plan showing how the optimizer will access tables and indexes
The EXPLAIN facility captures the query access plan chosen by the Db2 optimizer, showing table scan vs. index access methods, join strategies, and estimated costs.
Question 4: Which Db2 command is used to list all active database connections on an instance?
- LIST CONNECTIONS
- LIST APPLICATIONS (Correct answer)
- SHOW SESSIONS
- GET CONNECTIONS
Correct answer: LIST APPLICATIONS
The `LIST APPLICATIONS` command displays all applications currently connected to Db2 databases on the instance, including their agent IDs and connection details.
Question 5: What is IBM Db2's 'autonomic computing' feature known as 'Self-Tuning Memory Manager' (STMM)?
- A feature that automatically patches Db2 software
- A feature that dynamically adjusts memory allocations between buffer pools and internal memory heaps based on workload (Correct answer)
- A feature that self-heals corrupted data pages
- A feature that automatically creates indexes based on query patterns
Correct answer: A feature that dynamically adjusts memory allocations between buffer pools and internal memory heaps based on workload
STMM dynamically reallocates memory between buffer pools, the sort heap, and other memory consumers based on real-time workload demands, optimizing memory usage automatically.
Question 6: In IBM Db2, what is 'row compression' used for?
- Compressing backup files
- Reducing the physical storage size of table rows using a dictionary-based compression algorithm (Correct answer)
- Encrypting sensitive column data
- Compressing index entries for faster lookups
Correct answer: Reducing the physical storage size of table rows using a dictionary-based compression algorithm
Db2 row compression uses a dictionary-based algorithm to compress repeated data patterns within table rows, reducing storage footprint and improving I/O efficiency.
IBM Certification IBM Db2 Database Administration 2
What is the difference between 'BACKUP' and 'EXPORT' in IBM Db2?