I85PREP
⌘ K
IBM Db2 13 z/OS DBA Professional study materials
IBM Db2 13 z/OS DBA Professional study resources

IBM Db2 13 z/OS DBA Professional practice questions

One question at a time. Build your IBM Db2 13 z/OS DBA Professional understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Db2 13 for z/OS Database Administration

In Db2 13 for z/OS, which catalog table records the packages bound into a plan, including the order in which they are listed for a given plan?

  1. SYSIBM.SYSPACKLIST Correct answer.
  2. SYSIBM.SYSROUTINES
  3. SYSIBM.SYSDBRM
  4. SYSIBM.SYSSTMT

Explanation

SYSPACKLIST holds one row per package (collection ID and package ID) listed in a plan, along with its sequence number, which determines package list order at execution time. SYSPACKAGE holds package-level attributes, while SYSPACKLIST is the plan-to-package association table.

Question 2Db2 13 for z/OS Database Administration

A DBA wants a table space to be scanned with sequential prefetch automatically chosen by Db2 at run time rather than fixed at bind. Which behavior applies to the default (DYNAMIC) prefetch setting in Db2 13 for z/OS?

  1. Db2 disables prefetch entirely until the plan is rebound with STATIC.
  2. Db2 forces sequential prefetch only when the table space has more than one data set.
  3. Db2 always uses list prefetch for any table space scan regardless of the access path.
  4. Db2 chooses sequential, list, or no prefetch based on the access path and available buffers at execution time. Correct answer.

Explanation

With DYNAMIC prefetch (the default), Db2 decides at execution time whether to use sequential prefetch, list prefetch, or none, based on the chosen access path and buffer availability. STATIC requires the prefetch quantity to be fixed at bind time, and NONE/PAGE prevents sequential prefetch.

Question 3Db2 13 for z/OS Database Administration

Which utility is the correct choice to reorganize a partitioned table space and rebuild its indexes while maintaining the partition-by-partition availability of the table space?

  1. REORG TABLESPACE with SHRLEVEL CHANGE and inline REBUILD INDEX Correct answer.
  2. REORG TABLESPACE with SHRLEVEL REFERENCE and inline COPY
  3. LOAD REPLACE with LOG NO on the partitioned table space
  4. REBUILD INDEX with SHRLEVEL CHANGE on each index

Explanation

REORG TABLESPACE SHRLEVEL CHANGE allows read and write access during the reorganization, and the inline REBUILD INDEX option rebuilds the associated indexes as part of the same job. This maintains availability better than SHRLEVEL REFERENCE, which blocks writers.

Question 4Db2 13 for z/OS Database Administration

A DBA is defining a large table space and wants to use automatic space management. Which clause on CREATE TABLESPACE enables Db2 to manage extent allocation and data set sizing automatically?

  1. MAXPARTITIONS with a fixed NUMPARTS value
  2. USING STOGROUP with PRIQTY and SECQTY specified explicitly
  3. AUTOMATIC with a defined DSSIZE, or omitting PRIQTY and SECQTY when using SMS-managed storage Correct answer.
  4. SEGSIZE 0 with DSSIZE fixed

Explanation

In Db2 13 for z/OS, omitting PRIQTY and SECQTY (and using a Db2-managed STOGROUP backed by SMS) allows Db2 to automatically manage space allocation, including extent sizes. This is the automatic space management behavior for table spaces and index spaces.

Question 5Db2 13 for z/OS Database Administration

Which Db2 for z/OS feature allows a DBA to define a table space that automatically adds partitions as data grows, without predefining NUMPARTS?

  1. MAXPARTITIONS with a partitioning growth specification Correct answer.
  2. A LOB table space with AUX tables
  3. A segmented table space with SEGSIZE 64
  4. A universal table space with MEMBER CLUSTER

Explanation

MAXPARTITIONS on CREATE TABLESPACE enables partition-by-growth, where Db2 adds partitions automatically as data grows up to the specified maximum. This is distinct from range-partitioned table spaces defined with NUMPARTS.

