Chapter 11 Quiz: Data Definition Language

Test your understanding of Chapter 11. Attempt all questions from memory before revealing the answers. The act of retrieval — even when you get it wrong — strengthens learning more than re-reading.

Scoring Guide: - 20-25 correct: Excellent — you have strong command of DDL concepts - 15-19 correct: Good — review the sections where you missed questions - 10-14 correct: Fair — re-read the chapter with the quiz questions in mind - Below 10: Re-read the chapter carefully, then retake this quiz in 48 hours

Multiple Choice Questions

Question 1

Which data type should you use to store monetary amounts in DB2?

A) REAL B) DOUBLE C) DECIMAL(p,s) D) FLOAT

Answer **C) DECIMAL(p,s)** Binary floating-point types (REAL, DOUBLE, FLOAT) introduce rounding errors when representing decimal fractions. DECIMAL stores exact decimal values, which is mandatory for financial data where auditors require penny-perfect accuracy.

Question 2

What is the maximum length of a schema name on DB2 for z/OS?

A) 8 characters B) 30 characters C) 64 characters D) 128 characters

Answer **A) 8 characters** z/OS schema names are limited to 8 characters, reflecting the traditional TSO authorization ID length. DB2 for LUW supports schema names up to 128 characters. This is an important cross-platform consideration.

Question 3

On DB2 for z/OS, what happens if you omit the IN clause from a CREATE TABLE statement?

A) The table is created in the default tablespace for the schema B) DB2 creates an implicit database and tablespace C) The statement fails with SQLCODE -204 D) The table is created in the DSNDB04 database

Answer **B) DB2 creates an implicit database and tablespace** While this works, it is universally discouraged in production. Implicit databases and tablespaces give the DBA no control over buffer pool assignment, space allocation, locking granularity, or compression — all of which are critical for performance and operations.

Question 4

Which ON DELETE rule blocks the deletion of a parent row if child rows exist?

A) CASCADE B) SET NULL C) RESTRICT D) NO ACTION

Answer **C) RESTRICT** RESTRICT prevents the deletion immediately. NO ACTION is similar but checks at the end of the statement. For practical purposes in most scenarios, both block the delete — but RESTRICT is the standard choice for core entity relationships where child rows must not become orphans.

Question 5

What is the difference between GENERATED ALWAYS and GENERATED BY DEFAULT for identity columns?

A) GENERATED ALWAYS allows explicit values; GENERATED BY DEFAULT does not B) GENERATED BY DEFAULT allows explicit values; GENERATED ALWAYS does not C) There is no difference — they are synonyms D) GENERATED ALWAYS uses caching; GENERATED BY DEFAULT does not

Answer **B) GENERATED BY DEFAULT allows explicit values; GENERATED ALWAYS does not** GENERATED ALWAYS rejects any INSERT that tries to provide a value for the identity column. GENERATED BY DEFAULT generates a value only if the INSERT does not specify one. Use GENERATED ALWAYS for new systems; GENERATED BY DEFAULT for data migration scenarios.

Question 6

Which tablespace type does IBM recommend for new development on DB2 for z/OS?

A) Simple tablespace B) Segmented tablespace C) Classic partitioned tablespace D) Universal Table Space (UTS)

Answer **D) Universal Table Space (UTS)** Universal Table Spaces — in both partition-by-range and partition-by-growth variants — are IBM's strategic direction. Legacy types (simple, segmented, classic partitioned) still function but should not be used for new development.

Question 7

On DB2 for LUW, what are the three tablespace management approaches?

A) DMS, SMS, Automatic Storage B) Simple, Segmented, Partitioned C) Manual, Automatic, Hybrid D) File, Device, System

Answer **A) DMS, SMS, Automatic Storage** Database-Managed Space (DMS) gives the DBA explicit control over data files. System-Managed Space (SMS) delegates to the OS. Automatic Storage lets DB2 manage file placement across defined storage paths — the recommended modern default.

Question 8

What is the purpose of a clustering index?

A) To compress the index for faster reads B) To enforce uniqueness across partitions C) To tell DB2 to attempt to store rows in the physical order of the index D) To create a covering index for all queries

