I85PREP
⌘ K
Db2 12 z/OS DBA study materials
Db2 12 z/OS DBA study resources

Db2 12 z/OS DBA practice questions

One question at a time. Build your Db2 12 z/OS DBA 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 Architecture, Subsystems, and Installation/Migration

In a Db2 12 for z/OS subsystem, which component is responsible for managing the buffer pools and coordinating the writing of changed pages to disk?

  1. The Database Services address space (DBM1) Correct answer.
  2. The Distributed Data Facility (DDF) address space
  3. The IRLM address space
  4. The subsystem support address space (SSNM)

Explanation

DBM1 (Database Services) hosts buffer pool management, the log writer's page-flush coordination, and the bulk of database-dependent processing. DDF handles remote requests, IRLM handles locking, and SSNM handles subsystem services such as commands and start/stop. Buffer pool control blocks and the write engine live in DBM1.

Question 2Db2 Architecture, Subsystems, and Installation/Migration

A Db2 12 subsystem is being installed with the default settings. Which data set is used to store the Db2 bootstrap data set (BSDS) records that describe active and archive logs?

  1. A pair of VSAM KSDS data sets defined with a two-record BSDS structure Correct answer.
  2. A single sequential data set written by the log writer
  3. A VSAM linear data set allocated by the installation CLIST
  4. A partitioned data set with one member per log copy

Explanation

The BSDS is a VSAM key-sequenced data set, and Db2 requires a dual BSDS (two KSDS data sets) for recovery. It records log inventory and checkpoint information, not the log records themselves. A single BSDS is not permitted because losing it prevents restart.

Question 3Db2 Architecture, Subsystems, and Installation/Migration

During a migration from Db2 11 to Db2 12 for z/OS, which step must be completed before the new function mode can be enabled?

  1. All application packages must be rebound with REOPT(ALWAYS).
  2. The subsystem must be started in conversion mode and the catalog migration completed. Correct answer.
  3. The catalog must be reorganized with REORG SHRLEVEL REFERENCE.
  4. All table spaces must be placed in read-only status.

Explanation

Migration proceeds through enable-new-function mode only after the subsystem has been started in conversion mode and catalog migration has completed successfully. Conversion mode lets the new release access existing data while catalog structures are updated. Rebinding packages and reorganizing the catalog are not prerequisites for enabling new function.

Question 4Db2 Architecture, Subsystems, and Installation/Migration

A Db2 12 subsystem is defined with MEMBER and GROUP names in the data sharing group. Which statement correctly describes how the IRLM is configured for data sharing?

  1. A single IRLM serves all members of the data sharing group across all LPARs.
  2. The IRLM is optional in a data sharing group because locking is handled by DDF.
  3. Each member has its own IRLM, and all IRLMs in the group communicate to provide global locking. Correct answer.
  4. The IRLM must run in the same address space as DBM1 to provide global locking.

Explanation

In data sharing, each member has its own IRLM, and the IRLMs communicate with each other to coordinate global locks across the group. The IRLM is a separate address space, not part of DBM1, and it is mandatory for data sharing. DDF does not provide locking.

Question 5Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 catalog table contains one row for each table space in the subsystem, including its type and status?

  1. SYSIBM.SYSSTOGROUP
  2. SYSIBM.SYSTABLES
  3. SYSIBM.SYSTABLESPACE Correct answer.
  4. SYSIBM.SYSINDEXES

Explanation

SYSIBM.SYSTABLESPACE holds one row per table space and records attributes such as type, status, and associated storage group. SYSTABLES describes tables, SYSINDEXES describes indexes, and SYSSTOGROUP describes storage groups. Only SYSTABLESPACE is organized around table spaces.

Question 6Db2 Architecture, Subsystems, and Installation/Migration

An installation plans to use a Db2 12 subsystem with a 10-byte log record sequence number (LRSN) for data sharing. Which statement about the LRSN and RBA is correct?

  1. The RBA and LRSN are interchangeable in all configurations and differ only in length.
  2. A data sharing member uses the LRSN, which is derived from the store clock value, while a non-data-sharing subsystem uses the RBA. Correct answer.
  3. A data sharing member uses the RBA as its log address, and a non-data-sharing subsystem uses the LRSN.
  4. The RBA is used only when the BSDS is dual, and the LRSN is used otherwise.

