IBM Db2 12 for z/OS Administrator Free Sample Questions

A financial services company needs to run frequent, high-performance queries against a large transaction table. The queries filter transactions based on the year and month of the transaction timestamp, which is stored in a TRANSACTION_TS column of data type TIMESTAMP. The current query uses WHERE YEAR(TRANSACTION_TS) = ? AND MONTH(TRANSACTION_TS) = ?, which results in a full table space scan. How should the DBA improve the performance of these queries?

While analyzing buffer pool performance for a critical OLTP application, a DBA notices a high number of synchronous read I/Os and a low buffer pool hit ratio. Further investigation reveals that a nightly batch job performs a full table scan on a large, infrequently accessed table, effectively flushing out the OLTP application's frequently used pages from the buffer pool. Which buffer pool setting should be adjusted to mitigate this issue?

At 2:00 PM, a DBA discovers that a critical application bug introduced logical data corruption starting at approximately 10:00 AM. The business requires that the three affected table spaces (TSFIN01, TSFIN02, TSFIN03) be restored to their state at exactly 09:59 AM, just before the corruption began. All other table spaces in the database must remain online and current. Which utility control statement is the most precise and appropriate for this recovery scenario?

A Db2 12 for z/OS subsystem is currently at function level V12R1M503. The development team requires a new SQL feature that was introduced in function level V12R1M505. What is the correct procedure for the DBA to enable the new functionality?

A healthcare provider must allow external auditors to query patient demographic data. However, compliance regulations mandate that the auditors must not be able to view the actual values in the Social Security Number (PATIENT_SSN) column. The auditors should see a masked value, such as 'XXX-XX-XXXX', while internal applications must continue to see the real SSN. Which Db2 security feature is best suited for this requirement?

A nightly batch process receives a file of daily product stock updates. For each product in the file, the process must update the quantity if the product already exists in the INVENTORY table or insert a new row if it does not. Which single SQL statement provides the most efficient and atomic way to perform this 'upsert' operation?

A global banking application connects to Db2 for z/OS through the Distributed Data Facility (DDF). During non-peak hours, hundreds of connections remain established but idle for long periods, consuming valuable thread resources (TCBs) and virtual storage. The operations team wants to automatically terminate these idle connections after 30 minutes to reclaim system resources. Which DSNZPARM parameter should the DBA configure?

Case Study:

A major telecommunications company is designing a database to store Call Detail Records (CDRs). The lead architect has provided the following requirements and constraints for the primary CDR table.

Business Requirements:

• The table, CDR_MASTER, will ingest approximately 20 million records per day.
• Queries will almost always filter by a specific date range, typically for one or two days at a time.
• Data older than 36 months must be purged from the table with minimal impact on production workload and without generating massive amounts of log records.
• The solution must support high-speed data loading via the LOAD utility.

Technical Constraints:

• The table is expected to grow to over 20 billion rows.
• Administrative overhead for managing table growth and data purging should be minimized.
• The solution must prevent any single data set from exceeding the 64GB limit.

Given these requirements, which database design is the most appropriate for the CDR_MASTER table?

A performance analyst reviews an EXPLAIN output for a query that is performing poorly. The access path shows a table space scan is being used on a very large table, even though an index exists on the columns in the WHERE clause. Which of the following are plausible reasons for the optimizer choosing a table space scan? (Select TWO)

True or False: An online REORG TABLESPACE requires a brief application outage for all DML activity against the table space during its final LOG phase when it switches to the shadow copy.