Question 6Db2 13 for z/OS Database Administration

A DBA needs to change the clustering index of a table without dropping and redefining the table. Which Db2 13 for z/OS capability supports this?

  1. ALTER TABLE with DATA CAPTURE CHANGES
  2. ALTER INDEX with CLUSTER NO followed by REORG
  3. ALTER INDEX with a new clustering specification and then REORG to recluster Correct answer.
  4. ALTER TABLE with ALTER PARTITIONING and a new clustering specification

Explanation

ALTER INDEX can change the CLUSTER attribute of an index to designate it as the clustering index, and a subsequent REORG applies the clustering order to the data. This avoids dropping and redefining the table.

Question 7Db2 13 for z/OS Database Administration

Which Db2 for z/OS object must exist before a stored procedure can be created with the EXTERNAL NAME clause referencing a load module?

  1. A DBRM containing the procedure's SQL
  2. An active WLM application environment for the procedure Correct answer.
  3. A plan bound with ISOLATION UR
  4. A package bound with the correct collection

Explanation

External stored procedures in Db2 for z/OS execute in a WLM-established application environment, so an active WLM application environment must be defined before creation. The procedure's package is bound later and is not a prerequisite for the CREATE PROCEDURE statement.

Question 8Db2 13 for z/OS Database Administration

A DBA wants to enable data sharing so that multiple Db2 members in a data sharing group can access the same table space. Which object must be defined as the shared resource manager?

  1. The BSDS of each member
  2. The coupling facility structure for the group buffer pool Correct answer.
  3. The IRLM in each member
  4. The active log data sets

Explanation

Data sharing requires a coupling facility structure for the group buffer pool, which coordinates page invalidation and caching across members. IRLM manages locking within each member, and BSDS and active logs are per-member recovery resources.

Question 9Db2 13 for z/OS Database Administration

A table has a referential constraint with ON DELETE CASCADE from parent to child. A DBA deletes a parent row that has five child rows. What does Db2 do?

  1. The delete fails because dependent rows exist.
  2. The child rows are set to null if the foreign key column allows nulls.
  3. The five child rows are deleted automatically as part of the same statement. Correct answer.
  4. The child rows remain and the constraint is marked as violated until a REORG.

Explanation

ON DELETE CASCADE causes Db2 to delete dependent child rows automatically when the parent row is deleted. ON DELETE SET NULL is the alternative that nulls the foreign key columns when permitted.

Question 10Db2 13 for z/OS Database Administration

A DBA runs RUNSTATS with the KEYCARD option on a table's index. What information does this collect?

  1. The number of distinct values for the index key columns Correct answer.
  2. The clustering ratio of the index
  3. The number of leaf pages in the index
  4. The number of index levels

Explanation

KEYCARD collects the number of distinct values for the key columns, which improves cardinality estimates for predicates on those columns. FREQVAL collects frequent values, and other statistics such as NLEAF and CLUSTERRATIO are gathered by default or with separate options.

Question 11Db2 13 for z/OS Database Administration

A DBA needs to capture changes made to a table for SQL replication. Which ALTER TABLE option enables this capture?

  1. EDITPROC with a compression routine
  2. DATA CAPTURE NONE
  3. DATA CAPTURE CHANGES Correct answer.
  4. VALIDPROC with a validation routine

Explanation

DATA CAPTURE CHANGES causes Db2 to write change records to the log that the capture program can read for replication. DATA CAPTURE NONE disables this logging of change data.

Question 12Db2 13 for z/OS Database Administration

A DBA wants to reduce the cost of repeated dynamic SQL statement preparation for an application. Which Db2 for z/OS feature caches prepared dynamic statements for reuse?

  1. The group buffer pool
  2. The EDM pool statement cache Correct answer.
  3. The RID pool
  4. The sort pool

Explanation

