C1000-078 Sample Questions

C1000-078 Sample Questions & Answers

Designing tables, views and indexes leads the way, with the remainder split between operation and recovery commands, protecting and auditing access, tuning performance, planning installs and migrations, SQL constructs, and distributed data access.

Launch the full C1000-078 simulator →

Showing 10 of 20 free samples.

  1. Question 1Intermediate

    Additional Database Functionality · Describe the use of triggers, merge statement, pagination options and JSON support

    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?

    Show answer & explanation

    Correct answer: C

    The MERGE statement is specifically designed for this 'upsert' scenario. It allows specifying actions for both matched (WHEN MATCHED THEN UPDATE) and non-matched (WHEN NOT MATCHED THEN INSERT) conditions in a single, atomic, and set-based operation. This is the most efficient and standard SQL approach.

  2. Question 2Advanced

    Distributed Access · Recommend values for critical DDF configuration settings

    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?

    Show answer & explanation

    Correct answer: C

    IDTHTOIN (Idle Thread Timeout Interval) is the specific ZPARM designed to address this problem. It specifies the amount of time in seconds that an inactive server thread (DBAT) can remain idle before it is automatically terminated by Db2. Setting this to 1800 (30 minutes) would achieve the desired outcome.

  3. Question 3Advanced

    Database Design and Implementation · Design universal table spaces

    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?

    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
    Show answer & explanation

    Correct answer: C

    This design perfectly meets all requirements. A PBR universal table space allows for massive scalability. Partitioning by date enables partition pruning for fast date-range queries. Most importantly, purging old data can be done efficiently using ALTER TABLE ... ROTATE PARTITION, which is a fast, minimally logged operation. This also ensures individual partitions (data sets) remain a manageable size.

  4. Question 4IntermediateSelect 2

    Performance · Review and tune SQL

    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)

    Show answer & explanation

    Correct answers: A, C

    If statistics are out of date (e.g., CARDF is much lower than the actual row count), the optimizer might incorrectly calculate that a table space scan is cheaper than using the index. This is a very common cause for suboptimal access paths.

    Applying a function (e.g., WHERE UPPER(LAST_NAME) = 'SMITH') to a column in a predicate prevents a standard index on that column from being used. The optimizer cannot look up the modified value in the index and is forced to scan the table and apply the function to every row.

  5. Question 5Beginner

    Operation and Recovery · Reorganize objects when necessary

    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.

    Show answer & explanation

    Correct answer: A

    This statement is true. While an online REORG allows applications to access the data for most of its duration, there is a very short period of unavailability during the final LOG and SWITCH phases. During this time, Db2 applies any logged changes to the shadow copy and then switches the application's access to the newly reorganized data. DML is locked out during this switch.

  6. Question 6Intermediate

    Database Design and Implementation · Use the appropriate method to alter Db2 objects

    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?

    Show answer & explanation

    Correct answer: C

    This is the correct statement. In Db2 12, adding a column with NOT NULL WITH DEFAULT is an immediate schema change. Db2 handles this by storing the default value in the catalog and only materializing it in the data pages as they are accessed. This operation is very fast, does not require an immediate REORG, and has minimal impact on availability.

  7. Question 7Intermediate

    Performance · Monitor dynamic SQL performance

    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?

    Show answer & explanation

    Correct answer: B

    The STMT_CONC (Statement Concentration) ZPARM, when set to LITERALS, instructs Db2 to replace literals in dynamic SQL statements with parameter markers (?) before caching. This allows multiple statements that differ only by their literal values to match a single cached entry, dramatically increasing the cache hit ratio and reducing CPU overhead for statement preparation.

  8. Question 8Intermediate

    Operation and Recovery · Identify and respond to advisory/restrictive statuses on Db2 objects

    A LOAD RESUME YES LOG NO utility job, which was loading data into a table with referential integrity constraints, failed midway due to an input data error. After the failure, a DBA issues a -DISPLAY DATABASE command and observes that the target table space is in CHECK-pending (CHKP) status. What is the required action to clear this restrictive status and make the table space accessible?

    Show answer & explanation

    Correct answer: D

    The CHECK-pending status is set when an operation (like a failed LOAD) may have introduced data that violates integrity constraints (e.g., referential integrity). The CHECK DATA utility is specifically designed to scan the table space, identify any constraint violations, and, if it completes successfully, reset the CHECK-pending status.

  9. Question 9Advanced

    Installation and Migration · Identify and explain Db2 data sharing components and commands

    In a Db2 for z/OS data sharing group, an LPAR hosting a member subsystem needs to be shut down for scheduled hardware maintenance. To ensure the highest availability for the applications, what is the proper command to issue to shut down the member so that its held locks are retained by the group and inflight transactions can be resolved by other active members?

    Show answer & explanation

    Correct answer: C

    MODE(QUIESCE) is specifically designed for planned outages in a data sharing environment. It stops new work from starting on the member, allows existing work to complete, and ensures that any retained locks are transferred to the group. This allows other members to resolve indoubt transactions and provides the most graceful and highly available method for taking a member offline.

  10. Question 10IntermediateSelect 2

    Security and Auditing · Audit Db2 activity and resources and identify primary audit techniques

    A security administrator needs to implement an audit policy that tracks two specific activities: all failed attempts to connect to the Db2 subsystem, and any INSERT, UPDATE, or DELETE operations performed on the HR.EMPLOYEE_SALARY table. Which audit policy specifications are required to meet these requirements? (Select TWO)

    Show answer & explanation

    Correct answers: A, D

    The AUTHFAIL category is used to audit authorization failures. Specifying AUTHFAIL(ALL) will capture all failed connection attempts and other authorization checks, fulfilling the first requirement.

    The CHANGE category is used to audit data modification events. Specifying CHANGE(ALL) ON TABLE HR.EMPLOYEE_SALARY will capture all INSERT, UPDATE, and DELETE statements executed against that specific table, fulfilling the second requirement.

Ready for the real thing?

The full C1000-078 simulator has every exam-style question, timed mode, and instant scoring.

Go to the C1000-078 simulator →