Explanation

In data sharing, a common log address is needed across members, so Db2 uses the LRSN derived from the store clock. A non-data-sharing subsystem uses the relative byte address (RBA) within its own log. The two are not interchangeable, and the BSDS configuration does not determine which is used.

Question 7Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 utility is used to collect statistics for the Db2 catalog so that the optimizer has current information?

  1. COPY
  2. CHECK
  3. REORG
  4. RUNSTATS Correct answer.

Explanation

RUNSTATS gathers statistics on tables, indexes, and columns and records them in the Db2 catalog for the optimizer. REORG reorganizes data, COPY makes image copies, and CHECK verifies data and index consistency. Only RUNSTATS updates optimizer statistics.

Question 8Db2 Architecture, Subsystems, and Installation/Migration

A Db2 12 subsystem is started, and the DBA wants to verify that the subsystem is in new-function mode. Which command output confirms the current function level?

  1. DISPLAY DATABASE output
  2. DISPLAY BUFFERPOOL output
  3. DISPLAY GROUP output Correct answer.
  4. DISPLAY THREAD output

Explanation

DISPLAY GROUP reports the data sharing group status and the function level of each member, including whether it is in new-function mode. DISPLAY DATABASE shows database status, DISPLAY THREAD shows active threads, and DISPLAY BUFFERPOOL shows buffer pool activity. Only DISPLAY GROUP reports the function level.

Question 9Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 component is responsible for processing SQL statements received from remote DRDA clients?

  1. The DBM1 address space
  2. The IRLM address space
  3. The subsystem support address space (SSNM)
  4. The Distributed Data Facility (DDF) Correct answer.

Explanation

DDF handles DRDA and remote client requests, including SQL received over TCP/IP. DBM1 handles local database processing, SSNM handles subsystem services, and IRLM handles locking. Remote SQL processing is specifically a DDF function.

Question 10Db2 Architecture, Subsystems, and Installation/Migration

A DBA is planning a Db2 12 installation and needs to choose an initial data set allocation for the catalog and directory. Which statement about the catalog and directory is correct?

  1. The catalog and directory can be placed in user-managed table spaces, and the installation CLIST creates them by default. Correct answer.
  2. The catalog and directory cannot be migrated and must be recreated at each release.
  3. The catalog and directory must be placed in separate Db2 subsystems.
  4. The catalog and directory are always stored in a single table space named DSNDB06.

Explanation

The installation CLIST creates the catalog and directory in user-managed table spaces by default, and they reside in the DSNDB06 database. They are not required to be in separate subsystems, and they are migrated rather than recreated at each release. The catalog and directory consist of multiple table spaces, not a single one.

Question 11Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 subsystem parameter controls the maximum number of concurrent allied threads that can be active in the subsystem?

  1. CTHREAD Correct answer.
  2. IDBACK
  3. EDMSTMTC
  4. MAXDBAT

Explanation

CTHREAD sets the maximum number of concurrent allied threads, while MAXDBAT limits concurrent database access threads for remote clients. IDBACK controls the number of batch identifier entries, and EDMSTMTC controls statement cache size. Only CTHREAD governs allied thread concurrency.

Question 12Db2 Architecture, Subsystems, and Installation/Migration

A Db2 12 subsystem uses a data sharing group. Which statement about the lock structure in the coupling facility is correct?

  1. The lock structure replaces the BSDS for log management.
  2. The lock structure is optional and used only for performance tuning.
  3. The lock structure holds global lock information used by the IRLMs of all members. Correct answer.
  4. The lock structure stores all table data for the group.

Explanation

In data sharing, the coupling facility lock structure holds global lock information that the IRLMs of all members use to coordinate access. It does not store table data, does not replace the BSDS, and is required for data sharing. Lock structures and data structures are distinct coupling facility objects.

Question 13Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 utility is used to create a consistent copy of a table space for recovery purposes?

  1. MODIFY
  2. COPY Correct answer.
  3. RUNSTATS
  4. REORG

Explanation

COPY creates image copies of table spaces and indexes that can be used for recovery. REORG reorganizes data, RUNSTATS gathers statistics, and MODIFY updates recovery information such as the advisory REORG-pending status. Only COPY produces the recovery copies.