The dynamic statement cache in the EDM pool stores prepared dynamic SQL statements so that identical statements can be reused without a full prepare. This reduces prepare CPU for applications that issue repeated dynamic SQL.

Question 13Db2 13 for z/OS Database Administration

A DBA is binding a package and wants Db2 to defer as much access path selection as possible to run time, allowing reoptimization for different input values. Which bind option should be used?

  1. REOPT AUTO Correct answer.
  2. REOPT ALWAYS
  3. REOPT NONE
  4. REOPT ONCE

Explanation

REOPT AUTO lets Db2 decide at execution time whether to reoptimize based on the input values and the access path, reoptimizing only when it is likely to help. REOPT ALWAYS forces reoptimization for every execution, and REOPT ONCE uses the first set of values.

Question 14Db2 13 for z/OS Database Administration

A DBA wants to make a table space read-only to prevent modifications while allowing queries. Which ALTER TABLESPACE option achieves this?

  1. ALTER TABLESPACE with ACCESS FORCE
  2. ALTER TABLESPACE with CLOSE NO
  3. ALTER TABLESPACE with LOGGED
  4. ALTER TABLESPACE with ACCESS READ ONLY Correct answer.

Explanation

ALTER TABLESPACE with ACCESS READ ONLY restricts the table space to read access, preventing inserts, updates, and deletes while allowing queries. ACCESS FORCE is used to force utilities or to remove restrictions.

Question 15Db2 13 for z/OS Database Administration

A DBA is investigating poor performance and wants to know which access path Db2 chose for a static SQL statement. Which catalog table provides statement-level access path and cost information after EXPLAIN?

  1. SYSIBM.SYSPACKAGE
  2. SYSIBM.SYSSTMT Correct answer.
  3. SYSIBM.SYSPLAN
  4. SYSIBM.SYSINDEXES

Explanation

SYSSTMT stores one row per SQL statement in a package or DBRM, including access path and cost information populated by EXPLAIN. SYSPACKAGE holds package-level attributes, and SYSPLAN holds plan-level attributes.

Question 16Db2 13 for z/OS Database Administration

A DBA wants to create a clone table for use with online schema change so that a new version of a table can be populated while the original remains available. Which Db2 13 for z/OS feature supports this?

  1. ALTER TABLE with ADD CLONE and then EXCHANGE DATA Correct answer.
  2. ALTER TABLE with DATA CAPTURE CHANGES
  3. REORG TABLESPACE with SHRLEVEL CHANGE
  4. LOAD REPLACE with SHRLEVEL NONE

Explanation

Db2 for z/OS supports adding a clone of a table with ALTER TABLE ADD CLONE, populating it, and then using EXCHANGE DATA to swap the clone into place. This enables online schema changes with minimal outage.

Question 17Db2 13 for z/OS Database Administration

A DBA needs to monitor lock contention and identify which members in a data sharing group are involved in a global lock conflict. Which Db2 for z/OS resource provides this information?

  1. The IFCID 226 records in the accounting trace
  2. The IFCID 196 records in the IFCID trace for lock timeouts Correct answer.
  3. The IFCID 105 records in the monitor trace
  4. The IFCID 172 records in the statistics trace

Explanation

IFCID 196 reports lock timeout and deadlock information, including the members involved in a global lock conflict, when the IFCID trace is active. This is the primary trace record used to diagnose cross-member lock contention.

Question 18Db2 13 for z/OS Database Administration

A DBA observes that a utility job fails with a resource unavailable reason code because a restrictive state exists on the target object. Which action correctly resolves the state before rerunning the utility?

  1. Issue TERM UTILITY to terminate all utilities and ignore the state.
  2. Issue START DATABASE with the ACCESS FORCE option to clear the state.
  3. Issue MODIFY RECOVERY to delete the recovery information for the object.
  4. Issue DISPLAY DATABASE to identify the restrictive state and then use the appropriate RECOVER or START command to remove it. Correct answer.

Explanation