Answer **C) To tell DB2 to attempt to store rows in the physical order of the index** A clustering index improves range scan performance by keeping rows physically ordered. Over time, INSERT operations degrade clustering, and REORG restores it. A table can have only one clustering index.

Question 9

What does the INCLUDE clause in a CREATE INDEX statement do?

A) Includes NULL values in the index B) Includes additional columns in the index leaf pages for index-only access C) Includes the index in the primary key constraint D) Includes partitions from other tablespaces

Answer **B) Includes additional columns in the index leaf pages for index-only access** INCLUDE columns are stored in the leaf pages but are not part of the index key. Queries that need only the key columns plus the INCLUDE columns can be satisfied entirely from the index, avoiding base table access.

Question 10

Which DB2 platform supports the BOOLEAN data type?

A) z/OS only B) LUW only C) Both z/OS and LUW D) Neither — BOOLEAN is not a DB2 type

Answer **B) LUW only** DB2 for LUW (11.1+) supports BOOLEAN. On z/OS, the conventional workaround is CHAR(1) with a CHECK constraint restricting values to 'Y'/'N' or 'T'/'F'.

Question 11

Why should you always explicitly name your constraints?

A) Named constraints are enforced more strictly than unnamed ones B) Named constraints execute faster C) Unnamed constraints get system-generated names that are meaningless in error messages and catalog queries D) Named constraints are required by the SQL standard

Answer **C) Unnamed constraints get system-generated names that are meaningless in error messages and catalog queries** A constraint named `SQL260316140230570` tells you nothing. A constraint named `fk_account_customer` immediately identifies the relationship. Named constraints make error messages, migration scripts, and catalog queries self-documenting.

Question 12

What is an informational constraint?

A) A constraint that logs violations instead of preventing them B) A constraint that is defined but not enforced, used as an optimizer hint C) A constraint that provides information to users through error messages D) A documentation-only comment attached to a column

Answer **B) A constraint that is defined but not enforced, used as an optimizer hint** Informational constraints (NOT ENFORCED, ENABLE QUERY OPTIMIZATION) let the optimizer use knowledge about data properties without the overhead of enforcement. The application is responsible for ensuring the constraint holds. If the application violates the constraint, queries may return incorrect results.

Question 13

On z/OS, what is the CHECK-pending (ACHKP) state?

A) A state indicating the CHECK utility needs to run to validate constraints B) A state indicating the table is being checked for corruption C) A state indicating a CHECK constraint has been violated D) A state indicating the tablespace is full

Answer **A) A state indicating the CHECK utility needs to run to validate constraints** When a CHECK or FOREIGN KEY constraint is added to a table that already contains data, z/OS places the table in CHECK-pending status. The CHECK DATA utility must be run to validate existing data against the new constraint.

Question 14

What happens when you DROP TABLE on z/OS?

A) The table, its data, its indexes, and its tablespace are all removed B) The table, its data, and its indexes are removed, but the tablespace persists C) Only the table definition is removed; data remains in the tablespace D) The DROP fails unless you specify CASCADE

Answer **B) The table, its data, and its indexes are removed, but the tablespace persists** On z/OS, tablespaces are separate objects that must be dropped independently. The DROP TABLE removes the table definition, all data, all indexes, and invalidates dependent plans and packages, but the tablespace remains.

Question 15

Why do gaps occur in identity column values?

A) Because DB2 has a bug in its identity implementation B) Because of transaction rollbacks, database restarts, and cache loss C) Because identity columns are designed to produce non-consecutive values D) Because of concurrent reads from the identity column

Answer **B) Because of transaction rollbacks, database restarts, and cache loss** When a transaction generates an identity value but then rolls back, the value is consumed but never committed. When the database shuts down, cached values are lost. These gaps are normal and expected — applications should not rely on gapless sequences.

Question 16

What is the z/OS storage hierarchy from top to bottom?

A) Database > Tablespace > Table > Index B) Storage Group > Database > Tablespace > Table C) Catalog > Storage Group > Tablespace > Index D) Volume > Database > Table > Column

Answer **B) Storage Group > Database > Tablespace > Table** Storage groups define physical DASD volumes. Databases are logical groupings. Tablespaces are VSAM data sets that contain tables. Indexes reside in separate index spaces within the database.