Question 14Db2 Architecture, Subsystems, and Installation/Migration

A DBA needs to add a new member to an existing Db2 12 data sharing group. Which step is required?

  1. Recreate the BSDS for every existing member.
  2. Convert all existing members to non-data-sharing mode.
  3. Define the new member with the installation CLIST and start it with its own IRLM. Correct answer.
  4. Recreate the entire data sharing group from scratch.

Explanation

Adding a member involves defining the new subsystem with the installation CLIST and starting it with its own IRLM so it can join the group. The existing group is not recreated, existing members remain in data sharing mode, and their BSDSs are not recreated. The new member joins the existing group.

Question 15Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 catalog table records the authorization IDs and their associated privileges on database objects?

  1. SYSIBM.SYSROUTINES
  2. SYSIBM.SYSUSERAUTH Correct answer.
  3. SYSIBM.SYSTABAUTH
  4. SYSIBM.SYSCOLAUTH

Explanation

SYSIBM.SYSUSERAUTH records authorization IDs and their system-level privileges, while SYSTABAUTH records table privileges and SYSCOLAUTH records column privileges. SYSROUTINES describes routines. The question asks about authorization IDs and their privileges, which is SYSUSERAUTH.

Question 16Db2 Architecture, Subsystems, and Installation/Migration

A Db2 12 subsystem is running in conversion mode after migration. Which statement best describes the effect of conversion mode on new Db2 12 features?

  1. All new Db2 12 features become available immediately.
  2. The subsystem cannot access existing data until new-function mode is enabled.
  3. New Db2 12 features are not available, but the subsystem can access existing data. Correct answer.
  4. The subsystem reverts to Db2 11 behavior permanently.

Explanation

Conversion mode allows the migrated subsystem to access existing data while new Db2 12 features remain unavailable. New features become available only after enabling new-function mode. Existing data remains accessible, and conversion mode is a temporary migration phase, not a permanent reversion.

Question 17Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 address space is responsible for handling Db2 commands and subsystem start/stop processing?

  1. IRLM
  2. DBM1
  3. SSNM Correct answer.
  4. DDF

Explanation

The subsystem support address space (SSNM) handles command processing and start/stop coordination. DBM1 handles database services, DDF handles remote requests, and IRLM handles locking. Command processing is an SSNM responsibility.

Question 18Db2 Architecture, Subsystems, and Installation/Migration

A DBA is reviewing the Db2 12 subsystem parameters and wants to limit the size of the environment descriptor manager (EDM) pool. Which parameter governs the EDM pool size?

  1. EDMDBDC
  2. EDMSKPT
  3. EDMPOOL Correct answer.
  4. EDMSTMTC

Explanation

EDMPOOL controls the size of the EDM pool, which holds descriptors for database objects and statements. EDMDBDC limits database descriptor blocks, EDMSTMTC limits statement cache size, and EDMSKPT controls skeleton package table entries. Only EDMPOOL sets the overall EDM pool size.

Question 19Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 utility is used to verify the consistency of a table space and its indexes?

  1. REORG
  2. CHECK Correct answer.
  3. COPY
  4. LOAD

Explanation

CHECK verifies data and index consistency and can report inconsistencies such as mismatched index entries. COPY makes image copies, REORG reorganizes data, and LOAD populates tables. Only CHECK performs consistency verification.

Question 20Db2 Architecture, Subsystems, and Installation/Migration

A DBA wants to enable data sharing for an existing Db2 12 subsystem. Which statement about the required setup is correct?

  1. Data sharing requires all table spaces to be converted to universal table spaces.
  2. The subsystem must be defined with a data sharing group name and member name, and coupling facility structures must be allocated. Correct answer.
  3. Data sharing can be enabled without a coupling facility.
  4. Data sharing requires the IRLM to be disabled.

Explanation

Enabling data sharing requires defining the subsystem with a group name and member name and allocating the required coupling facility structures, including lock and data structures. A coupling facility is mandatory, the IRLM is required, and universal table spaces are not a data sharing prerequisite.

Question 21Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 catalog table contains the definition of storage groups, including the volumes assigned to them?

  1. SYSIBM.SYSVOLUMES
  2. SYSIBM.SYSTABLESPACE
  3. SYSIBM.SYSSTOGROUP Correct answer.
  4. SYSIBM.SYSINDEXES