DISPLAY DATABASE shows restrictive states such as COPY pending or RECOVER pending, and the appropriate RECOVER or START command clears the specific state. Blindly forcing access or terminating utilities does not address the underlying state.

Question 19Db2 13 for z/OS Database Administration

A DBA wants to use the Db2 for z/OS profile tables to control which users can execute a specific plan. Which profile table column and table are used to specify the plan name?

  1. SYSIBM.SYSUSERAUTH with the PLAN column
  2. SYSIBM.SYSPLANAUTH with the GRANTEE column
  3. SYSIBM.SYSPROFILE with the PLAN column Correct answer.
  4. SYSIBM.SYSDUMMY1 with the AUTHID column

Explanation

The profile tables, including SYSIBM.SYSPROFILE, let a DBA control plan and package authorization by specifying the PLAN column and associated attributes. These tables are used with the profile monitoring function to apply authorization rules.

Question 20Db2 13 for z/OS Database Administration

A DBA is designing a table space and wants to ensure that rows for a given key are physically clustered to improve range scan performance. Which Db2 for z/OS option should be used when defining the index?

  1. CLUSTER YES on the index Correct answer.
  2. DEFINE YES on the index
  3. UNIQUE on the index
  4. PIECESIZE on the index

Explanation

CLUSTER YES designates the index as the clustering index, so Db2 attempts to keep rows in that key order when inserting and reorganizing. This improves the effectiveness of range scans on that key.

Question 21Db2 13 for z/OS Database Administration

A DBA wants to limit the number of parallel tasks used for a query to avoid excessive resource consumption. Which Db2 for z/OS bind or subsystem parameter governs the maximum degree of parallelism?

  1. The CURRENT DEGREE special register and the CDSSRDEF subsystem parameter
  2. The PARAMDEG DSNZPARM and the PARALLEL bind option
  3. The MAXDEGREE DSNZPARM and the DEGREE bind option Correct answer.
  4. The NUMPARTS parameter and the DEGREE bind option

Explanation

The MAXDEGREE subsystem parameter sets the maximum degree of parallelism for the subsystem, and the DEGREE bind option (or the CURRENT DEGREE special register) controls parallelism for a plan or package. Together they bound the number of parallel tasks.

Question 22Db2 13 for z/OS Database Administration

A DBA needs to remove rows from a large table while keeping the table space online for readers and writers. Which statement best describes the effect of the DELETE statement with a WHERE clause on a large table in Db2 13 for z/OS?

  1. It requires a REORG before the deleted rows are physically removed and space is reclaimed.
  2. It automatically triggers a REORG utility job to reclaim the space.
  3. It acquires an exclusive lock on the entire table space for the duration of the delete.
  4. It deletes rows and logs each change, and the table space remains available subject to normal locking and logging. Correct answer.

Explanation

A DELETE statement removes rows, logs the changes, and remains subject to normal locking and logging, so concurrent access continues within those rules. Reclaiming space typically requires a REORG, but the delete itself does not trigger one.

Question 23Db2 13 for z/OS Database Administration

A DBA is evaluating whether to use a universal table space with partition-by-range versus partition-by-growth. Which statement correctly distinguishes the two in Db2 13 for z/OS?

  1. Partition-by-growth requires a LIMITKEY for each partition, while partition-by-range uses MAXPARTITIONS.
  2. Partition-by-range requires a LIMITKEY for each partition, while partition-by-growth uses MAXPARTITIONS and adds partitions automatically. Correct answer.
  3. Both require explicit LIMITKEY values and differ only in the number of partitions allowed.
  4. Partition-by-range cannot be used with a universal table space.

Explanation

Partition-by-range table spaces define each partition with a LIMITKEY, while partition-by-growth table spaces use MAXPARTITIONS and let Db2 add partitions as data grows. Both are forms of universal table spaces.

Question 24Db2 13 for z/OS Database Administration