Question 17

Which parameter controls the default lock granularity for a tablespace on z/OS?

A) LOCKMODE B) LOCKSIZE C) LOCKLEVEL D) LOCKSCOPE

Answer **B) LOCKSIZE** LOCKSIZE can be set to ROW, PAGE, TABLE, or TABLESPACE. ROW is standard for OLTP workloads. PAGE is more efficient for batch processing. TABLE and TABLESPACE essentially serialize access.

Question 18

On LUW, what constraint exists on page sizes and buffer pools?

A) A buffer pool can serve any page size B) Each page size requires its own buffer pool C) Buffer pools are not related to page sizes D) Only 4K and 8K pages use buffer pools

Answer **B) Each page size requires its own buffer pool** A tablespace with 8K pages must use a buffer pool configured for 8K pages. This means you need at least one buffer pool for each page size in use, and at least one system temporary tablespace for each page size.

Question 19

What is the ROW CHANGE TIMESTAMP feature on z/OS?

A) A column that records when the CREATE TABLE was executed B) A column that automatically updates to the current timestamp whenever any column in the row is modified C) A system table that tracks all row changes D) A trigger-based audit mechanism

Answer **B) A column that automatically updates to the current timestamp whenever any column in the row is modified** ROW CHANGE TIMESTAMP is a z/OS feature that maintains the column automatically without trigger overhead. It is used for optimistic concurrency control and incremental data extraction. LUW achieves similar functionality through triggers or application logic.

Question 20

An alias in DB2 is:

A) A copy of a table with a different name B) An alternative name for a table, view, or another alias C) A read-only view of a table D) A temporary table that mirrors a permanent table

Answer **B) An alternative name for a table, view, or another alias** Aliases are resolved at execution time and always point to the current underlying object. They are useful for schema management across environments, distributed access simplification, and zero-downtime table replacement.

True/False Questions

Question 21

True or False: Adding a NOT NULL column with a DEFAULT value to a large table on z/OS requires an immediate REORG to materialize the default values in existing rows.

Answer **False** On z/OS (DB2 12+), adding a NOT NULL column with DEFAULT is a catalog-only operation. DB2 stores the default in the catalog and materializes it lazily on first access or during the next REORG. This makes ADD COLUMN an instant operation regardless of table size.

Question 22

True or False: A table can have multiple clustering indexes.

Answer **False** A table can have at most one clustering index. Data can only be physically ordered one way. Choose the clustering index based on the most common range-scan access pattern.

Question 23

True or False: The CACHE clause on a sequence trades potential gaps for improved INSERT throughput.

Answer **True** A larger cache means fewer disk writes to update the sequence counter, improving throughput. But if the database shuts down abnormally, the cached values are lost, creating gaps. This is an acceptable trade-off for nearly all OLTP workloads.

Short Answer Questions

Question 24

Explain in two or three sentences why you should always index foreign key columns.

Answer Without an index on the foreign key column in the child table, every DELETE or UPDATE of the primary key in the parent table requires a full table scan of the child table to check for referencing rows. For large child tables, this can cause severe performance problems and lock contention. Additionally, joins between parent and child tables on the foreign key benefit from the index for access path selection.

Question 25

A developer asks: "Why can't I just use VARCHAR(4000) for every string column? That way I never have to ALTER the column length." Give three specific reasons why this is a bad practice.

Answer 1. **Optimizer estimates**: DB2 uses the declared maximum length to estimate average column width for cardinality and cost calculations. An inflated VARCHAR maximum leads to inaccurate cost estimates, potentially causing the optimizer to choose suboptimal access paths. 2. **Memory allocation**: Client applications (JDBC, ODBC), sort work areas, and utility buffers allocate memory based on the declared maximum length, not the actual data length. VARCHAR(4000) columns waste memory across every connection and sort operation. 3. **Data quality**: A generous VARCHAR maximum provides no validation. A phone_number column declared as VARCHAR(4000) will accept an entire paragraph — the declared maximum should serve as a semantic constraint that catches data quality issues at INSERT time.

Return to Chapter 11 | Continue to Exercises