Explanation

SYSIBM.SYSSTOGROUP contains storage group definitions, including attributes such as the volumes assigned. SYSTABLESPACE describes table spaces, SYSVOLUMES records volume information used by storage groups, and SYSINDEXES describes indexes. The storage group definition itself is in SYSSTOGROUP.

Question 22Db2 Architecture, Subsystems, and Installation/Migration

A Db2 12 subsystem is being migrated and the DBA needs to verify that the catalog migration completed successfully. Which statement about the catalog migration is correct?

  1. Catalog migration is performed automatically when the subsystem is started in conversion mode. Correct answer.
  2. Catalog migration must be performed manually with a utility after the subsystem is started.
  3. Catalog migration can be skipped if the catalog was recently reorganized.
  4. Catalog migration is performed only when new-function mode is enabled.

Explanation

When the subsystem is started in conversion mode during migration, catalog migration is performed as part of that startup process. It is not a manual utility step, cannot be skipped based on a recent REORG, and is not deferred to new-function mode. The catalog must be migrated before new function can be enabled.

Question 23Db2 Architecture, Subsystems, and Installation/Migration

Which Db2 12 subsystem parameter controls the maximum number of concurrent database access threads for remote clients?

  1. IDBACK
  2. CTHREAD
  3. MAXDBAT Correct answer.
  4. NUMLKTS

Explanation

MAXDBAT limits the number of concurrent database access threads used by remote clients. CTHREAD limits allied threads, IDBACK controls batch identifier entries, and NUMLKTS controls the number of locks per table space. Only MAXDBAT governs remote database access thread concurrency.

Question 24Db2 Architecture, Subsystems, and Installation/Migration

A DBA is reviewing the Db2 12 subsystem and wants to confirm that the BSDS is dual. Which statement about the dual BSDS is correct?

  1. The two BSDS data sets must reside on the same volume.
  2. The two BSDS data sets record identical information and provide redundancy for recovery. Correct answer.
  3. The dual BSDS is optional and used only for data sharing.
  4. Only one BSDS is used at a time, and the other is a standby that is never updated.

Explanation

The dual BSDS data sets record identical information and provide redundancy so that a failure of one does not prevent recovery. They should reside on different volumes for protection, both are updated, and the dual BSDS is required for all subsystems, not only data sharing.

Question 25Database Design, Objects, and Schema Management

In Db2 12 for z/OS, which object must exist before a table can be created in a table space that uses segmented organization?

  1. A storage group
  2. An index space
  3. A LOB table space
  4. A database Correct answer.

Explanation

Every Db2 table space is owned by a database, so the database must be created first and the table space defined within it. The storage group is optional and only needed when you want explicit volume placement, and index spaces are separate objects created for indexes.

Question 26Database Design, Objects, and Schema Management

A Db2 12 universal table space is created with SEGSIZE 0. Which physical organization does it use?

  1. Segmented pages with a fixed segment size
  2. Hash-organized pages
  3. Partition-by-range with user-defined limit keys
  4. Partition-by-growth with DSSIZE-controlled partitions Correct answer.

Explanation

In Db2 12, SEGSIZE 0 on a universal table space requests partition-by-growth organization, where Db2 adds partitions automatically as data grows up to the DSSIZE limit. Segmented organization requires a nonzero SEGSIZE, and partition-by-range requires explicit limit keys.

Question 27Database Design, Objects, and Schema Management

You create a table with an identity column GENERATED BY DEFAULT and load rows that already contain values for that column. What happens during the load?

  1. Db2 rejects every supplied value and generates new ones
  2. Db2 accepts the supplied values without generating new ones Correct answer.
  3. Db2 accepts the supplied values only if the column is also defined as a primary key
  4. Db2 accepts the supplied values and resets the internal counter to the maximum loaded value

Explanation

GENERATED BY DEFAULT permits user-supplied values for the identity column, so the values in the input data are stored as-is. GENERATED ALWAYS would reject them unless the load uses an overriding clause, and the identity counter is not automatically advanced to match loaded values.

Question 28Database Design, Objects, and Schema Management