A DBA is tuning a query that performs a large sort and wants to reduce the chance that the sort spills to work files. Which Db2 for z/OS resource should be reviewed first?

  1. The EDM pool size
  2. The sort pool size (SRTPOOL) and the size of the work file database Correct answer.
  3. The RID pool size
  4. The group buffer pool size

Explanation

The sort pool provides virtual storage for sort work, and the work file database provides DASD space for sorts that exceed the pool. Insufficient size in either area causes sort spills and performance degradation.

Question 25Db2 13 for z/OS Database Administration

A DBA needs to recover a single partition of a partitioned table space to a point in time without affecting other partitions. Which Db2 for z/OS capability enables this?

  1. REBUILD INDEX for all partitions
  2. RECOVER TABLESPACE with the PART or PARTLEVEL option for the specific partition Correct answer.
  3. RECOVER INDOUBT for the table space
  4. COPY with SHRLEVEL CHANGE for the entire table space

Explanation

RECOVER TABLESPACE with the PART or PARTLEVEL option restores only the specified partition, leaving other partitions available. This is a key benefit of partition-level recovery for large partitioned objects.

Question 26Db2 13 for z/OS Database Administration

A DBA needs to create a table space that can grow automatically as data is added, without predefined partitions, and must support a table with a LOB column. Which Db2 13 for z/OS table space type should be used?

  1. Simple table space with a primary space allocation
  2. Segmented table space with a maximum of 64 segments
  3. Classic partitioned table space with a clustering index
  4. Partition-by-growth universal table space (UTS) with DSSIZE specified Correct answer.

Explanation

Partition-by-growth universal table spaces automatically add partitions as data grows and support LOB columns, which classic segmented or simple table spaces cannot. A DSSIZE must be specified to control partition size. Classic partitioned table spaces require predefined partitions and are not ideal for automatic growth.

Question 27Db2 13 for z/OS Database Administration

A DBA wants to ensure that a table space is automatically reorganized when certain thresholds are exceeded. Which Db2 13 for z/OS feature provides this capability?

  1. Real-time statistics (RTS)
  2. STOSPACE
  3. Automatic reorganization (auto REORG) Correct answer.
  4. Online REORG with SHReLEVEL

Explanation

Automatic reorganization uses real-time statistics to trigger a REORG when thresholds like REORGCHK or space growth are met. STOSPACE collects space statistics but does not trigger reorganization. RTS provides statistics but not automatic actions.

Question 28Db2 13 for z/OS Database Administration

A DBA is creating a table and wants to define a column that automatically generates unique values for each row without using an identity column. Which Db2 13 for z/OS feature should be used?

  1. A ROWID column with a generated value
  2. A trigger that populates the column from a timestamp
  3. A sequence object and a DEFAULT clause referencing NEXTVAL Correct answer.
  4. A column with the AS IDENTITY attribute

Explanation

A sequence object generates unique numeric values, and a column can use DEFAULT NEXTVAL FOR sequence to automatically populate values on insert. ROWID is for internal row identifiers, and AS IDENTITY is an identity column, which the question excludes.

Question 29Db2 13 for z/OS Database Administration

A DBA needs to grant a user the authority to create and drop tables in a specific database. Which Db2 13 for z/OS privilege should be granted?

  1. DBADM on the database Correct answer.
  2. CREATETAB on the database
  3. SYSADM
  4. CREATEIN on the schema

Explanation

DBADM authority on a database includes the ability to create and drop tables within that database. CREATETAB only allows creating tables, not dropping them. SYSADM is a system-wide authority that is too broad.

Question 30Db2 13 for z/OS Database Administration

A DBA wants to capture information about SQL statements that are executed dynamically, including statement text and performance metrics, for later analysis. Which Db2 13 for z/OS facility should be used?

  1. Db2 traces Correct answer.
  2. Dynamic statement cache
  3. IFCID 0225
  4. Accounting trace

Explanation

Db2 traces, such as performance trace (IFCID 0225) or accounting trace, can capture dynamic SQL statement text and metrics. The dynamic statement cache stores prepared statements but does not provide a historical record. Accounting trace provides resource usage but not necessarily statement text.

