C1000-078 sample questions

Question 1

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?

Accepted Answer

RECOVER TABLESPACE TSFIN01, TSFIN02, TSFIN03 TOLOGPOINT X'log_rba_at_0959AM'

Answer

RECOVER TABLESPACE TSFIN01, TSFIN02, TSFIN03

Answer

RESTORE SYSTEM and RECOVER to the current time.

Answer

Use DSN1COPY to restore the table spaces from a backup taken before 10:00 AM.

Question 2

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?

Accepted Answer

Issue the `-ACTIVATE FUNCTION LEVEL (V12R1M505)` command.

Answer

Re-run the Db2 installation CLISTs and specify M505 as the target function level.

Answer

Update the DSNZPARM module with the new function level and recycle the Db2 subsystem.

Answer

Run a `REBIND` on all application packages.

Question 3

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?

Accepted Answer

Implement a `CREATE MASK` statement as part of a Row and Column Access Control (RCAC) policy.

Answer

Create a view that omits the `PATIENT_SSN` column and grant the auditors access to the view.

Answer

Use column-level `GRANT` and `REVOKE` to deny the auditors `SELECT` privilege on the `PATIENT_SSN` column.

Answer

Encrypt the `PATIENT_SSN` column using Db2's built-in encryption.

Question 4

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?

Accepted Answer

A `MERGE` statement.

Answer

An `UPDATE` statement, followed by an `INSERT` statement for rows that were not found.

Answer

A stored procedure that loops through the input, attempts an `UPDATE`, and if `SQLCODE` = +100, performs an `INSERT`.

Answer

A `REPLACE` trigger on the `INVENTORY` table.

Question 5

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?

Accepted Answer

IDTHTOIN

Answer

CMTSTAT

Answer

MAXDBAT

Answer

CONDBAT

Question 6

**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?

```mermaid
graph TD
subgraph Data Lifecycle
A[Ingest 20M rows/day] --> B{Store for 36 months};
B --> C[Purge old data];
end
subgraph Query Pattern
D[User Query] --> E{Filter by date range};
E --> F[Retrieve CDRs];
end
```

Accepted Answer

A Partition-by-Range (PBR) universal table space with a partitioning key on `CALL_DATE` (e.g., one partition per month).

Answer

A classic segmented table space with a clustering index on the call date.

Answer

A Partition-by-Growth (PBG) universal table space with `MAXPARTITIONS 4096`.

Answer

Multiple separate tables, one for each month, managed by a union-all view.

Question 7

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)

Accepted Answer

The statistics for the table are stale and do not reflect the current data distribution.

Accepted Answer

A function is applied to the indexed column in the `WHERE` clause, making the predicate non-indexable.

Answer

The table space is in `COPY-pending` status.

Answer

The query is using `ISOLATION(UR)`.

Question 8

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.

Accepted Answer

True

Answer

False

Question 9

A DBA needs to add a new column, `ORDER_STATUS`, to a very large and highly available `SALES` table. The new column must not allow nulls and should have a default value of 'NEW'. Which `ALTER TABLE` statement will accomplish this with the least impact on application availability and avoid placing the table space in a REORG-pending state?

Accepted Answer

`ALTER TABLE SALES ADD COLUMN ORDER_STATUS CHAR(3) NOT NULL WITH DEFAULT 'NEW';`

Answer

`ALTER TABLE SALES ADD COLUMN ORDER_STATUS CHAR(3) NOT NULL;`

Answer

`ALTER TABLE SALES ADD COLUMN ORDER_STATUS CHAR(3) WITH DEFAULT 'NEW';` followed by a `REORG`.

Answer

Unload the data, drop and recreate the table with the new column, and then load the data back.

Question 10

An application is built using dynamic SQL where values are concatenated into the SQL string, resulting in thousands of unique, single-use statements in the dynamic statement cache. This causes a very low cache hit ratio and high CPU consumption for SQL preparation. How can a DBA configure Db2 to improve the efficiency of the statement cache for this application without changing the application code?

Accepted Answer

Set the ZPARM `STMT_CONC` to `LITERALS`.

Answer

Increase the size of the dynamic statement cache (CACHESIZE).

Answer

Run `REBIND` on the application packages.

Answer

Set the ZPARM `REOPT` to `ALWAYS`.

Free C1000-078 sample questions

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?

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.

10 more free samples are waiting