Which Db2 12 catalog table contains one row for each column of every table and view in the subsystem?

  1. SYSIBM.SYSSTOGROUP
  2. SYSIBM.SYSINDEXES
  3. SYSIBM.SYSTABLES
  4. SYSIBM.SYSCOLUMNS Correct answer.

Explanation

SYSIBM.SYSCOLUMNS holds descriptive information for every column of every table, view, and alias in the Db2 subsystem. SYSTABLES describes table-level attributes, SYSINDEXES describes indexes, and SYSSTOGROUP describes storage groups.

Question 29Database Design, Objects, and Schema Management

A table space is defined with COMPRESS YES STATIC. Which statement describes the effect?

  1. Rows are compressed using a static dictionary built at REORG or LOAD time Correct answer.
  2. Compression applies only after the table space is placed in advisory REORG-pending status
  3. Data is compressed page-by-page using a dictionary built dynamically at insert time
  4. Only LOB columns are compressed

Explanation

STATIC compression uses a dictionary created during LOAD or REORG, so rows are compressed against that fixed dictionary. Dynamic compression builds dictionaries in memory as data is inserted and does not require a REORG to take effect.

Question 30Database Design, Objects, and Schema Management

Which clause in a CREATE INDEX statement enables index key compression in Db2 12?

  1. USING STOGROUP
  2. COMPRESS YES Correct answer.
  3. DEFINE YES
  4. CLUSTER

Explanation

COMPRESS YES on CREATE INDEX enables index key compression, which removes repeated leading key values from index entries. USING STOGROUP assigns storage, CLUSTER controls clustering, and DEFINE YES controls index space definition timing.

Question 31Database Design, Objects, and Schema Management

What is the primary purpose of a partition-by-range universal table space?

  1. To store LOB data separately from base rows
  2. To hold only the indexes of a partitioned table
  3. To add partitions automatically as rows are inserted
  4. To distribute rows across partitions based on ranges of one or more key columns Correct answer.

Explanation

Partition-by-range table spaces use explicit limit keys to direct each row to a partition based on the value of the partitioning columns. Automatic growth describes partition-by-growth, LOB storage is handled by auxiliary table spaces, and indexes live in index spaces.

Question 32Database Design, Objects, and Schema Management

A DBA wants two tables to share the same physical table space without being related by referential integrity. Which Db2 object accomplishes this?

  1. A clone table
  2. A materialized query table
  3. A multi-table table space Correct answer.
  4. A segmented table space

Explanation

A multi-table table space stores more than one table in the same table space, which is allowed for segmented and partitioned organizations. A segmented table space describes page layout rather than the number of tables, and clone and materialized query tables serve entirely different purposes.

Question 33Database Design, Objects, and Schema Management

Which statement about a Db2 12 alias is correct?

  1. An alias can only be created for tables that have a clustering index
  2. An alias must be dropped before the underlying table can be altered
  3. An alias is an alternative name that resolves to a table or view at the time of reference Correct answer.
  4. An alias has its own storage and stores a copy of the data

Explanation

An alias is a catalog entry that points to a table or view and is resolved when the SQL statement is executed, so it holds no data itself. Aliases can be created for tables and views regardless of indexing, and they do not block alterations to the base object.

Question 34Database Design, Objects, and Schema Management

What does the AUDIT clause on a CREATE TABLE statement control?

  1. The compression mode used for the table space
  2. Whether the table participates in referential integrity
  3. The level of Db2 audit logging for access to the table Correct answer.
  4. The frequency of RUNSTATS collection

Explanation

The AUDIT clause specifies whether Db2 records audit information, such as successful reads and changes, for the table. RUNSTATS frequency, referential integrity, and compression are controlled by separate clauses or utilities.

Question 35Database Design, Objects, and Schema Management

A table is created with DATA CAPTURE CHANGES. What is the effect?

  1. Db2 writes additional information to the log for changes to the table Correct answer.
  2. Db2 forces all updates to use optimistic locking
  3. Db2 enables static compression for the table
  4. Db2 automatically creates a clone table

Explanation

DATA CAPTURE CHANGES causes Db2 to record extra log information, including before and after images, so that change-data-capture tools can read the changes. It does not create clone tables, enable compression, or alter locking behavior.

Question 36Database Design, Objects, and Schema Management