Question 31Db2 13 for z/OS Database Administration

A DBA needs to load data into a table and wants to ensure that the load utility does not invalidate dependent packages. Which LOAD utility option should be specified?

  1. ENFORCE CONSTRAINTS
  2. LOG YES Correct answer.
  3. REPLACE
  4. SHRLEVEL CHANGE

Explanation

Specifying LOG YES for the LOAD utility ensures that the load operation is logged, which allows dependent packages to remain valid and enables recovery. SHRLEVEL CHANGE allows concurrent access but does not directly address package invalidation. ENFORCE CONSTRAINTS checks constraints but does not affect package validity.

Question 32Db2 13 for z/OS Database Administration

A DBA is defining a foreign key constraint and wants to ensure that when a parent row is deleted, the corresponding child rows are also deleted. Which ON DELETE action should be specified?

  1. SET NULL
  2. RESTRICT
  3. CASCADE Correct answer.
  4. NO ACTION

Explanation

ON DELETE CASCADE deletes child rows when the parent row is deleted. RESTRICT prevents deletion if child rows exist. SET NULL sets the foreign key to null, and NO ACTION is similar to RESTRICT but checked at end of statement.

Question 33Db2 13 for z/OS Database Administration

A DBA needs to create an index that allows efficient access for queries that use a LIKE predicate with a wildcard at the end, such as 'ABC%'. Which index type should be used?

  1. A spatial index
  2. A hash index
  3. A standard B-tree index on the column Correct answer.
  4. A clustered index

Explanation

A standard B-tree index can be used for prefix matching in LIKE predicates when the pattern has a fixed prefix. Hash indexes are not supported in Db2 for z/OS. Spatial indexes are for geospatial data, and clustered indexes are a property of the table, not a separate index type.

Question 34Db2 13 for z/OS Database Administration

A DBA is creating a table space and wants to ensure that the table space is automatically recovered after a failure using the Db2 restart process. Which option must be specified?

  1. LOGGED Correct answer.
  2. MEMBER CLUSTER
  3. NOT LOGGED
  4. COMPRESS YES

Explanation

A table space defined with LOGGED (the default) has changes logged, allowing Db2 to perform restart recovery and rollback. NOT LOGGED table spaces cannot be recovered to a point in time and are not eligible for restart recovery. COMPRESS and MEMBER CLUSTER are unrelated to logging.

Question 35Db2 13 for z/OS Database Administration

A DBA needs to create a materialized query table (MQT) that is automatically refreshed when the underlying tables are updated. Which option should be specified in the CREATE TABLE statement?

  1. REFRESH IMMEDIATE Correct answer.
  2. MAINTAINED BY SYSTEM
  3. REFRESH DEFERRED
  4. ENABLE QUERY OPTIMIZATION

Explanation

REFRESH IMMEDIATE means the MQT is refreshed automatically as part of the transaction that changes the underlying tables. REFRESH DEFERRED requires manual refresh. ENABLE QUERY OPTIMIZATION allows the optimizer to use the MQT but does not control refresh.

Question 36Db2 13 for z/OS Database Administration

A DBA wants to partition a table by range on a date column and also ensure that each partition is limited to a specific size. Which Db2 13 for z/OS table space type should be used?

  1. Partition-by-range universal table space with DSSIZE Correct answer.
  2. Segmented table space with a large page size
  3. Partition-by-growth universal table space with MAXPARTITIONS
  4. Classic partitioned table space with a clustering index

Explanation

A partition-by-range universal table space allows explicit range partitioning and supports DSSIZE to limit partition size. Partition-by-growth automatically adds partitions but does not allow range specification. Classic partitioned table spaces are older and do not support UTS features like DSSIZE.

Question 37Db2 13 for z/OS Database Administration

A DBA needs to create a stored procedure that returns a result set to the caller. Which SQL statement must be included in the procedure body?

  1. FETCH FROM CURSOR
  2. DECLARE CURSOR WITH RETURN Correct answer.
  3. RETURN SELECT
  4. OPEN CURSOR

Explanation

To return a result set from a stored procedure, the procedure must declare a cursor WITH RETURN and then open it. The OPEN statement alone does not return a result set. FETCH is used to retrieve rows into variables, and RETURN SELECT is not valid SQL.

Question 38Db2 13 for z/OS Database Administration

A DBA is using the REORG utility on a partitioned table space and wants to reorganize only the partitions that have changed. Which REORG option should be used?

  1. SHRLEVEL REFERENCE
  2. REBALANCE
  3. SHRLEVEL CHANGE
  4. SCOPE PENDING Correct answer.

Explanation

The SCOPE PENDING option limits reorganization to partitions that are in a pending state, such as those with REORG-pending status. SHRLEVEL options control access during reorganization, not which partitions are processed. REBALANCE is for index reorganization.

Question 39Db2 13 for z/OS Database Administration

A DBA needs to create a trigger that fires after an update on a table and inserts a row into an audit table. Which trigger type should be used?

  1. AFTER trigger Correct answer.
  2. FOR EACH STATEMENT trigger
  3. BEFORE trigger
  4. INSTEAD OF trigger

Explanation

An AFTER trigger fires after the triggering event and is appropriate for audit logging because the original change has completed. BEFORE triggers fire before the event and are used to validate or modify data. INSTEAD OF triggers are for views.

Question 40Db2 13 for z/OS Database Administration

A DBA is using the UNLOAD utility to extract data from a table and wants to ensure that the data is consistent while the table remains online for updates. Which UNLOAD option should be specified?

  1. CONSISTENT YES
  2. SHRLEVEL CHANGE
  3. SHRLEVEL REFERENCE Correct answer.
  4. ISOLATION UR

Explanation

SHRLEVEL REFERENCE allows the UNLOAD to read data while the table is online for updates, providing a consistent view. SHRLEVEL CHANGE is not a valid UNLOAD option. ISOLATION UR allows uncommitted reads but does not guarantee consistency.

Question 41Db2 13 for z/OS Database Administration

A DBA needs to create a view that includes a column derived from an expression and wants to ensure that the view can be used for query rewrite. Which option must be specified when creating the view?

  1. ENABLE QUERY OPTIMIZATION Correct answer.
  2. WITH CHECK OPTION
  3. CREATE VIEW ... AS SELECT ...
  4. WITH CASCADED CHECK OPTION

Explanation

ENABLE QUERY OPTIMIZATION allows the Db2 optimizer to use the view for query rewrite, including views with derived columns. WITH CHECK OPTION is for data validation on insert/update through the view, not for query rewrite.

Question 42Db2 13 for z/OS Database Administration

A DBA is using the Db2 13 for z/OS RESTORE utility to recover a table space and wants to apply log records to roll forward to a point in time. Which RESTORE option must be specified?

  1. ROLLFORWARD Correct answer.
  2. RESTART
  3. LOGONLY
  4. RECOVER

Explanation

The ROLLFORWARD option of the RESTORE utility indicates that the table space will be rolled forward using log records after the restore. LOGONLY is not a RESTORE option; RECOVER is a separate utility. RESTART is for restarting Db2.

Question 43Db2 13 for z/OS Database Administration

A DBA needs to create a table that uses hash-organized storage for faster access by a key column. Which Db2 13 for z/OS feature should be used?

  1. Partition-by-growth table space
  2. Hash-organized table (HOT) Correct answer.
  3. Clustered index
  4. Materialized query table

Explanation

Hash-organized tables (HOT) store rows based on a hash of the key, providing fast access for equality lookups. Partition-by-growth is for automatic partitioning, MQT is for precomputed results, and clustered indexes determine row order but not hash storage.

Question 44Db2 13 for z/OS Database Administration