Which Db2 12 feature allows a table space to be reorganized online while applications continue to read and write the data?

  1. LOAD REPLACE with LOG NO
  2. Online REORG with SHReLEVEL CHANGE Correct answer.
  3. RUNSTATS with SHRLEVEL REFERENCE
  4. REBUILD INDEX with SORTKEYS

Explanation

Online REORG with SHReLEVEL CHANGE lets Db2 switch to a shadow copy so that applications retain read and write access during most of the reorganization. LOAD REPLACE restricts access, and REBUILD INDEX and RUNSTATS do not reorganize table space data.

Question 37Database Design, Objects, and Schema Management

A DBA creates a table with a column defined as TIMESTAMP WITH TIME ZONE. Which statement about the stored value is correct?

  1. The value stores the UTC offset along with the timestamp Correct answer.
  2. The value stores only the time zone name
  3. The value is converted to UTC and the offset is discarded
  4. The value can only be inserted using the CURRENT TIMESTAMP special register

Explanation

TIMESTAMP WITH TIME ZONE stores both the timestamp and its UTC offset, preserving the local time context of the value. Plain TIMESTAMP discards the offset, and the data type does not restrict inserts to a single special register.

Question 38Database Design, Objects, and Schema Management

Which statement about generated columns in Db2 12 is correct?

  1. A generated column cannot be indexed
  2. A generated column must be the first column of the table
  3. A generated column can only reference columns from another table
  4. A generated column can be defined as an expression over other columns of the same row Correct answer.

Explanation

A generated column is computed from an expression that references other columns in the same row, such as a concatenation or arithmetic expression. Its position in the table is arbitrary, it cannot reference other tables, and it can be indexed like any other column.

Question 39Database Design, Objects, and Schema Management

A DBA needs to enforce that every value of a column is unique and that nulls are not allowed. Which constraint accomplishes this?

  1. A unique constraint with a non-null check
  2. A check constraint
  3. A foreign key constraint
  4. A primary key constraint Correct answer.

Explanation

A primary key constraint enforces both uniqueness and non-null values for the key columns. A unique constraint allows one null, check constraints do not enforce uniqueness, and foreign keys enforce referential relationships rather than uniqueness.

Question 40Database Design, Objects, and Schema Management

Which Db2 12 catalog table should a DBA query to find the referential constraints defined on a table?

  1. SYSIBM.SYSPLAN
  2. SYSIBM.SYSCOLUMNS
  3. SYSIBM.SYSDATABASE
  4. SYSIBM.SYSRELS Correct answer.

Explanation

SYSIBM.SYSRELS contains one row for each referential constraint, including the parent and dependent table information. SYSCOLUMNS describes columns, SYSDATABASE describes databases, and SYSPLAN describes application plans.

Question 41Database Design, Objects, and Schema Management

A DBA wants a view to be read-only regardless of the privileges granted on it. Which CREATE VIEW option should be used?

  1. WITH ENCRYPTION
  2. WITH UR
  3. WITH RESTRICTIVE CHECK OPTION Correct answer.
  4. WITH CASCADED CHECK OPTION

Explanation

The WITH RESTRICTIVE CHECK OPTION on a view makes the view non-updatable, so it can only be used for read operations. The cascaded check option enforces predicate checking on updates rather than making the view read-only, and encryption and isolation clauses serve other purposes.

Question 42Database Design, Objects, and Schema Management

Which statement about a partitioned index in Db2 12 is correct?

  1. It cannot be used for partition elimination
  2. It can be partitioned even if the underlying table space is segmented
  3. It must have the same number of partitions as the table space it indexes Correct answer.
  4. It can only be created on a clustering index

Explanation

A partitioned index on a partitioned table space must have the same number of partitions as the table space, with each index partition corresponding to a data partition. Partitioned indexes are not limited to segmented table spaces or clustering indexes, and they support partition elimination.

Question 43Database Design, Objects, and Schema Management

What is the effect of specifying NOT VOLATILE on an ALTER TABLE statement in Db2 12?

  1. Db2 disables the use of indexes on the table
  2. Db2 prevents the table from being reorganized
  3. Db2 assumes the table has few rows and uses index access where possible Correct answer.
  4. Db2 assumes the table has many rows and uses a full table space scan

Explanation

NOT VOLATILE tells Db2 to assume the table has many rows, so access paths favor index access rather than scanning the entire table space. VOLATILE conveys the opposite assumption for small, heavily accessed tables.