A DBA is using the Db2 13 for z/OS EXPLAIN facility and wants to capture the access path for a static SQL statement at bind time. Which EXPLAIN option should be used?

  1. EXPLAIN(ALL) in the BIND PLAN statement
  2. EXPLAIN(NO) in the BIND PACKAGE statement
  3. EXPLAIN(YES) in the BIND PLAN statement Correct answer.
  4. EXPLAIN(ONLY) in the BIND PACKAGE statement

Explanation

EXPLAIN(YES) in the BIND PLAN or BIND PACKAGE statement captures access path information for static SQL into the plan table. EXPLAIN(ONLY) is used for dynamic statements. EXPLAIN(ALL) is not a valid option.

Question 45Db2 13 for z/OS Database Administration

A DBA needs to create a table space that uses compression to reduce storage and improve I/O performance. Which Db2 13 for z/OS feature should be specified?

  1. COMPRESS YES Correct answer.
  2. AUDIT YES
  3. TRACKMOD YES
  4. COMPRESS NO

Explanation

COMPRESS YES enables data compression for the table space, reducing storage and potentially improving I/O. COMPRESS NO disables compression. AUDIT and TRACKMOD control auditing and modification tracking, not compression.

Question 46Db2 13 for z/OS Database Administration

A DBA is creating a table with a column that must store large text documents and be searched using full-text search. Which Db2 13 for z/OS feature should be used?

  1. XML column with a full-text index
  2. CLOB column with a full-text index Correct answer.
  3. VARCHAR column with a full-text index
  4. BLOB column with a standard index

Explanation

CLOB columns are designed for large character data and can be indexed with a full-text index for searching. BLOB is for binary data and cannot use full-text search. VARCHAR has a limited length and is not suitable for large documents.

Question 47Db2 13 for z/OS Database Administration

A DBA is using the Db2 13 for z/OS COPY utility and wants to create an image copy that can be used for point-in-time recovery. Which COPY option should be specified?

  1. SHRLEVEL CHANGE
  2. SHRLEVEL REFERENCE Correct answer.
  3. FULL YES
  4. LOG YES

Explanation

SHRLEVEL REFERENCE creates a consistent image copy while allowing read access, suitable for point-in-time recovery. SHRLEVEL CHANGE allows updates during the copy but may not be consistent. FULL YES is not a valid COPY option.

Question 48Db2 13 for z/OS Database Administration

A DBA is designing a database and wants to ensure that a column cannot contain null values and that each value is unique across the table. Which constraint should be defined?

  1. FOREIGN KEY
  2. CHECK
  3. PRIMARY KEY Correct answer.
  4. UNIQUE

Explanation

A PRIMARY KEY constraint enforces both uniqueness and non-null values. A UNIQUE constraint enforces uniqueness but allows nulls (unless the column is defined as NOT NULL). FOREIGN KEY enforces referential integrity, and CHECK enforces a condition.

Question 49Db2 13 for z/OS Database Administration

A DBA is analyzing a query that performs a sort and wants to avoid sort spill to work files. Which Db2 13 for z/OS parameter should be tuned?

  1. MAXKEEPD
  2. EDMSTMTC
  3. DSMAX
  4. SORTL Correct answer.

Explanation

SORTL specifies the maximum amount of storage for a sort, and increasing it can reduce spill to work files. DSMAX controls the maximum number of data sets, MAXKEEPD controls kept dynamic statements, and EDMSTMTC controls statement cache size.

Question 50Db2 13 for z/OS Database Administration

A DBA needs to create a table that is partitioned by range and also has a secondary index that is partitioned differently to optimize queries. Which Db2 13 for z/OS feature allows this?

  1. Partitioned secondary index (PSPI) Correct answer.
  2. Non-partitioned secondary index (NPSI)
  3. Hash-organized table
  4. Clustered index

Explanation

A partitioned secondary index (PSPI) allows an index to be partitioned independently of the table space, which can improve query performance for certain access patterns. NPSI is a non-partitioned index that spans all partitions. Clustered and hash-organized are not index partitioning schemes.