Question 44Database Design, Objects, and Schema Management

A DBA needs to capture before and after images of changes for an external replication tool. Which table option supports this?

  1. DATA CAPTURE CHANGES Correct answer.
  2. AUDIT ALL
  3. VALIDPROC
  4. COMPRESS YES

Explanation

DATA CAPTURE CHANGES causes Db2 to write the additional log records needed by replication and change-capture products. AUDIT records access for security purposes, COMPRESS controls storage, and VALIDPROC validates input data.

Question 45Database Design, Objects, and Schema Management

Which Db2 12 object is created automatically when a table with a LOB column is defined?

  1. A clone table
  2. A LOB table space and an auxiliary table Correct answer.
  3. A materialized query table
  4. A history table

Explanation

When a table includes a LOB column, Db2 creates a LOB table space and an auxiliary table to hold the LOB data separately from the base row. Clone, materialized query, and history tables are created only when explicitly requested.

Question 46Database Design, Objects, and Schema Management

Which statement about temporal tables in Db2 12 is correct?

  1. A system-period temporal table cannot have indexes
  2. A system-period temporal table requires the application to insert history rows manually
  3. A system-period temporal table stores only the current version of each row
  4. A system-period temporal table automatically maintains a history table of prior row versions Correct answer.

Explanation

A system-period temporal table uses Db2-maintained system time and automatically moves prior row versions to an associated history table. Applications do not insert history rows manually, and indexes are fully supported on temporal tables.

Question 47Database Design, Objects, and Schema Management

A DBA wants to change the data type of a column from CHAR(10) to VARCHAR(20) without dropping and recreating the table. Which approach is supported in Db2 12?

  1. CREATE a new table and use INSERT to copy the rows
  2. DROP and recreate the table, then reload the data
  3. ALTER TABLE ALTER COLUMN with a compatible type change, followed by a REORG Correct answer.
  4. Use LOAD REPLACE to change the column definition

Explanation

Db2 12 supports certain ALTER COLUMN type changes, such as widening a character column, and may place the table space in advisory REORG-pending status until a REORG materializes the change. Dropping the table, creating a new table, and LOAD REPLACE do not alter column definitions.

Question 48Database Design, Objects, and Schema Management

Which Db2 12 catalog table stores information about the table spaces defined in the subsystem?

  1. SYSIBM.SYSTABLES
  2. SYSIBM.SYSINDEXSPACES
  3. SYSIBM.SYSVOLUMES
  4. SYSIBM.SYSTABLESPACE Correct answer.

Explanation

SYSIBM.SYSTABLESPACE contains one row for each table space and records attributes such as organization and partition information. SYSTABLES describes tables, SYSINDEXSPACES describes index spaces, and SYSVOLUMES records volume information.

Question 49Database Design, Objects, and Schema Management

What is the purpose of the MAXPARTITIONS value on a partition-by-growth table space?

  1. It controls the maximum size of each partition in megabytes
  2. It sets the maximum number of rows per partition
  3. It limits how many partitions Db2 can add as the table space grows Correct answer.
  4. It defines the limit keys for each partition

Explanation

MAXPARTITIONS caps the number of partitions a partition-by-growth table space can reach, and the DSSIZE value controls the size of each partition. Limit keys apply only to partition-by-range, and row counts per partition are not directly configured.

Question 50Database Design, Objects, and Schema Management

A DBA creates a table space using CREATE TABLESPACE with the SEGSIZE 8 clause. Which statement describes how Db2 12 allocates and uses space in this table space?

  1. Db2 allocates the table space in 8-page extents that any table in the table space can use as needed.
  2. Db2 assigns each table to one or more 8-page segments, and a table's rows can only occupy segments assigned to that table. Correct answer.
  3. Db2 reserves 8 percent of each page for segment directory entries and uses the remainder for row data.
  4. Db2 limits the table space to a maximum of 8 segments, after which additional tables are placed in a new table space.

Explanation

SEGSIZE defines a segment as a fixed number of 4 KB pages (or page-equivalent units for other page sizes), and in a segmented table space each table owns whole segments. Space within a segment is not shared with other tables, which reduces interleaving and improves mass delete and scan behavior.